History Cross-Tab via Cursors

I have a table with a PK of id and timestamp, plus other variables: an
update of a row adds another row with the same id but a later timestamp. I
need to create a report that lists the variables that have changed. The
following proof of concept code works fine, but I was wondering if the same
thing can be done more simply--perhaps without cursors.
Thanks for taking a look. All comments appreciated.
--FINAL RESULT TABLE FOR HISTORY REPORTING
drop table rpt_tbl
go
create table rpt_tbl
(fld_name varchar(20),
old_rpt_id int,
old_rpt_timestamp datetime,
old_fld varchar(20),
new_rpt_id int,
new_rpt_timestamp datetime,
new_fld varchar(20))
--TEST DATA
create table PROD
(prod_id int,
prod_timestamp datetime,
prod_name varchar(20),
prod_categ varchar(20))
insert prod
(prod_id, prod_timestamp, prod_name, prod_categ)
values
(1, '2004/07/05', 'Acme', 'Steel')
insert prod
(prod_id, prod_timestamp, prod_name, prod_categ)
values
(1, '2004/08/08', 'Best', 'Steel')
insert prod
(prod_id, prod_timestamp, prod_name, prod_categ)
values
(2, '2004/07/10', 'Pink', 'Color')
insert prod
(prod_id, prod_timestamp, prod_name, prod_categ)
values
(2, '2005/01/05', 'Red', 'Color')
insert prod
(prod_id, prod_timestamp, prod_name, prod_categ)
values
(2, '2005/02/17', 'Fuchsia', 'Shade')
insert prod
(prod_id,prod_timestamp, prod_name, prod_categ)
values
(3, '2005/02/17', 'Ivory', 'Shade')
insert prod
(prod_id,prod_timestamp, prod_name, prod_categ)
values
(4, '2005/4/22', 'Yellow', 'Color')
--VIEW
--GET ROWS IN PROD TABLE WITH UDPATE HISTORY
--that is, multiple rows for same prod_id, with different timestamps
create view DUPES as
select * from prod
where prod_id in
(select prod_id from prod
group by prod_id
having count(*) > 1)
--CREATE 1-ROW TABLE FOR COMPARE
create function create_table
(@.prod_id int,
@.prod_timestamp datetime)
returns table
as return
(select
prod_id, prod_timestamp,
prod_name, prod_categ
from dupes where
(prod_id = @.prod_id and prod_timestamp = @.prod_timestamp))
--COMPARE 2 ROWS
create proc compare_2_rows
(@.a_prod_id int,
@.a_prod_timestamp datetime,
@.b_prod_id int,
@.b_prod_timestamp datetime)
as
declare
@.a_prod_name varchar(20),
@.a_prod_categ varchar(20),
@.b_prod_name varchar(20),
@.b_prod_categ varchar(20)
declare xc2 cursor for
select * from
create_table (@.a_prod_id, @.a_prod_timestamp) t1
join
create_table (@.b_prod_id, @.b_prod_timestamp) t2
on t1.prod_id = t2.prod_id
open xc2
fetch next from xc2 into
@.a_prod_id, @.a_prod_timestamp,
@.a_prod_name, @.a_prod_categ,
@.b_prod_id, @.b_prod_timestamp,
@.b_prod_name, @.b_prod_categ
while @.@.fetch_status = 0
begin
if @.a_prod_name <> @.b_prod_name
insert rpt_tbl
(fld_name,
old_rpt_id, old_rpt_timestamp, old_fld,
new_rpt_id, new_rpt_timestamp, new_fld)
values
('name',
@.a_prod_id, @.a_prod_timestamp, @.a_prod_name,
@.b_prod_id, @.b_prod_timestamp, @.b_prod_name)
if @.a_prod_categ <> @.b_prod_categ
insert rpt_tbl
(fld_name,
old_rpt_id, old_rpt_timestamp, old_fld,
new_rpt_id, new_rpt_timestamp, new_fld)
values
('categ',
@.a_prod_id, @.a_prod_timestamp, @.a_prod_categ,
@.b_prod_id, @.b_prod_timestamp, @.b_prod_categ)
fetch xc2 into
@.a_prod_id, @.a_prod_timestamp,
@.a_prod_name, @.a_prod_categ,
@.b_prod_id, @.b_prod_timestamp,
@.b_prod_name, @.b_prod_categ
end
close xc2
deallocate xc2
create proc history_proc as
--OVERALL PROC
--1. empty report table
--2. use cursor to find paired PK's
--3. exec proc compare_2_rows with PK params for paired data
-- a. use cursor to join function tables for paired data
-- b. compare 2 prod_names and insert paired data into
-- rpt_tbl
-- c. compare 2 prod_categs and insert paired data into
-- rpt_tbl
truncate table rpt_tbl --empty report table
declare --the keys for the rows to be compared
--@.a refers to the older row
--@.b refers to the newer (more recent) row
@.a_prod_id int,
@.a_prod_timestamp datetime,
@.b_prod_id int,
@.b_prod_timestamp datetime
declare xc cursor for select prod_id, prod_timestamp
from dupes
open xc
--1st record (older row)
fetch xc into @.a_prod_id, @.a_prod_timestamp
--2nd record (newer row): has to be at least a pair for same id
fetch next from xc into @.b_prod_id, @.b_prod_timestamp
while @.@.fetch_status = 0
begin
--if a break in keys, don't compare them,
--and move newer key to older key variables
if @.b_prod_id <> @.a_prod_id
begin
set @.a_prod_id = @.b_prod_id
set @.a_prod_timestamp = @.b_prod_timestamp
goto skip
end
exec compare_2_rows
@.a_prod_id, @.a_prod_timestamp,
@.b_prod_id, @.b_prod_timestamp
--after compare, move newer key to older key variables
set @.a_prod_id = @.b_prod_id
set @.a_prod_timestamp = @.b_prod_timestamp
skip:
--get key for new row
fetch next from xc into @.b_prod_id, @.b_prod_timestamp
end
close xc
deallocate xc
--THIS RUNS THE OVERALL PROC
exec history_proc
--THIS SHOWS WHAT'S IN THE FINAL RESULTS TABLE
select * from rpt_tblBased on your example data, the primary key should be prod_id ,
prod_categ and prod_timestamp.
Try this:
select ProdNew.prod_id
, ProdNew.prod_categ
, ProdNew.prod_timestamp
, ProdNew.prod_name
, ProdOld.prod_timestamp
, ProdOld.prod_name
from (select Prod6.prod_id, Prod6.prod_categ ,
max(Prod6.prod_timestamp)
from Prod as Prod6
group by Prod6.prod_id, Prod6.prod_categ
) as ProdLastest ( prod_id, prod_categ , prod_timestamp)
join Prod as ProdNew
on ProdNew.prod_id = ProdLastest.prod_id
and ProdNew.prod_categ = ProdLastest.prod_categ
and ProdNew.prod_timestamp = ProdLastest.prod_timestamp
left outer join
(
select Prod1.prod_id
, Prod1.prod_categ
, Prod1.prod_timestamp
, Prod1.prod_name
from Prod as Prod1
join (select Prod.prod_id, Prod.prod_categ , max(Prod.prod_timestamp)
from Prod
join (select prod_id, prod_categ , max(prod_timestamp)
from Prod
group by Prod.prod_id, Prod.prod_categ
) as Prod3 ( prod_id, prod_categ , prod_timestamp)
on Prod.prod_id = Prod3.prod_id
and Prod.prod_categ = Prod3.prod_categ
and Prod.prod_timestamp < Prod3.prod_timestamp
group by Prod.prod_id, Prod.prod_categ
) as ProdOlder ( prod_id, prod_categ , prod_timestamp)
on Prod1.prod_id = ProdOlder.prod_id
and Prod1.prod_categ = ProdOlder.prod_categ
and Prod1.prod_timestamp = ProdOlder.prod_timestamp
) as ProdOld (prod_id, prod_categ, prod_timestamp, prod_name)
on ProdOld.prod_id = ProdNew.prod_id
and ProdOld.prod_categ = ProdNew.prod_categ
order by ProdNew.prod_id
, ProdNew.prod_categ
*** Sent via Developersdex http://www.examnotes.net ***|||Check out the doc for the RAC utility.
Focus on the 'What' instead of the 'How'.
www.rac4sql.net|||Thanks--I'll have to take a look at your code in the morning. I'm too blear
y.
The pk is prod_id/prod_timestamp. In the real world, the timestamp would
have year-month-day-time.
There's no limit to the possible number of rows per id. I need to compare
every column (there will be about 10 or 20, in the real world) in every row
for the same prod_id, so that the final table has a row for every old/new
column pair which are different.
"Carl Federl" wrote:

> Based on your example data, the primary key should be prod_id ,
> prod_categ and prod_timestamp.
> Try this:
> select ProdNew.prod_id
> , ProdNew.prod_categ
> , ProdNew.prod_timestamp
> , ProdNew.prod_name
> , ProdOld.prod_timestamp
> , ProdOld.prod_name
> from (select Prod6.prod_id, Prod6.prod_categ ,
> max(Prod6.prod_timestamp)
> from Prod as Prod6
> group by Prod6.prod_id, Prod6.prod_categ
> ) as ProdLastest ( prod_id, prod_categ , prod_timestamp)
> join Prod as ProdNew
> on ProdNew.prod_id = ProdLastest.prod_id
> and ProdNew.prod_categ = ProdLastest.prod_categ
> and ProdNew.prod_timestamp = ProdLastest.prod_timestamp
> left outer join
> (
> select Prod1.prod_id
> , Prod1.prod_categ
> , Prod1.prod_timestamp
> , Prod1.prod_name
> from Prod as Prod1
> join (select Prod.prod_id, Prod.prod_categ , max(Prod.prod_timestamp)
> from Prod
> join (select prod_id, prod_categ , max(prod_timestamp)
> from Prod
> group by Prod.prod_id, Prod.prod_categ
> ) as Prod3 ( prod_id, prod_categ , prod_timestamp)
> on Prod.prod_id = Prod3.prod_id
> and Prod.prod_categ = Prod3.prod_categ
> and Prod.prod_timestamp < Prod3.prod_timestamp
> group by Prod.prod_id, Prod.prod_categ
> ) as ProdOlder ( prod_id, prod_categ , prod_timestamp)
> on Prod1.prod_id = ProdOlder.prod_id
> and Prod1.prod_categ = ProdOlder.prod_categ
> and Prod1.prod_timestamp = ProdOlder.prod_timestamp
> ) as ProdOld (prod_id, prod_categ, prod_timestamp, prod_name)
> on ProdOld.prod_id = ProdNew.prod_id
> and ProdOld.prod_categ = ProdNew.prod_categ
> order by ProdNew.prod_id
> , ProdNew.prod_categ
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
>

Hiding Top Banner In Subscriptions

Hi there
Does anyone know how to hide the banner on the top of the subscriptions
page when you are viewing via a URL. I have managed it in the reports
by setting the parameters to false etc but cannot do it in the
subscriptions.
Thanks in advance
DaveI would like to second Dave's request for information. I am struggling with
the same problem.
Thanks,
Joe
"Bungle" wrote:
> Hi there
> Does anyone know how to hide the banner on the top of the subscriptions
> page when you are viewing via a URL. I have managed it in the reports
> by setting the parameters to false etc but cannot do it in the
> subscriptions.
> Thanks in advance
> Dave
>|||Add me in as well. I thought I figured this out once, but for the life of me
cannot get this to work anymore.
"Joe" wrote:
> I would like to second Dave's request for information. I am struggling with
> the same problem.
> Thanks,
> Joe
> "Bungle" wrote:
> > Hi there
> >
> > Does anyone know how to hide the banner on the top of the subscriptions
> > page when you are viewing via a URL. I have managed it in the reports
> > by setting the parameters to false etc but cannot do it in the
> > subscriptions.
> >
> > Thanks in advance
> >
> > Dave
> >
> >

Hiding SQL Server Reporting Services Header

we published some reports and want to give user access to these via report
manager UI to Certain Folders.
Is it possible to hide the header which display SQL Server Reporting
Services, Home, My Subscriptions etc.?
thxHave you tried simply using the ReportServer URL? //SERVERNAME/ReportServer/
Let me know if this helps...
-David.
"nkg" wrote:
> we published some reports and want to give user access to these via report
> manager UI to Certain Folders.
> Is it possible to hide the header which display SQL Server Reporting
> Services, Home, My Subscriptions etc.?
> thx
>
>

Hiding reports

Is there a way to hide reports before publishing and then hiding reports via the Report Manager? We have some subreports that before we publish, we would like to set a flag to hidden so the Report Manager will know after the deploy that this report is to be hidden.

Thanks for the information.

Check out this post.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=546114&SiteID=1

-Somsong

|||

Thanks for the link.

I guess there is not a way to tell the report to be hidden before deploying it to the server. I kind of wished there were without having to go into the Report Manager after the report has been published.

|||

Yes you can. If you use the Reporting Services Web Service to deploy your reports you can pass in properties that sets the hidden property just like you would do in the report manager.

-Somsong

Hiding 2005 Instance via TSQL

Is there a way to set the "Hide Instance" property of a SQL 2005 instance programmatically?

Every reference I find is for using the Configuration Manager GUI, but we need to create and hide instances in an automated setup prgram.

I was hoping that sp_Config and RECONFIGURE would do the trick, but the options don't seem to include hiding the instance.

I would appreciate any hints at where to look next. Thanks!

Hi Ted,

This flag is stored in the registry:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.N\MSSQLServer\SuperSocketNetLib\HideInstance

Set the decimal value to 1 to hide - if you need to do this from within MSSQL, you can use the registry xp's (xp_regread and xp_regwrite) - as these are undocumented, the usual warnings apply.

Cheers,

Rob

|||

Perfect, thank you Rob, that is exactly what I was looking for.

-Ted