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


query for continuous period


query for continuous period

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86291 Visits: 41098
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. Blush

--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.
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 problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86291 Visits: 41098
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.
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 problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86291 Visits: 41098
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.
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 problems
How to post performance problems
Forum FAQs
SJTerrill
SJTerrill
Mr or Mrs. 500
Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)

Group: General Forum Members
Points: 562 Visits: 728
Mr. Moden,

Is there any chance you might publish a book?

Please?

--SJTerrill--



Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86291 Visits: 41098
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.
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 problems
How to post performance problems
Forum FAQs
Mark Cowne
Mark Cowne
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3109 Visits: 24089
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;



____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

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




TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12487 Visits: 8553
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 on googles mail service
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86291 Visits: 41098
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.
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 problems
How to post performance problems
Forum FAQs
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