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 «««23456»»

Group Islands of Contiguous Dates (SQL Spackle) Expand / Collapse
Author
Message
Posted Monday, June 27, 2011 5:07 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 2:56 PM
Points: 12, Visits: 107
Oh, that row_number trick is so beautiful it makes me want to cry!
Post #1132562
Posted Monday, June 27, 2011 6:45 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 7:37 PM
Points: 35,617, Visits: 32,212
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."

(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 #1132577
Posted Wednesday, November 30, 2011 6:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, June 7, 2014 8:55 PM
Points: 19, Visits: 201
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.
Post #1213885
Posted Wednesday, November 30, 2011 7:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:08 PM
Points: 12,927, Visits: 32,333
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1213940
Posted Wednesday, November 30, 2011 7:48 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:59 AM
Points: 1,678, Visits: 19,556
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


____________________________________________________

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 #1213951
Posted Wednesday, November 30, 2011 8:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, June 7, 2014 8:55 PM
Points: 19, Visits: 201
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.
Post #1213993
Posted Saturday, February 18, 2012 11:28 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:08 AM
Points: 1,262, Visits: 13,557
nice article Jeff!
thanks!!!!



rfr.ferrari
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
Post #1254354
Posted Saturday, February 18, 2012 11:57 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 7:37 PM
Points: 35,617, Visits: 32,212
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."

(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 #1254357
Posted Thursday, September 27, 2012 7:07 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 24, 2014 4:53 AM
Points: 3,422, Visits: 5,368
OMG!

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.

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!
Post #1365569
Posted Thursday, September 27, 2012 10:26 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 24, 2014 4:53 AM
Points: 3,422, Visits: 5,368
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!
Post #1365594
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse