InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 20 Jun 2013 06:23:52 GMT20http://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspxThank you for the feedback, Nelson, and welcome aboard!Fri, 30 Nov 2012 23:15:23 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspxVery nice, thanks for the article. CTE's can be really confusing.Fri, 30 Nov 2012 21:14:17 GMTnelson4183http://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspxSorry for the late reply, Dwain. Thanks for the research and the confirmation.Sun, 21 Oct 2012 18:45:41 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspx[quote][b]Jeff Moden (1/16/2011)[/b][hr][quote][b]Sachin Nandanwar (1/16/2011)[/b][hr]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.[code="sql"]--=============================================================================-- 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[/code][/quote]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.[/quote]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.Thu, 27 Sep 2012 22:26:41 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspxOMG! :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! [url]http://www.sqlservercentral.com/Forums/Topic1364849-392-1.aspx?Update=1[/url] 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!Thu, 27 Sep 2012 19:07:31 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspx[quote][b]rfr.ferrari (2/18/2012)[/b][hr]nice article Jeff!thanks!!!![/quote]You bet. Thank you for the feedback.Sat, 18 Feb 2012 11:57:26 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspxnice article Jeff!thanks!!!!Sat, 18 Feb 2012 11:28:44 GMTrfr.ferrarihttp://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspxThank 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.Wed, 30 Nov 2011 08:39:13 GMTNaomi Nhttp://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspx[quote][b]Naomi N (11/30/2011)[/b][hr]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.[/quote]There's a similar thread here[url]http://www.sqlservercentral.com/Forums/Topic1125847-392-1.aspx[/url]Wed, 30 Nov 2011 07:48:06 GMTMark-101232http://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspx[quote][b]Naomi N (11/30/2011)[/b][hr]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.[/quote]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.Wed, 30 Nov 2011 07:36:31 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspxHow 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.Wed, 30 Nov 2011 06:34:35 GMTNaomi Nhttp://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspx[quote][b]quickdraw (6/27/2011)[/b][hr]Oh, that row_number trick is so beautiful it makes me want to cry![/quote]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:[url]http://www.solidq.com/sqj/Pages/2011-March-Issue/Packing-Intervals.aspx[/url]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.Mon, 27 Jun 2011 18:45:07 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspxOh, that row_number trick is so beautiful it makes me want to cry!Mon, 27 Jun 2011 17:07:55 GMTquickdrawhttp://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspx[quote]Sure... and sorry... I get a little touchy about people not following all the rules because of the huge amount of heat I've taken about it. Here's the link:http://www.sqlservercentral.com/articles/T-SQL/68467/[/quote]Sure.. no issues.I don't mind.:-)Thanks again for your feedback and the link.Greatly appreciate it.Mon, 17 Jan 2011 21:42:37 GMTSachin Nandanwarhttp://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspx[quote][b]Sachin Nandanwar (1/17/2011)[/b][hr][quote]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.[/quote]Thank you for your feedback.I just thought of trying it in a different way.If I remember properly I think you had an article on performance implications of quirky update on sqlservercentral.comCan you please post that link ?[/quote]Sure... and sorry... I get a little touchy about people not following all the rules because of the huge amount of heat I've taken about it. Here's the link:[url]http://www.sqlservercentral.com/articles/T-SQL/68467/[/url]Please be advised of a couple of things about that article... it's in the process of being updated for a March re-release because I screwed up on the execution plan explanation, an extremely unlikely "break" has been discovered, and just as quick as that was discovered, a method for guaranteeing the operation even past that "break" using a simple "inline" data-checker has also been discovered. So far, retesting hasn't required any changes in the rules but I'm also not done with the retesting on the new guaranteed method.Mon, 17 Jan 2011 13:22:35 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspx[quote][b]tskelley (1/17/2011)[/b][hr][quote][b]rstarr-916208 (1/16/2011)[/b][hr]Ok, I consider myself duly chastised.By the way, what does SLA mean?[/quote]SLA is Service Level Agreement. Basically, a performance and quality agreement between the vendor/programmer and company commissioning the work or service.[/quote]Thank you for the cover on that. It's one of those abbreviations that I use so often everyday with people who know what it is, I sometimes forget there may be those who don't.Mon, 17 Jan 2011 13:00:44 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspx[quote][b]rstarr-916208 (1/16/2011)[/b][hr]Ok, I consider myself duly chastised.By the way, what does SLA mean?[/quote]SLA is Service Level Agreement. Basically, a performance and quality agreement between the vendor/programmer and company commissioning the work or service.Mon, 17 Jan 2011 03:35:29 GMTtskelleyhttp://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspx[quote]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.[/quote]Thank you for your feedback.I just thought of trying it in a different way.If I remember properly I think you had an article on performance implications of quirky update on sqlservercentral.comCan you please post that link ?Mon, 17 Jan 2011 02:07:09 GMTSachin Nandanwarhttp://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspxOk, I consider myself duly chastised.By the way, what does SLA mean?Sun, 16 Jan 2011 23:14:12 GMTrstarr-916208http://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspx[quote][b]Sachin Nandanwar (1/16/2011)[/b][hr]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.[code="sql"]--=============================================================================-- 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[/code][/quote]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.Sun, 16 Jan 2011 09:37:18 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspx[quote][b]rstarr-916208 (12/8/2010)[/b][hr]I ran a test on 500 dates over the span of four years. It returned in 6 seconds. In the environment I come from that would be sufficient for any practical problems we might come up with.[/quote]Oh, be careful when making such evaluations. First, 6 seconds for only 500 rows violates every SLA for performance that I've ever come across especially for GUI code where most of the SLA's I've been exposed to require no more than 1 second to return.The other danger is that someone in a hurry may borrow your code not knowing how long it takes. If they don't actually test for performance, the end user will be the one doing the performance testing for you and taking 6 seconds for only 500 rows is going to be a problem for them. If they run into only 1000 rows, they'll find that code takes exponentially longer times measured in minutes instead of seconds.Please be careful with justifying code performance based simply on number of rows... it always ends up biting you in the end.Sun, 16 Jan 2011 09:12:21 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspx[quote][b]venoym (12/8/2010)[/b][hr]Great post Jeff!I had a thought... this could be extended to account for Times in addition to dates... just move the calculation to the lowest significant field (from day to second). I had to code an algorithm in VB to find the "islands" and the "water" for a scheduling application, this may well drop the need for that...Again, Great Job![/quote]My apologies for the late reply. Thanks for the feedback, venoym!Before you think of replacing your VB function, remember that this code solves a very specific problem where the dates are grouped only if contiguous dates are available. It won't currently handle overlapping date "Ranges" where you have a StartDate and EndDate which is typically required for scheduling problems.Sun, 16 Jan 2011 08:43:40 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspxWell 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.[code="sql"]--=============================================================================-- 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[/code]Sun, 16 Jan 2011 04:21:34 GMTSachin Nandanwarhttp://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspx[quote][b]rstarr-916208 (12/8/2010)[/b][hr]Thanks Craig, Jeff for pointing out the inefficiency. I didn't realize that using the aggregation like that would be so slow.On the other hand the original question was: "How can I determine overlapping dates?" That is not a totally trivial problem. My purpose was to suggest a solution that was relatively simple, but yet got the job done.I ran a test on 500 dates over the span of four years. It returned in 6 seconds. In the environment I come from that would be sufficient for any practical problems we might come up with.But I enjoyed reading through the other solutions as well.[/quote]Not sure where the original question of overlapping dates is from, I must have missed it even in the re-read. However, easiest way to find that is to do a DENSE_RANK on your datefield, then do an aggregate COUNT(*) by the rank. Anything with HAVING COUNT(*) > 1 will be a duplicate.EDIT: You know, I'm apparently not on the ball today, there's no reason to even use the dense_rank to find your duplicates. Just group by your date/other qualifiers and look for a having count(*) > 1.Wed, 08 Dec 2010 15:46:17 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspxThanks Craig, Jeff for pointing out the inefficiency. I didn't realize that using the aggregation like that would be so slow.On the other hand the original question was: "How can I determine overlapping dates?" That is not a totally trivial problem. My purpose was to suggest a solution that was relatively simple, but yet got the job done.I ran a test on 500 dates over the span of four years. It returned in 6 seconds. In the environment I come from that would be sufficient for any practical problems we might come up with.But I enjoyed reading through the other solutions as well.Wed, 08 Dec 2010 15:40:04 GMTrstarr-916208http://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspx[quote][b]venoym (12/8/2010)[/b][hr]Great post Jeff!I had a thought... this could be extended to account for Times in addition to dates... just move the calculation to the lowest significant field (from day to second). I had to code an algorithm in VB to find the "islands" and the "water" for a scheduling application, this may well drop the need for that...Again, Great Job![/quote]You might, but my recommendation would be to add a calculated column that is a datediff(ss, <datefield>, Min(date)), and use this technique off the resultant BIGINT.Don't do this from date 0 or something, you want to make it tight as possible. There's gonna be a LOT of seconds over the course of a year or three. :-)Wed, 08 Dec 2010 11:11:27 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspxGreat post Jeff!I had a thought... this could be extended to account for Times in addition to dates... just move the calculation to the lowest significant field (from day to second). I had to code an algorithm in VB to find the "islands" and the "water" for a scheduling application, this may well drop the need for that...Again, Great Job!Wed, 08 Dec 2010 10:26:55 GMTvenoymhttp://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspx[quote][b]Geoff A (12/7/2010)[/b][hr]yet another GREAT spackle article. (Polyfilla for the people on the other side of the pond) :-Pthanks Jeff.[/quote]Thanks for the feedback and for stopping by, Geoff. :-)Tue, 07 Dec 2010 20:07:50 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspx[quote][b]Craig Farrell (12/7/2010)[/b][hr]RStarr, while your code seems functional (I didn't extensively test it), take a look at the million row test.If you look near the bottom of the article, Jeff gives a way to build out a very large sample dataset for testing. Try your code out vs. his. You'll (most likely) notice a large discrepency. This will be primarily because of the necessary data manipulation you'll be performing. I think that will actually run the aggregation structure *per row*. This is as much about optimization as anything else. A while/cursor loop doing a single pass on the data might outperform that.[/quote]Just a follow up... The code from the article is nearly instantaneous even on the first run on 100,000 rows without an index. I stopped the other code after 6-1/2 minutes. Looking at the actual execution plan on a 100 row table, it had to "touch" more than 164,000 internal rows because, as you said, the aggregations are fully executed on a "per row" basis.Tue, 07 Dec 2010 19:55:42 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspxRStarr, while your code seems functional (I didn't extensively test it), take a look at the million row test.If you look near the bottom of the article, Jeff gives a way to build out a very large sample dataset for testing. Try your code out vs. his. You'll (most likely) notice a large discrepency. This will be primarily because of the necessary data manipulation you'll be performing. I think that will actually run the aggregation structure *per row*. This is as much about optimization as anything else. A while/cursor loop doing a single pass on the data might outperform that.Tue, 07 Dec 2010 13:03:49 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspxFor the original question, how about this:[font="Courier New"][code="sql"]select a.SomeDate, (select max(b.SomeDate) from #MyHead b -- where difference of dates equals -- count of dates in between where datediff(d,a.SomeDate,b.SomeDate) = (select count(distinct c.SomeDate) from #MyHead c where c.SomeDate > a.SomeDate and c.SomeDate <= b.SomeDate)) EndDatefrom #MyHead a[/code][/font]Tue, 07 Dec 2010 12:43:37 GMTrstarr-916208http://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspxyet another GREAT spackle article. (Polyfilla for the people on the other side of the pond) :-Pthanks Jeff.Tue, 07 Dec 2010 12:23:47 GMTGeoff Ahttp://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspx[quote][b]Jeff Moden (12/7/2010)[/b][hr]Craig... it was your code that clarified it all here. Thanks again for the wonderful "cover" while I was busy.[/quote]It was my pleasure. No code though, just a different phrasing. After the whole End Of Week calculation that I think 10 people had to chime in on just to get a different phrasing that finally made sense to me and make it all click, the value of a different voice with a slightly different viewpoint to explain the same thing has become embedded in me again. :-)P.S. What's this stuff about you needing to work and not being on SSC 24/7?! BAH! Free internet forums pay the bills!Don't they?Tue, 07 Dec 2010 10:25:37 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspx[quote][b]mtassin (12/7/2010)[/b][hr]As always when I read an article by Jeff two things happen.1. I find a very useful tip that I'm sure I'll need within 1 month of reading the article2. I am amazed at how Jeff thinks. That's such an elegant solution to that sort of problem.[/quote]You're too kind, Mark. :blush: Thanks for the awesome compliment. Like I said at the end of the article, it appears that someone else thought of the same thing and published it in a book so I'm certainly not alone in that way of thinking. What most people don't understand is that I learned a huge amount of what I know and the way I think from this very site and I'm just trying to return the favor.Glad to see that "no RBAR" flag is still flying high. :-DTue, 07 Dec 2010 10:05:52 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspx[quote][b]JJ B (12/7/2010)[/b][hr]This was new to me and you did an excellent job of explaining how it works. I was with you every step of the way. 5 stars.[/quote]Thanks for the great feedback, JJ. Good to "see" you again. :-)Tue, 07 Dec 2010 10:02:30 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspx[quote][b]nigel. (12/7/2010)[/b][hr]Nice Jeff, very nice indeed.Now, all I need is to find somewhere I can use this :-)[/quote]Thanks for the feed. With a bit of extrapolation, you can also do a similar thing to find "islands" of just about any numeric sequence. Think the inverse of "find missing identies".Tue, 07 Dec 2010 10:00:42 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspx[quote][b]autoexcrement (12/6/2010)[/b][hr]Okay, I think this is starting to make sense now. Contiguous is defined here as "at least one date entry per day during a range". Okay. And now the rest of the article makes sense too. Cool.Wow, now that I understand what's going on, this article is freaking awesome. Jeff rocks. (Craig, too.)[/quote]Now there's a huge compliment. Nah... I don't mean the "Jeff rocks" thing. I mean the huge lightbulb that just turned on. Thanks for the awesome feedback.Craig... it was your code that clarified it all here. Thanks again for the wonderful "cover" while I was busy.Tue, 07 Dec 2010 09:58:47 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspx[quote][b]tommyh (12/6/2010)[/b][hr]Nice article as a whole... a bit weak/confusing intro though. But thx to Craigs little comment it became much more clear what/why was being done. /T[/quote]The folks that know me know where the detail of my articles usually is... in the comments in the code. I should probably get into the habit of stating that, though. :pinch: Thanks for the constructive feedback, Tommy. :-)Tue, 07 Dec 2010 09:56:09 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspx[quote][b]autoexcrement (12/6/2010)[/b][hr]But how can you tell which dates are "start", "end" or "start and end" without any additional information (like the comments)? I don't see any rhyme or reason here, aside from the dates being in chronological order.[/quote]I know Craig covered this, but you actually got it right there. The dates are simply in chronolgical order and the StartDate and EndDates identify the "islands" of dates where there are no missing dates.I think a lot of folks were looking for a much more complicated answer to this simple problem.Tue, 07 Dec 2010 09:53:42 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1031000-203-1.aspx[quote][b]Craig Farrell (12/7/2010)[/b][hr]Alright, let's make the data a little more real world scenario.You have a contractor who comes by and does work as needed. You're looking for a per problem average of how long it takes for them to fix whatever issue they were brought in for. You've got a log of said person's hours:...[/quote]Very cool explanation on that post. Thanks a bunch for covering for me, Craig. I was busy at work at just couldn't get the time to respond.Tue, 07 Dec 2010 09:46:45 GMTJeff Moden