## Logic to break period of time by month

 Author Message Jeff Moden SSC Guru Group: General Forum Members Points: 215384 Visits: 41979 To summarize a CROSS APPLY, it's a correlated subquery much like you might find in a subquery within a SELECT list except that it's capable of returning more than one row. CROSS APPLY is like an inner join against the subquery and OUTER APPLY is like a LEFT OUTER JOIN away from the subquery.Paul white wrote a couple of great articles on the subject right here on SSC.http://www.sqlservercentral.com/articles/APPLY/69953/http://www.sqlservercentral.com/articles/APPLY/69954/ --Jeff ModenRBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs mario17 Ten Centuries Group: General Forum Members Points: 1408 Visits: 683 Thanks, JeffI just started to go into CROSS APPLY, being a JOIN person I'm trying to translate in into join, and still can't make it work as in original CROSS APPLY, join can't recognize n, I think it's the main difference/advantage for APPLY.d`--*CROSS APPLY (SELECT n FROM Tally WHERE n BETWEEN 1 AND period+1) cinner join #Tally on N between 1 and Period+1--* CROSS APPLY (SELECT [month]=DATEPART(month, a.fromDD+n-1),DD=CAST(a.fromDD+n-1 AS DATE)) inner join (SELECT [month]=DATEPART(month, a.fromDD+n-1),DD=CAST(a.fromDD+n-1 AS DATE)) z on 1 = 1 `TxM Jeff Moden SSC Guru Group: General Forum Members Points: 215384 Visits: 41979 mario17 (8/25/2013)Thanks, JeffI just started to go into CROSS APPLY, being a JOIN person I'm trying to translate in into join, and still can't make it work as in original CROSS APPLY, join can't recognize n, I think it's the main difference/advantage for APPLY.d`--*CROSS APPLY (SELECT n FROM Tally WHERE n BETWEEN 1 AND period+1) cinner join #Tally on N between 1 and Period+1--* CROSS APPLY (SELECT [month]=DATEPART(month, a.fromDD+n-1),DD=CAST(a.fromDD+n-1 AS DATE)) inner join (SELECT [month]=DATEPART(month, a.fromDD+n-1),DD=CAST(a.fromDD+n-1 AS DATE)) z on 1 = 1 `TxMBWAA-HAA!!! I'm right there with you on that. I used to hate any form of correlated sub-query. Sure, they made for nice encapsulation, easy readability, and sometimes had a performance advantage (Where IN was good for that not to mention the implied "DISTINCT") but they were (are) a bitch to troubleshoot because the code wouldn't work by itself. You couldn't necessarily just highlight the code and click "Execute" to test it.I've got some things to do for work first but I'll try to carve out some time to see if I can come up with something that you'll like better when I'm done. I think this can actually be done with a single CROSS JOIN to the "Tally Table" feature, whatever form it takes. --Jeff ModenRBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs mario17 Ten Centuries Group: General Forum Members Points: 1408 Visits: 683 Thanks, JeffI definitely need more prep for APPLY.I don't think it's possible for Dwain sample (other sample from Scott has Joins).I tried to translate it to Join and it never can refer to , unlike on first part where have #Tally.n specified on join. BestMario ScottPletcher SSCoach Group: General Forum Members Points: 19671 Visits: 7410 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, Scottyour 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 allMYes, 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 tttcteDigits 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 PeriodFROM tttINNER 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.