May 1, 2017 at 1:29 am
Hi All
I have a typical scenario where I have to maintain History Data of 5 tables. So I have 5 rate tables: Rate1,Rate2,Rate3,Rate4 and Rate5. These 5 tables have different types of rates stored in them. Whenever there is change in rate of any table, old data is sent in history table. So there are 5 history tables: Rate1History,Rate2History,Rate3History,Rate4History,Rate5History.
When I have to finally show the Rate history to client, I have to pull data from all 5 history tables using joins and present it. Now if rate changed only for 1 table, then there will be no history data in remaining 4 tables, so join won't work. So basically I need a way to pull history data even when rate changes in only one table.So I need your suggestion/viewpoint what could be a best way to achieve it.
Pls help.
thx
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
May 1, 2017 at 2:40 am
I believe that this will achieve what you are looking for.
It feels a bit clunky, but I can't seem to think of a better way at this stage.
.
WITH
--=== Get a unified data set for each rate
cteRate1 AS (
SELECT RateDate, Rate FROM dbo.Rate1
/***/ UNION ALL /***/
SELECT RateDate, Rate FROM dbo.Rate1History
)
, cteRate2 AS (
SELECT RateDate, Rate FROM dbo.Rate2
/***/ UNION ALL /***/
SELECT RateDate, Rate FROM dbo.Rate2History
)
, cteRate3 AS (
SELECT RateDate, Rate FROM dbo.Rate3
/***/ UNION ALL /***/
SELECT RateDate, Rate FROM dbo.Rate3History
)
, cteRate4 AS (
SELECT RateDate, Rate FROM dbo.Rate4
/***/ UNION ALL /***/
SELECT RateDate, Rate FROM dbo.Rate4History
)
, cteRate5 AS (
SELECT RateDate, Rate FROM dbo.Rate5
/***/ UNION ALL /***/
SELECT RateDate, Rate FROM dbo.Rate5History
)
--=== Get a unique list of rate dates
, cteDates AS (
SELECT RateDate FROM cteRate1
/***/ UNION /***/
SELECT RateDate FROM cteRate2
/***/ UNION /***/
SELECT RateDate FROM cteRate3
/***/ UNION /***/
SELECT RateDate FROM cteRate4
/***/ UNION /***/
SELECT RateDate FROM cteRate5
)
--=== Get the last known rate for each unique rate date
SELECT
RateDate = d.RateDate
, Rate1 = r1.Rate
, Rate2 = r2.Rate
, Rate3 = r3.Rate
, Rate4 = r4.Rate
, Rate5 = r5.Rate
FROM cteDates AS d
OUTER APPLY (SELECT TOP(1) r.Rate FROM cteRate1 AS r
WHERE r.RateDate <= d.RateDate
ORDER BY r.RateDate DESC) AS r1
OUTER APPLY (SELECT TOP(1) r.Rate FROM cteRate2 AS r
WHERE r.RateDate <= d.RateDate
ORDER BY r.RateDate DESC) AS r2
OUTER APPLY (SELECT TOP(1) r.Rate FROM cteRate3 AS r
WHERE r.RateDate <= d.RateDate
ORDER BY r.RateDate DESC) AS r3
OUTER APPLY (SELECT TOP(1) r.Rate FROM cteRate4 AS r
WHERE r.RateDate <= d.RateDate
ORDER BY r.RateDate DESC) AS r4
OUTER APPLY (SELECT TOP(1) r.Rate FROM cteRate5 AS r
WHERE r.RateDate <= d.RateDate
ORDER BY r.RateDate DESC) AS r5
ORDER BY d.RateDate;
May 1, 2017 at 5:12 am
Thanks so much for taking out time to write this for me.it really will help me to go in right direction. I'll spend some time to analyze this approach and see if this goes well with my scenario. Again much appreciated...
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply