Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Logic to break period of time by month Expand / Collapse
Author
Message
Posted Saturday, August 24, 2013 1:42 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:26 PM
Points: 36,717, Visits: 31,167
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 Moden
"RBAR 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1488148
Posted Sunday, August 25, 2013 1:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 6:15 PM
Points: 150, Visits: 369
Thanks, Jeff

I 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) c
inner 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

Tx
M
Post #1488192
Posted Sunday, August 25, 2013 11:19 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:26 PM
Points: 36,717, Visits: 31,167
mario17 (8/25/2013)
Thanks, Jeff

I 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) c
inner 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

Tx
M


BWAA-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 Moden
"RBAR 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1488218
Posted Sunday, August 25, 2013 12:43 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 6:15 PM
Points: 150, Visits: 369
Thanks, Jeff

I 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 <n>, unlike on first part where have #Tally.n specified on join.


Best
Mario
Post #1488223
Posted Monday, August 26, 2013 7:54 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:20 PM
Points: 1,959, Visits: 2,893
mario17 (8/23/2013)
Thanks, Scott
Your 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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1488391
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse