query for continuous period

Question

Post reply

query for continuous period

Viewing 3 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26983 More actions · Answer 1

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 SSC Guru Points: 134017 More actions · Answer 2

If you want an amazingly efficient way to collapse date intervals, see here: http://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 SSC Guru Points: 1004638 More actions · Answer 3

TheSQLGuru (6/22/2011)
If you want an amazingly efficient way to collapse date intervals, see here: http://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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)