Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Group Islands of Contiguous Dates (SQL Spackle)


Group Islands of Contiguous Dates (SQL Spackle)

Author
Message
quickdraw
quickdraw
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 107
Oh, that row_number trick is so beautiful it makes me want to cry!
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45189 Visits: 39925
quickdraw (6/27/2011)
Oh, that row_number trick is so beautiful it makes me want to cry!


If you'd like to see a similar "Row_Number Trick" on steroids ;-) to solve the problem when the dates and times aren't contiguous and are truly overlapping, check out Itzik's article on the subject. The man's use of simple mathematics is something to behold. Here's the link:
http://www.solidq.com/sqj/Pages/2011-March-Issue/Packing-Intervals.aspx

That site does require a membership to read the full article just as SQLServerCentral does. And, like SQLServerCentral, membership is free and safe and they only need your email address. They don't sell your email address nor give it to "interested parties" unless you allow them to by not unchecking some of the "agreement" boxes.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Naomi N
Naomi N
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 232
How will you expand the solution when you have StartDate EndDate fields in your table and you want continuous intervals, e.g.

01/01/2010 - 01/15/2010
01/16/2010 - 02/10/2010

The above two intervals should come as

01/01/2010 - 02/10/2010

I've never seen a blog explaining this more complicated case, although I have seen and tried myself to solve this problem.
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14971 Visits: 38985
Naomi N (11/30/2011)
How will you expand the solution when you have StartDate EndDate fields in your table and you want continuous intervals, e.g.

01/01/2010 - 01/15/2010
01/16/2010 - 02/10/2010

The above two intervals should come as

01/01/2010 - 02/10/2010

I've never seen a blog explaining this more complicated case, although I have seen and tried myself to solve this problem.


Naomi you'll want to start a separate thread to discuss this, but the trick is to use a Tally/Calendar table to fill/generate the dates between the two dates...it's a closely related idea to the Tally Splitting functionality.

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Mark Cowne
Mark Cowne
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2095 Visits: 22800
Naomi N (11/30/2011)
How will you expand the solution when you have StartDate EndDate fields in your table and you want continuous intervals, e.g.

01/01/2010 - 01/15/2010
01/16/2010 - 02/10/2010

The above two intervals should come as

01/01/2010 - 02/10/2010

I've never seen a blog explaining this more complicated case, although I have seen and tried myself to solve this problem.


There's a similar thread here

http://www.sqlservercentral.com/Forums/Topic1125847-392-1.aspx

____________________________________________________

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




Naomi N
Naomi N
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 232
Thank you both. The expanding with Numbers table was my idea as well and I see on the second page of pointed discussion that this was the approach taken by Jeff. I am now looking at Itzik's article.
rfr.ferrari
rfr.ferrari
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: 1351 Visits: 13620
nice article Jeff!
thanks!!!!


rfr.ferrari
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
the period you fastest growing is the most difficult period of your life!

Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45189 Visits: 39925
rfr.ferrari (2/18/2012)
nice article Jeff!
thanks!!!!


You bet. Thank you for the feedback.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4269 Visits: 6431
OMG! w00t

I must have read this article 5 times at least and I never could quite get a grip on it.

Finally, I've been able to apply it to a real problem! http://www.sqlservercentral.com/Forums/Topic1364849-392-1.aspx?Update=1 Not that I doubted its applicability, just couldn't quite achieve that nirvana of understanding.

Not sure that I have yet, but at least this is a start. Cool

As always, thanks Jeff!


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
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4269 Visits: 6431
Jeff Moden (1/16/2011)
Sachin Nandanwar (1/16/2011)
Well I just stumbled upon this article.I tried to do it using quirky update method and seems to be working but haven't tested it on a huge no of rows though.

--=============================================================================
-- Create the test data. This is NOT a part of the solution.
-- This is virually instantaneous.
--=============================================================================
--===== Conditionally drop the test table to make reruns easier.
IF OBJECT_ID('tempdb..#MyHead','U') IS NOT NULL
DROP TABLE #MyHead
;
GO
--===== Create the test table
CREATE TABLE #MyHead
(SomeDate DATETIME, id int DEFAULT(0))
;
--===== Populate the test table with test data
INSERT INTO #MyHead
(SomeDate)
SELECT '2010-01-01' UNION ALL --1st "Group" of dates (StartDate and EndDate)
SELECT '2010-01-01' UNION ALL --Duplicate date
SELECT '2010-01-03' UNION ALL --2nd "Group" of dates (StartDate and EndDate)
SELECT '2010-01-05' UNION ALL --3rd "Group" of dates (StartDate)
SELECT '2010-01-06' UNION ALL --3rd "Group" of dates (EndDate)
SELECT '2010-01-10' UNION ALL --4th "Group" of dates (StartDate)
SELECT '2010-01-10' UNION ALL --Duplicate date
SELECT '2010-01-11' UNION ALL --4th "Group" of dates
SELECT '2010-01-11' UNION ALL --Duplicate date
SELECT '2010-01-11' UNION ALL --Duplicate date
SELECT '2010-01-12' --4th "Group" of dates (EndDate)
;

declare @ordse int=0
declare @somedate datetime=''

update #MyHead set @ordse=ID=case when somedate=@somedate+1 or @somedate=somedate then @ordse+1 else @ordse-1 end,@somedate=somedate

select min(somedate)min,max(somedate)max,DATEDIFF(dd,min(SomeDate)-1,max(SomeDate))Diff from
(
select *,id-ROW_NUMBER()over(order by (select 1))id1 from #MyHead
)t group by id1 order by min(SomeDate)

drop table #MyHead




I realize the intentions are good here and thank you for that but there are a couple of problems with the code there. For one, it breaks several of the rules for doing a Quirky Update. It's tough enough for me to defend the use of the Quirky Update as it is. If you're going to use it and post such solutions, please follow the rules for its use. Thanks.

Second, although the Quirky Update does the job, isn't a panacea and there's simply no need no need for it here. It requires the use of an extra column and would necessarily require the copying of data from a permanent table to a Temp Table if the column couldn't be added to the permanent table.

Last but not least, since you still do a SELECT with aggregates, I believe you'll find that the Quirky Update method is actually a bit slower than conventional methods, in this case.


I can confirm the last statement, namely that QU performs slower than Jeff's method.

Because I was so conceptually challenged to understand what Jeff had done at first, I tried to see if I could apply QU to this case. While I did get it to work (QU I understand, including the rules :-)), it was definitely slower.


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