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

query for continuous period Expand / Collapse
Author
Message
Posted Saturday, June 18, 2011 3:55 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 6:46 PM
Points: 36,944, Visits: 31,446
Mark-101232 (6/18/2011)

Mark's second rendition???? Nope not mine, ColdCoffee's offering.



Ack! Sorry. Thanks for the correction, Mark. I'll fix that post.


--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 #1127803
Posted Saturday, June 18, 2011 5:23 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 6:46 PM
Points: 36,944, Visits: 31,446
Using the Calendar table I posted in one of the previous posts above, here's what the code at the link that Lutz posted should look like with a couple of very necessary corrections to make it work for this thread and just about any place else where overlapping date ranges need to be solved...

WITH
cteExplodeDateRanges AS
(
SELECT DISTINCT Src, OrgNo,
ExplodedDate = c.Dt
FROM dbo.Test test
CROSS JOIN dbo.Calendar c
WHERE c.Dt BETWEEN test.SDate AND test.EDate
)
,
cteGroupDates AS
(
SELECT Src, OrgNo, ExplodedDate,
DateGroup = ExplodedDate - ROW_NUMBER() OVER (PARTITION BY Src, OrgNo ORDER BY ExplodedDate)
FROM cteExplodeDateRanges
)
SELECT Src, OrgNo, SDate = MIN(ExplodedDate), EDate = MAX(ExplodedDate)
FROM cteGroupDates
GROUP BY Src, OrgNo, DateGroup
ORDER BY Src, OrgNo, DateGroup
;

Using the previous 11 rows already posted several times, it produces the correct answer.

If you want to have some fun with testing for performance, you can run the following code to setup a good number more rows...

WITH 
cteGenRandomDates AS
(
SELECT TOP (10000)
SDate = DATEADD(dd,ABS(CHECKSUM(NEWID()))%DATEDIFF(dd,'2000','2050'),CAST('2000' AS DATETIME))
FROM sys.all_columns ac1,
sys.all_columns ac2
)
SELECT Src = 'abc',
OrgNo = '99999',
SDate,
EDate = DATEADD(dd,ABS(CHECKSUM(NEWID()))%15,SDate)
INTO dbo.Test
FROM cteGenRandomDates
;

Still, the corrected code above still takes a whopping 1.2 to 1.5 seconds to return the answer on a lousy 10,000 rows. I think I may know a better way and I'll work on it over the next day or two.


--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 #1127810
Posted Saturday, June 18, 2011 8:02 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 6:46 PM
Points: 36,944, Visits: 31,446
Actually, I couldn't wait. I tried using a "Modified Quirky Update" and it beat the tar out of the Calendar Table method for larger start/end date combinations.

I'm still tweekin' and testin' so I won't post anything yet. For smaller stuff, the Calendar Table method will probably do for now.


--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 #1127819
Posted Sunday, June 19, 2011 1:52 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 10:14 AM
Points: 477, Visits: 727
Mr. Moden,

Is there any chance you might publish a book?

Please?

--SJTerrill--



Post #1127837
Posted Sunday, June 19, 2011 10: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 @ 6:46 PM
Points: 36,944, Visits: 31,446
SJTerrill (6/19/2011)
Mr. Moden,

Is there any chance you might publish a book?

Please?

--SJTerrill--


I started one over a year ago. Got about 3 chapters into it and was overtaken by events. Since you're about the 4th person in the last couple of weeks to ask if I might publish a book, now seems like a good time to pick it back up.

Thank you for the confidence and the kudo. I'm humbled by your question.


--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 #1127860
Posted Tuesday, June 21, 2011 2:00 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 6:13 AM
Points: 1,694, Visits: 19,550
Second rendition, performs better than the first.

WITH StartsAndEnds(StartEnd,Sdate,Edate,SRC,OrgNo) AS (
SELECT 'S' AS StartEnd,
Sdate,
DATEADD(day,-1,Sdate),
SRC,
OrgNo
FROM TEST
UNION ALL
SELECT 'E' AS StartEnd,
DATEADD(day,1,Edate),
Edate,
SRC,
OrgNo
FROM TEST),

OrderedStarts AS (
SELECT Sdate,
SRC,
OrgNo,
ROW_NUMBER() OVER(PARTITION BY SRC,OrgNo ORDER BY Sdate,StartEnd DESC) AS rnBoth,
2*(ROW_NUMBER() OVER(PARTITION BY SRC,OrgNo,StartEnd ORDER BY Sdate))-1 AS rnStartEnd
FROM StartsAndEnds),

OrderedEnds AS (
SELECT Edate,
SRC,
OrgNo,
ROW_NUMBER() OVER(PARTITION BY SRC,OrgNo ORDER BY Edate DESC,StartEnd) AS rnBothRev,
2*(ROW_NUMBER() OVER(PARTITION BY SRC,OrgNo,StartEnd ORDER BY Edate DESC))-1 AS rnStartEndRev
FROM StartsAndEnds),

Starts AS (
SELECT Sdate,
SRC,
OrgNo,
ROW_NUMBER() OVER(PARTITION BY SRC,OrgNo ORDER BY Sdate) AS rn
FROM OrderedStarts
WHERE rnBoth=rnStartEnd),

Ends AS (
SELECT Edate,
SRC,
OrgNo,
ROW_NUMBER() OVER(PARTITION BY SRC,OrgNo ORDER BY Edate) AS rn
FROM OrderedEnds
WHERE rnBothRev=rnStartEndRev)

SELECT s.SRC,s.OrgNo,s.Sdate,e.Edate
FROM Starts s
INNER JOIN Ends e ON e.SRC=s.SRC AND e.OrgNo=s.OrgNo AND e.rn=s.rn AND s.Sdate<=e.Edate
ORDER BY s.SRC,s.OrgNo,s.Sdate,e.Edate;



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1128813
Posted Wednesday, June 22, 2011 9:42 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:29 PM
Points: 4,343, Visits: 6,149
If you want an amazingly efficient way to collapse date intervals, see here: www.solidq.com/sqj/Pages/2011-March-Issue/Packing-Intervals.aspx. Incredible TSQL goodness served up by Itzik Ben-Gan.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1129804
Posted Sunday, June 26, 2011 11:44 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 6:46 PM
Points: 36,944, Visits: 31,446
TheSQLGuru (6/22/2011)
If you want an amazingly efficient way to collapse date intervals, see here: www.solidq.com/sqj/Pages/2011-March-Issue/Packing-Intervals.aspx. Incredible TSQL goodness served up by Itzik Ben-Gan.


Damn, that's clever. It's not so much the T-SQL I impressed with... it's the simple math behind it all.


--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 #1131883
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse