August 21, 2013 at 10:04 pm
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
August 21, 2013 at 11:00 pm
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
😎
August 21, 2013 at 11:41 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 22, 2013 at 1:14 am
see familiar fish, Thanks so much Dwain !!!!
Best
M
August 22, 2013 at 1:45 am
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
August 22, 2013 at 5:24 am
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 22, 2013 at 4:36 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 23, 2013 at 5:24 pm
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
August 23, 2013 at 5:44 pm
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
August 23, 2013 at 8:10 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 24, 2013 at 1:42 pm
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
Change is inevitable... Change for the better is not.
August 25, 2013 at 1:09 am
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
August 25, 2013 at 11:19 am
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) 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
Change is inevitable... Change for the better is not.
August 25, 2013 at 12:43 pm
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
August 26, 2013 at 7:54 am
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply