TSQL Challenge #12 Using Date Functions And Recursive CTE, Laurent Schneider Fun Stuff
TSQL Challenge #12
I'm a bit late on this one !
The last submition date for TSQL Challenge #12 was yesterday !
But I had some holidays and the weather was sunny so I spend less time on my computer !
I couldn't use (nor understand) the arithmetics Pacmann used in the comments of the previous blog entrie.
He managed to avoid the date functions using a lot of trunc (or floor) and modulo functions.
I suppose pure math computes faster than date functions. I'll have to keep this in mind and to try it out seriously.
Ok, about the TSQL Solution so I did use the date functions !
Let's begin with the initial datas :
DECLARE @Scores TABLE
(
YearMonth INT,
Score INT
);
INSERT @Scores VALUES(200903, 100);
INSERT @Scores VALUES(200803, 95);
INSERT @Scores VALUES(200802, 99);
INSERT @Scores VALUES(200801, 100);
INSERT @Scores VALUES(200711, 100);
SELECT * FROM @Scores;
YearMonth Score
----------- -----------
200903 100
200803 95
200802 99
200801 100
200711 100
With the lack of the recent SQL:2008 analytic functions, I have to think about a way to make the score slide from one month to another.
I only know the recursive CTE to achieve this.
About this recursion, I figured that if I can find the number of recursion needed to be achieved before entering the R-CTE it may perform better.
As I'm using the dateadd and datepart function, I'll first convert the YearMonth integer to a date using concatenation, convert and the 112 format (YYYYMMDD).
I'm also using a scalar subquery to find the next YearMonth, and if this one is null I use the coalesce function to insert something aroung the current date using getdate() (the current month is august) :
AS
(
SELECT
CONVERT(smalldatetime, CAST(Sc1.YearMonth AS VARCHAR(6)) + '01', 112),
Sc1.Score,
DATEDIFF(month, CONVERT(smalldatetime, CAST(Sc1.YearMonth AS VARCHAR(6)) + '01', 112),
COALESCE(
(SELECT CONVERT(smalldatetime, CAST(min(YearMonth) AS VARCHAR(6)) + '01', 112) FROM @Scores AS Sc2 WHERE Sc2.YearMonth> Sc1.YearMonth),
CONVERT(smalldatetime, CONVERT(VARCHAR(6), DATEADD(month, 1, getdate()), 112) + '01', 112)))
FROM
@Scores AS Sc1
)
SELECT * FROM CTFull ORDER BY YearMonth DESC;
YearMonth Score NbMonth
----------------------- ----------- -----------
2009-03-01 00:00:00 100 6
2008-03-01 00:00:00 95 12
2008-02-01 00:00:00 99 1
2008-01-01 00:00:00 100 1
2007-11-01 00:00:00 100 2
I know how many times I'll now have to use recursivity on each row (this is NbMonth minus one).
So I just have to create the R-CTE. I'm anchoring the datas keeping an initial YearMonth into a dedicated column, and I just add one month every recursion to the current month until I've reached NbMonth minus one (or strictly inferior to NbMonth) :
AS
(
SELECT YearMonth, YearMonth, Score, 1
FROM CTFull
UNION ALL
SELECT
C.YearMonth,
DATEADD(month, 1, T.YearMonth),
C.Score,
T.Lvl + 1
FROM
CTFull AS C
INNER JOIN CTSol AS T
ON T.YearMonthInit = C.YearMonth
WHERE
T.Lvl <C.NbMonth
)
And the remaining part is doing the last conversion to the desired output and print results.
Here is the full query (the one I send to the TC Team, with my comments) :
DECLARE @Scores TABLE
(
YearMonth INT,
Score INT
);
INSERT @Scores VALUES(200903, 100);
INSERT @Scores VALUES(200803, 95);
INSERT @Scores VALUES(200802, 99);
INSERT @Scores VALUES(200801, 100);
INSERT @Scores VALUES(200711, 100);
-- Query
-- My maths going always lower, I took the easy way and heavily used date functions to solve this challenge.
-- All date conversions use the format 112 : YYYYMMDD
-- I know the query will perform slow on a big table, due to all the conversions, date functions and a scalar query.
-- That's another solution I'll have to wait for !
-- With holidays I didn't took the time to improve all this, at least I enjoyed the sun :-)
-- The first query is to convert the YearMonth into a smalldatetime
-- I'm also computing the number of months between the current YearMonth and the nextYearMonth using a scalar query
-- performing well on small dataset but not so well on bigger ones
-- If I don't find any nextmonth I'm using the first day of the next month instead
-- the next month is due to the further use of a strict inegality
WITH CTFull (YearMonth, Score, NbMonth)
AS
(
SELECT
CONVERT(smalldatetime, CAST(Sc1.YearMonth AS VARCHAR(6)) + '01', 112),
Sc1.Score,
DATEDIFF(month, CONVERT(smalldatetime, CAST(Sc1.YearMonth AS VARCHAR(6)) + '01', 112),
COALESCE(
(SELECT CONVERT(smalldatetime, CAST(min(YearMonth) AS VARCHAR(6)) + '01', 112) FROM @Scores AS Sc2 WHERE Sc2.YearMonth> Sc1.YearMonth),
CONVERT(smalldatetime, CONVERT(VARCHAR(6), DATEADD(month, 1, getdate()), 112) + '01', 112)))
FROM
@Scores AS Sc1
),
CTSol(YearMonthInit, YearMonth, Score, Lvl)
AS
(
SELECT YearMonth, YearMonth, Score, 1
-- I'm anchoring the datas keeping an initial YearMonth into YearMonthInit
-- Lvl is the number of recursion
FROM CTFull
-- No root for the CTE, every row is a root
UNION ALL
SELECT
C.YearMonth,
-- Here is the part of the query where I make the month slide with the same score value
DATEADD(month, 1, T.YearMonth),
C.Score,
T.Lvl + 1
FROM
CTFull AS C
INNER JOIN CTSol AS T
ON T.YearMonthInit = C.YearMonth
-- That's why I need an anchor
WHERE
T.Lvl <C.NbMonth
-- The level of my recursion is known by the number of months before reaching the next row
)
-- Final reconversion to match the desired output
SELECT CONVERT(VARCHAR(6), YearMonth, 112) AS YearMonth,
Score
FROM CTSol
ORDER BY YearMonth DESC;
YearMonth Score
--------- -----------
200908 100
200907 100
200906 100
200905 100
200904 100
200903 100
200902 95
200901 95
200812 95
200811 95
200810 95
200809 95
200808 95
200807 95
200806 95
200805 95
200804 95
200803 95
200802 99
200801 100
200712 100
200711 100
Even if it's far for being perfect, my results seems good !
I can't wait to read about the winners of this challenge !
Laurent Schneider
I would just like to add a link to Laurent Schneider last entrie, which is a fun game !
Could you - using all (1, 3, 4, 6) integers once with only (+, -, *, /) as operators - manage to compute the number... 24 ?
I just saw it and didn't start to look for a solution, but if it starts with some PL/SQL one can suppose it's no trivial.
Don't forget that Laurent Schneider is an Oracle ACE who published the following book :
Advanced Oracle SQL Programming, The Expert Guide to Writing Complex Queries
I've ordered it tonight ! :)
This entry is filed under SQLing. And tagged with Challenges, cte, Laurent Schneider, Pacmann, TSQL. You can follow any responses to this entry through RSS 2.0. You can leave a response, or trackback from your own site.