I'm working on a snapshot of a Fact. The snapshot's purpose is to record changes of columns x,y, and z. If either x,y, or z change in the Fact, a new row should be added to the Snapshot and flag columns for the three columns should read Y or N (depending on which of the three columns changed).
My approach was to simply compare columns x,y, and z between extract date and dates from the day before(could be mins or secs before, nonetheless, I just want a variance in time).
My thought was to use a CASE statement in the source query:
SELECT CASE WHEN [x] <> (SELECT [x] FROM dw.dbo.FCT_TABLE WHERE LAST_MOD_DATE = DATEADD(DAY,-1,[LAST_MOD_DATE])) then 'Y' else 'N' end as FLAG_X
But I don't seem to be getting the correct results. Any suggestions? Ultimately, I will apply the logic to SSIS
- Edited by Rookie3000 Friday, February 07, 2014 12:00 AM Forgot some essential details
This WHERE clause
WHERE LAST_MOD_DATE = DATEADD(DAY,-1,[LAST_MOD_DATE])
will of course never match any rows.
Supposedly, you mean LAST_MOD_DATE from two different tables, but you need to tell the computer that. Computers are not very smart.
Also, what data type is LAST_MOD_DATE? If it's datetime or datetime2 and includes a time portion, your changes for a hit are bad.
If x can be NULL, the logic needs to be more sophisticated.
You could of course also consider Change Data Capture (if you are on Enterprise Edition) of Change Tracking (any edition), but it may be too heavy artillery, if this is your only snapshot.
Erland Sommarskog, SQL Server MVP, email@example.com
- Proposed as answer by Kalman TothModerator Tuesday, February 25, 2014 4:13 PM
X won't be NULL is any of our cases...
I want to default the flags to 'N'...then conduct lookups on x,y, or z based on the keys in the FCT and FCT Snp. If the Keys match but x,y,z don't then I'll insert a new record with 'Y' for the values that have change. What do you think of this approach?
Kalman Toth Database & OLAP Architect SELECT Video Tutorials 4 Hours
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012