T-SQL to retrieve history data from table / log_table by RelevanceDate
I have the following tables:
1) TBL (ID, Data, LastUpdated, DateCreated) 2) TBL_LOG(Log_ID, LogCreateDate, ID, Data, LastUpdated, DateCreated)
TBL
used to store the current data and TBL_LOG
used to store each data change in TBL
(using an update trigger, the deleted record from is TBL
copied to TBL_LOG
).
Now I need to get data by relevance date.
How can I write a table valued function (or other alternative) that will return data depending on RelevanceDate
.
I understand the logic, but I can't find a good way to write it in SQL ...
The logic looks like this:
@RelevanceDate = '2011-03-01'
IF TBL.LastUpdated <= @RelevanceDate THEN return data from TBL
ELSE IF Exists data in TBL_LOG where TBL_LOG.LastUpdated <= @RelevanceDate
THEN return most resent data from TBL_LOG where TBL_LOG.LastUpdated <= @RelevanceDate
ELSE IF Exists data in TBL_LOG where TBL_LOG.LastUpdated > @RelevanceDate
THEN return the oldest data from TBL_LOG.LastUpdated > @RelevanceDate
ELSE return data from TBL
This function should return data using the above logic for all records in TBL
.
Long story short, for each row in TBL I need the data that was on "RelevanceDate".
source to share
At the end of my research, I ended up with something like the following:
DECLARE @RelevanceDate DATETIME
SET @RelevanceDate = '2012-03-01'
SELECT
MainData.ID, MainData.Data, MainData.LastUpdated, MainData.DateCreated
FROM (
SELECT
*
,ROW_NUMBER() OVER(PARTITION BY AllData.ID, AllData.RowNum ORDER BY AllData.LastUpdated) AS MainRowNumber
FROM (
--Current Data
SELECT
NULL LogID
,NULL LogCreateDate
,*
,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY LastUpdated DESC) AS RowNum
FROM TBL
WHERE (lastupdate<=@RelevanceDate)
UNION
--History Data
SELECT
*
,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY LastUpdated) AS RowNum
FROM TBL_LOG
WHERE lastupdate>@RelevanceDate
) AllData WHERE AllData.RowNum=1
) MainData
WHERE MainData.MainRowNumber = 1
If anyone has a suggestion on how it can be done better, I will be glad to see it :)
source to share
You can use NESTED CASE statements in your selection, in lines like the following:
SELECT
CASE WHEN TBL.LastUpdated <= @RelevanceDate THEN TBL.data ELSE
CASE WHEN TBL_LOG.LastUpdated < @RelevanceDate AND NOT TBL_LOG.Data IS NULL THEN
TBL_LOG.Data
ELSE
...
END
END AS Data
FROM TBL TBL
LEFT JOIN TBL_LOG TBL_LOG
ON TBL.ID = TBL_LOG.ID
source to share