use changes table to update main

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

use changes table to update main

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-14 : 11:00:47

James writes "Hi

I have a main table and a changes table. The main table reflects the current data state. The changes table has exactly the same structure as the main table except it has a date field to indicate when an expected change will take place in the data.

I want to be able to create a predicted data view for a specific date by merging these two tables. Eg:

Main: (key:ID)

ID Name Fav_colour
1 James Blue
2 John Red

Changes: (key:ID & Date)

ID Name Fav_colour Date
1 Jimmy <NULL> 2004/04/04
2 <NULL> Violet 2003/01/01
1 <NULL> Green 2002/10/11

The idea is that the changes table reflects the predicted information: On 2002/10/11 id=1 changes his favorite color to Green. On 2003/01/01 id=2 changes his favorite color to violet. On 2004/04/04 id=1 changes his name to Jimmy.

I would like to be able to create a prediction table on the fly using a query that would give me the predicted state of my data on a certain date, eg. 2005/01/01. So the result of my query for 2005/01/01 should be:

ID Name Fav_colour
1 Jimmy Green
2 John Violet

Reflecting all the predicted changes up to then.

Any ideas ?"

ToddV
Posting Yak Master

218 Posts

Posted - 2002-02-14 : 13:00:04

Here are a couple of solutions. Not real pretty, but functional.

CREATE TABLE #People (
ID INT,
Name VARCHAR(10),
Fav_colour VARCHAR(10))

INSERT #People VALUES(1, 'James', 'Blue')
INSERT #People VALUES(2, 'John', 'Red')

Create table #Changes(
ID INT,
Name VARCHAR(10),
Fav_colour VARCHAR(10),
Date Datetime)

INSERT #Changes VALUES(1, 'Jimmy', NULL, '2004/04/04')
INSERT #Changes VALUES(2, NULL, 'Violet', '2003/01/01')
INSERT #Changes VALUES(1, NULL, 'Green', '2002/10/11')

SELECT ID,
MAX(DATE) AS "NameDate"
WHERE Name IS NOT NULL
GROUP BY ID

SELECT ID,
MAX(DATE) AS "Fav_colourDATE"
WHERE Fav_colour IS NOT NULL
GROUP BY ID

select * from #Changes

SELECT A.ID,
Coalesce(B.Name,A.NAME) AS Name,
Coalesce(C.Fav_Colour, A.Fav_Colour) AS FavoriteColor
FROM #People A
LEFT JOIN (SELECT B1.ID,Name
FROM #Changes B1
JOIN (SELECT ID,
MAX(DATE) AS "NameDate"
FROM #Changes
WHERE Name IS NOT NULL AND Date <= '2005/01/01'
GROUP BY ID) AS B2
ON B1.ID = B2.ID AND B2.NameDate = B1.Date) AS B
ON B.ID = A.ID
LEFT JOIN (SELECT C1.ID,Fav_colour
FROM #Changes C1
JOIN (SELECT ID,
MAX(DATE) AS "Fav_colourDATE"
FROM #Changes
WHERE Fav_colour IS NOT NULL AND Date <= '2005/01/01'
GROUP BY ID) AS C2
ON C1.ID = C2.ID AND C2.Fav_colourDATE = C1.Date) AS C
ON C.ID = A.ID

SELECT A.ID,
Coalesce(B.Name,A.NAME) AS Name,
Coalesce(B.Fav_Colour, A.Fav_Colour) AS FavoriteColor
FROM #People A
LEFT JOIN (SELECT X.ID,
MAX(CASE WHEN DATE = NameDATE THEN NAME END) AS Name,
MAX(CASE WHEN DATE = Fav_colourDATE THEN Fav_colour END) AS Fav_colour
FROM #CHANGES X
JOIN ( SELECT ID,
MAX(CASE WHEN Name IS NOT NULL THEN Date END) AS NameDATE ,
MAX(CASE WHEN Fav_colour IS NOT NULL THEN Date END) AS Fav_colourDATE
FROM #Changes
WHERE Date <= '2005/01/01'
GROUP BY ID) AS Y
ON X.ID = Y.ID
GROUP BY X.ID) AS B
ON A.ID= B.ID

Edited by - toddv on 02/14/2002 13:00:47

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources