mario17 (8/23/2013)
Thanks, ScottYour code works fine, and much easy to digest ( or learn if you will), amazing.. and thanks again to you and Dwain for sharing this, sure you can read this in books...-)
BTW, Scott
your code produces not correct results if you go beyound 210 days or so, I played with it and found this.
So curious why/where it could happened, tally I think works just fine, it has 100 rows ( for 100 days?), but it still works fine on d,-209 and start breaking on d,-210. (toDD = today = getdate().
You can see looking at toDD for last level, it set to 8/31/13
select 303 Id, DATEADD(d,-210,getdate()) fromDD, getdate() toDD,
datediff(d, DATEADD(d,-210,getdate()), getdate()) period ,3000 prodID
Best to all
M
Yes, quite right, sorry. I think I had some cut/paste issues when I assembled the code.
Please try the corrected code below. I've added test rows for 220 days and 415 days. The code, as is, should handle anything up to 99 months; for more months, just increase the size of the tally CTE.
;WITH ttt AS (
select 123 Id, DATEADD(d,-10,getdate()) fromDD, DATEADD(d,-3,getdate()) toDD,
datediff(d, DATEADD(d,-10,getdate()), DATEADD(d,-3,getdate())) period ,1000 prodID union all
select 202 Id, DATEADD(d,-25,getdate()) fromDD, DATEADD(d,-17,getdate()) toDD,
datediff(d, DATEADD(d,-25,getdate()), DATEADD(d,-17,getdate())) period ,2000 prodID union all
select 303 Id, DATEADD(d,-55,getdate()) fromDD, DATEADD(d,-10,getdate()) toDD,
datediff(d, DATEADD(d,-55,getdate()), DATEADD(d,-10,getdate())) period ,3000 prodID union all
select 403 Id, DATEADD(d,-220,getdate()) fromDD, DATEADD(d,0,getdate()) toDD,
datediff(d, DATEADD(d,-220,getdate()), DATEADD(d,0,getdate())) period ,4000 prodID union all
select 503 Id, DATEADD(d,-415,getdate()) fromDD, DATEADD(d,0,getdate()) toDD,
datediff(d, DATEADD(d,-415,getdate()), DATEADD(d,0,getdate())) period ,5000 prodID
),--select * from ttt
cteDigits AS (
SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
SELECT [10s].digit * 10 + [1s].digit AS tally
FROM cteDigits [1s]
CROSS JOIN cteDigits [10s]
)
SELECT
t.tally + 1 AS Level,
ttt.Id,
CONVERT(varchar(8),
CASE WHEN t.tally = 0 THEN ttt.fromDD ELSE DATEADD(MONTH, DATEDIFF(MONTH, 0, ttt.fromDD) + t.tally, 0) END
, 1) AS fromDD,
CONVERT(varchar(8),
CASE WHEN t.tally = DATEDIFF(MONTH, ttt.fromDD, ttt.toDD) THEN ttt.toDD
ELSE DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, ttt.fromDD) + t.tally + 1, 0)) END
, 1) AS toDD,
DATEDIFF(DAY,
CASE WHEN t.tally = 0 THEN ttt.fromDD ELSE DATEADD(MONTH, DATEDIFF(MONTH, 0, ttt.fromDD) + t.tally, 0) END,
CASE WHEN t.tally = DATEDIFF(MONTH, ttt.fromDD, ttt.toDD) THEN ttt.toDD
ELSE DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, ttt.fromDD) + t.tally + 1, 0)) END) + 1 AS Period
FROM ttt
INNER JOIN cteTally t ON
t.tally BETWEEN 0 AND DATEDIFF(MONTH, ttt.fromDD, ttt.toDD)
ORDER BY
Id, Level
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.