SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Logic to break period of time by month


Logic to break period of time by month

Author
Message
mario17
mario17
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1384 Visits: 683
Hi,
Can anybody give me and idea if it's possible, I need to check the following table and do some logic counting period of time but withing each of month, so let say for id=303 with period = 45, I'll get 3 records ,etc:

303 6/27/13 8/11/13 45
||
\/

1 303 6/27/13 6/30/13 3
2 303 7/01/13 7/31/13 31
3 303 8/01/13 8/11/13 11




Is there any date function to do this in TSQL?
my sample data

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
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
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
)

select * from ttt



Thanks Mario
Jamie Ingram-729524
Jamie Ingram-729524
Mr or Mrs. 500
Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)

Group: General Forum Members
Points: 521 Visits: 439
If you just want the difference between the dates in the two columns
datediff(mm,col1,col2) + 1 to include the first month

eg select datediff(mm,'6/27/13','8/11/13') +1

Cheers

Jamie
Cool

We are the pilgrims, master.
We shall go always, a little further.

dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17501 Visits: 6431
I believe you can do it something like this.


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
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
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
),
Tally (n) AS (
SELECT number
FROM [master].dbo.spt_values Tally
WHERE [Type] = 'P' AND Number BETWEEN 1 AND 100)
SELECT id, fromDD=MIN(d.DD), toDD=MAX(d.DD), period=COUNT(*), prodID
FROM ttt a
CROSS APPLY (SELECT n FROM Tally WHERE n BETWEEN 1 AND period+1) c
CROSS APPLY (SELECT [month]=DATEPART(month, a.fromDD+n-1),DD=CAST(a.fromDD+n-1 AS DATE)) d
GROUP BY id, prodID, [month]




Note that if you have a period > 100 days, then you'll need a bigger Tally table (examples abound). You can also do this with a Calendar table but that might be a little harder to explain.

Not sure if you really need that last CAST to DATE but you did show your expected results as a DATE and not a DATETIME.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
mario17
mario17
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1384 Visits: 683
see familiar fish, Thanks so much Dwain !!!!


Best
M
mario17
mario17
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1384 Visits: 683
Is it possible to assign level (count for how many parts (months) it was broken for each ID),


    
303 6/27/13 8/11/13 45
||
\/
level
1 303 6/27/13 6/30/13 3
2 303 7/01/13 7/31/13 31
3 303 8/01/13 8/11/13 11




Tx again,
Jamie thansk for your post. I got you point, then I can do inpivot.

Best
mario
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17501 Visits: 6431
mario17 (8/22/2013)
Is it possible to assign level (count for how many parts (months) it was broken for each ID),


    
303 6/27/13 8/11/13 45
||
\/
level
1 303 6/27/13 6/30/13 3
2 303 7/01/13 7/31/13 31
3 303 8/01/13 8/11/13 11




Tx again,
Jamie thansk for your post. I got you point, then I can do inpivot.

Best
mario


Add an outer select to get a ROW_NUMBER():


SELECT Level=ROW_NUMBER() OVER (PARTITION BY id ORDER BY fromdd)
,id, fromDD, toDD, period, prodID
FROM (
SELECT id, fromDD=MIN(d.DD), toDD=MAX(d.DD), period=COUNT(*), prodID
FROM ttt a
CROSS APPLY (SELECT n FROM Tally WHERE n BETWEEN 1 AND period+1) c
CROSS APPLY (SELECT [month]=DATEPART(month, a.fromDD+n-1),DD=CAST(a.fromDD+n-1 AS DATE)) d
GROUP BY id, prodID, [month]
) a





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
ScottPletcher
ScottPletcher
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19357 Visits: 7410
You should use your own tally table, either a permanent one or a dynamically generated one. Since the permanent one would be clustered on the tally value, it might give better performance. I'm using a tally CTE in the code below to allow it to be run independently.
[Reason: Aside from the danger that master.dbo.spt_values could be modified, or removed, at any time by MS, you don't want your application needing authority to read from the master db to be able to run!]

You can then do an INNER JOIN to the tally table to expand the months; the tally value conveniently also provides the level -- nice!


;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
),--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 DATEDIFF(MONTH, ttt.fromDD, ttt.toDD) = 0 OR DATEADD(MONTH, t.tally, 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 DATEDIFF(MONTH, ttt.fromDD, ttt.toDD) = 0 OR DATEADD(MONTH, t.tally, 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) 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.
mario17
mario17
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1384 Visits: 683
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
mario17
mario17
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1384 Visits: 683
Dwain,

trying to reverse eng your super code, is this a place to use Cross Apply ONLY, or could be done with Join.
I can't get second Cross Apply


--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)) d




Thank again
M
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17501 Visits: 6431
mario17 (8/23/2013)
Dwain,

trying to reverse eng your super code, is this a place to use Cross Apply ONLY, or could be done with Join.
I can't get second Cross Apply


--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)) d




Thank again
M


INNER JOIN like that should work assuming your Tally table is in a temporary table. I have a preference for CROSS APPLY.

Are you saying you don't understand what the second CROSS APPLY is doing?

Basically it just does some intermediate calculations. [month] is just the month number which is used later to group the results. DD is the start date + the tally value (-1 to adjust the offset so first n=0).


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search