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...
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);
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:
Btw, Teradata implemented the temporal feature in TD13.10 to facilitate loading temporal data.