Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Group Islands of Contiguous Dates (SQL Spackle) Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, June 27, 2011 5:07 PM
 Grasshopper 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-Forever Group: General Forum Members Last Login: Today @ 1:40 PM Points: 42,046, Visits: 39,425
Post #1132577
 Posted Wednesday, November 30, 2011 6:34 AM
 Grasshopper Group: General Forum Members Last Login: Wednesday, May 11, 2016 9:11 AM Points: 19, 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/201001/16/2010 - 02/10/2010 The above two intervals should come as01/01/2010 - 02/10/2010I'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 Group: General Forum Members Last Login: Today @ 9:48 AM Points: 14,541, Visits: 38,381
 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/201001/16/2010 - 02/10/2010 The above two intervals should come as01/01/2010 - 02/10/2010I'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!
Post #1213940
 Posted Wednesday, November 30, 2011 7:48 AM
 SSCrazy Group: General Forum Members Last Login: Today @ 2:42 PM Points: 2,014, Visits: 22,605
 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/201001/16/2010 - 02/10/2010 The above two intervals should come as01/01/2010 - 02/10/2010I'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 herehttp://www.sqlservercentral.com/Forums/Topic1125847-392-1.aspx ____________________________________________________Deja View - The strange feeling that somewhere, sometime you've optimised this query beforeHow to get the best help on a forumhttp://www.sqlservercentral.com/articles/Best+Practices/61537
Post #1213951
 Posted Wednesday, November 30, 2011 8:39 AM
 Grasshopper Group: General Forum Members Last Login: Wednesday, May 11, 2016 9:11 AM Points: 19, 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.
Post #1213993
 Posted Saturday, February 18, 2012 11:28 AM
 Ten Centuries Group: General Forum Members Last Login: Friday, November 25, 2016 5:31 AM Points: 1,265, Visits: 13,618
 nice article Jeff!thanks!!!! rfr.ferrariDBA - SQL Server 2008MCITP | MCTS remember is live or suffer twice!the period you fastest growing is the most difficult period of your life!
Post #1254354
 Posted Saturday, February 18, 2012 11:57 AM
 SSC-Forever Group: General Forum Members Last Login: Today @ 1:40 PM Points: 42,046, Visits: 39,425
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1254357
 Posted Thursday, September 27, 2012 7:07 PM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 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!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1365569
 Posted Thursday, September 27, 2012 10:26 PM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 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=0declare @somedate datetime=''update #MyHead set @ordse=ID=case when somedate=@somedate+1 or @somedate=somedate then @ordse+1 else @ordse-1 end,@somedate=somedateselect 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
Post #1365594

 Permissions