Type2 query

Database
Enthusiast

Type2 query

can anyone please help me with query...

I have a table like below

Product_ID    Product_Name    Value        Start_Date

999        xxx        100        2012-01-01

But the product value changes once in a whiile and we would like to store the historical data...

Product_ID    Product_Name    Value        Start_Date    End_Date

999        xxx        100        2012-01-01    2012-02-12

999        xxx        200        2012-02-12    2012-03-05

999        xxx        100        2012-03-05    NULL

If value change, then i need to set the date of update to End_Date and add a new record with the start date of updated date

Can someone please help me with the query...

3 REPLIES
Junior Contributor

Re: Type2 query

You do exactly what you described:

update tab set end_date = newdate where product_id = 999 and end_date is null;

insert into tab values (999, ..., newdate,NULL);

Dieter

Enthusiast

Re: Type2 query

but i have to do this for 1000's of records and also need to include it in daily process. Not manually. Is there a way to do that?

Junior Contributor

Re: Type2 query

Of course there is, it's done every day at every DWH :-)

In your simple case you just have to change single row update/insert to set based update/insert processing multiple rows. But it will be much more complex for other cases resulting in highly sophisticated load scripts.

When you google for "scd type 2" you'll find lots of info on how to do it, lots of those scripts are based on the work of Richard Snodgrass on temporal databases. His book "Developing Time-Oriented Database Applications in SQL" is out of print, but you can download the PDF on his website:

www.cs.arizona.edu/~rts/publications.html

Btw, Teradata implemented the temporal feature in TD13.10 to facilitate loading temporal data.

Dieter