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 ***
>