May 14, 2014 at 5:22 am
/* You missed out the ddl for table #C, the ReleaseNumber on the
last row of your sample data is probably incorrect, and your spec is sloppy
and vague. That's why your post has been up for five hours with no responses.
This query comes close enough as a trial run. Note that it's NOT optimised,
it's laid out in a way to facilitate development and understanding.
Check the query against a more substantial sample set and post back inconsistencies.
If it's correct, there are a number of changes to make before
letting it anywhere near a production environment. */
SELECT
a.BusinessDate,
a.SeasonID,
a.RouteID,
b.ReleaseNumber,
b.Value
FROM #A a
CROSS APPLY (
SELECT b.ReleaseID, b.ReleaseNumber, b.NumberOfDays, b.Value,
x.TodaysDate, x.NewDate,
y.DaysDiff,
rn = ROW_NUMBER() OVER(ORDER BY CASE
WHEN y.DaysDiff < 0 THEN 9999
WHEN y.DaysDiff IS NULL THEN 9999
ELSE y.DaysDiff END)
FROM #B b
CROSS APPLY (
SELECT
TodaysDate = CAST(GETDATE()-1 AS DATE),
NewDate = DATEADD(day, 0-B.NumberofDays, A.BusinessDate)
) x
CROSS APPLY (
SELECT DaysDiff = DATEDIFF(day, x.NewDate, x.TodaysDate)
) y
WHERE b.SeasonID = a.SeasonID
AND b.RouteID = a.RouteID
) b
WHERE rn = 1
ORDER BY a.BusinessDate, a.SeasonID, a.RouteID
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 14, 2014 at 11:54 am
Thanks much Chris. You are awesome! 🙂 Thanks for working through my unclear specs and data. My apologies.
Your solution works well on larger sample size.
Could you mention the changes needed to the current code.
May 16, 2014 at 2:20 am
MathewK (5/14/2014)
Thanks much Chris. You are awesome! 🙂 Thanks for working through my unclear specs and data. My apologies.Your solution works well on larger sample size.
Could you mention the changes needed to the current code.
Thanks, that's very kind.
Firstly, rip out unnecessary columns from the subquery for table B. Less data to carry through, more chance of an index matching the query. I've made a couple of small changes to your sample set to force usage of an index.
Secondly, cut down on the CROSS APPLY cascades, they can get expensive very quickly.
Thirdly, try the TOP equivalent to ROW_NUMBER used in the original query.
DROP TABLE #A
CREATE TABLE #A (MainId INT NOT NULL PRIMARY KEY,SeasonId INT,RouteId INT,BusinessDate DATE)
INSERT INTO #A
SELECT 1,100,10,'2014/05/17' UNION
SELECT 2,100,9,'2014/05/17' UNION
SELECT 4,100,10,'2014/05/22' UNION
SELECT 5,100,9,'2014/05/22'
DROP TABLE #B
CREATE TABLE #B (ReleaseId INT NOT NULL PRIMARY KEY,SeasonId INT,RouteId INT,ReleaseNumber INT,NumberOfDays INT NULL,Value INT, Dummy VARCHAR(MAX))
CREATE INDEX ix_Helper ON #B (SeasonId, RouteId) INCLUDE (ReleaseNumber, NumberofDays, Value)
INSERT INTO #B
SELECT 1,100,10,1,NULL,20, REPLICATE('A',8000) UNION
SELECT 2,100,10,2,7,30, 'A' UNION
SELECT 3,100,10,3,14,40, 'A' UNION
SELECT 4,100,9,1,NULL,50, 'A'
--============================================================================
SELECT
a.BusinessDate,
a.SeasonID,
a.RouteID,
b.ReleaseNumber,
b.Value
FROM #A a
CROSS APPLY ( -- b
SELECT
b.ReleaseNumber, b.Value, -- just the columns you need
rn = ROW_NUMBER() OVER(ORDER BY CASE
WHEN y.DaysDiff < 0 THEN 9999
WHEN y.DaysDiff IS NULL THEN 9999
ELSE y.DaysDiff END)
FROM #B b
CROSS APPLY ( -- cascaded CROSS APPLY can get expensive
SELECT DaysDiff = DATEDIFF(day,
DATEADD(day, 0-b.NumberofDays, a.BusinessDate),
CAST(GETDATE()-1 AS DATE))
) y
WHERE b.SeasonID = a.SeasonID
AND b.RouteID = a.RouteID
) b
WHERE rn = 1
ORDER BY a.BusinessDate, a.SeasonID, a.RouteID
-- ==============================================================
SELECT
a.BusinessDate,
a.SeasonID,
a.RouteID,
b.ReleaseNumber,
b.Value
FROM #A a
CROSS APPLY ( -- b
SELECT TOP 1
b.ReleaseNumber, b.Value -- just the columns you need
FROM #B b
CROSS APPLY ( -- cascaded CROSS APPLY can get expensive
SELECT DaysDiff = DATEDIFF(day,
DATEADD(day, 0-b.NumberofDays, a.BusinessDate),
CAST(GETDATE()-1 AS DATE))
) y
WHERE b.SeasonID = a.SeasonID
AND b.RouteID = a.RouteID
ORDER BY CASE
WHEN y.DaysDiff < 0 THEN 9999
WHEN y.DaysDiff IS NULL THEN 9999
ELSE y.DaysDiff END
) b
ORDER BY a.BusinessDate, a.SeasonID, a.RouteID
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 19, 2014 at 10:25 am
Dear Chris - thanks much for your kind help and your detailed answer!! This helps a lot. 🙂
Regards
Mathew
Viewing 4 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply