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

 An Efficient Set-based Solution for Islands and Gaps Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, November 11, 2004 2:51 PM
 SSC Veteran Group: General Forum Members Last Login: Tuesday, October 30, 2012 5:30 PM Points: 246, Visits: 46
 Comments posted to this topic are about the content posted at http://www.sqlservercentra
Post #145928
 Posted Tuesday, September 27, 2005 7:01 AM
 Grasshopper Group: General Forum Members Last Login: Friday, September 4, 2015 12:06 AM Points: 10, Visits: 183
 Hi thereIs there any way to apply the same principle to a series of records with  start date / end dates and then to determine where the gaps are. RegardsPeet
Post #223869
 Posted Tuesday, October 25, 2005 11:19 AM
 UDP Broadcaster Group: General Forum Members Last Login: Wednesday, January 2, 2013 12:15 PM Points: 1,443, Visits: 711
 OK - here's a question - why are holes and islands such a bad thing in an identity field?  Should the always be 'closed up' ?Mark
Post #232096
 Posted Wednesday, January 2, 2008 3:04 AM
 SSC-Forever Group: General Forum Members Last Login: Today @ 1:40 PM Points: 42,046, Visits: 39,425
 I happened to be doing a little research on different methods for finding "gaps" and ran across this article. Here's a much faster way to find gaps... and, it doesn't use Temp Tables, Table Variables, or UDF's... No, there's nothing missing. This is the "find gap" code in it's entirety. :P` SELECT GapStart = (SELECT ISNULL(MAX(b.ID),0)+1 FROM yourtable b WHERE b.ID < a.ID), GapEnd = ID - 1 FROM yourtable a WHERE a.ID - 1 NOT IN (SELECT ID FROM yourtable) AND a.ID - 1 > 0`My poor "old" computer is a 1.8GHz single cpu P5 and was built in 2002. This method is so fast that even when I return the gaps to the screen in the Grid mode, I still get at least 7 times the performance as follows...`Upper bound Author's Method This Method Performance Ratio----------- --------------- ----------- ----------------- 1,000,000 89 11 8.1:1 2,000,000 188 19 9.9:1 3,000,000 354 33 10.7:1 4,000,000 490 45 10.8:1 5,000,000 648 54 12.0:1 6,000,000 799 57 14:0:1`Of course, all times assume that you have a decent index, preferably a clustered one, on the ID column.I agree with Mark's statement above, though... it's normally a "Bozo-no-no" to even think about reusing ID's especially if they're of the IDENTITY flavor.Peet,If your dates are "whole" dates, this method will also work for your date problem. Just make sure you have an index on the date column. --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 #437872
 Posted Wednesday, December 21, 2011 8:00 AM
 Valued Member Group: General Forum Members Last Login: Thursday, August 11, 2016 5:22 AM Points: 50, Visits: 451
 EDIT: Answered on StackOverflow: http://stackoverflow.com/questions/8592764/crosstabbing-rows-based-on-sequence-islands-in-a-list-of-timesHi JeffLoving your solution, but i'm struggling to apply it to a scenario where the ID is a compund of multiple columns.Let me explain with the following example:`CREATE TABLE [dbo].[tbl_Example]( [Date] [date] NULL, [Time] [time](0) NULL, [Branch] [varchar](3) NULL, [Count] [int] NULL) ON [PRIMARY]GOINSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00907E0000000000 AS Time), N'001', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00CC7E0000000000 AS Time), N'001', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00087F0000000000 AS Time), N'001', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00447F0000000000 AS Time), N'001', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00807F0000000000 AS Time), N'001', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00BC7F0000000000 AS Time), N'001', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00F87F0000000000 AS Time), N'001', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x0034800000000000 AS Time), N'001', 1)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x0070800000000000 AS Time), N'001', 1)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00AC800000000000 AS Time), N'001', 1)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00E8800000000000 AS Time), N'001', 1)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x0024810000000000 AS Time), N'001', 2)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x0060810000000000 AS Time), N'001', 2)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x009C810000000000 AS Time), N'001', 2)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00D8810000000000 AS Time), N'001', 1)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x0014820000000000 AS Time), N'001', 1)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x0050820000000000 AS Time), N'001', 1)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x008C820000000000 AS Time), N'001', 1)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00C8820000000000 AS Time), N'001', 1)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x0004830000000000 AS Time), N'001', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x0040830000000000 AS Time), N'001', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x007C830000000000 AS Time), N'001', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00B8830000000000 AS Time), N'001', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00F4830000000000 AS Time), N'001', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x0030840000000000 AS Time), N'001', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x006C840000000000 AS Time), N'001', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00907E0000000000 AS Time), N'002', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00CC7E0000000000 AS Time), N'002', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00087F0000000000 AS Time), N'002', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00447F0000000000 AS Time), N'002', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00807F0000000000 AS Time), N'002', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00BC7F0000000000 AS Time), N'002', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00F87F0000000000 AS Time), N'002', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x0034800000000000 AS Time), N'002', 1)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x0070800000000000 AS Time), N'002', 1)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00AC800000000000 AS Time), N'002', 1)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00E8800000000000 AS Time), N'002', 1)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x0024810000000000 AS Time), N'002', 2)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x0060810000000000 AS Time), N'002', 2)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x009C810000000000 AS Time), N'002', 2)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00D8810000000000 AS Time), N'002', 1)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x0014820000000000 AS Time), N'002', 1)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x0050820000000000 AS Time), N'002', 1)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x008C820000000000 AS Time), N'002', 1)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00C8820000000000 AS Time), N'002', 3)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x0004830000000000 AS Time), N'002', 3)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x0040830000000000 AS Time), N'002', 3)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x007C830000000000 AS Time), N'002', 3)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00B8830000000000 AS Time), N'002', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00F4830000000000 AS Time), N'002', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x0030840000000000 AS Time), N'002', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x006C840000000000 AS Time), N'002', 0)`I need to select data as follows:`Branch Date Start Time End Time Count====== ==== ========== ======== ========001 12/12/2011 09:00:00 09:06:00 0001 12/12/2011 09:07:00 09:10:00 1001 12/12/2011 09:11:00 09:13:00 2001 12/12/2011 09:14:00 09:18:00 1001 12/12/2011 09:19:00 09:25:00 0002 13/12/2011 09:00:00 09:06:00 0002 13/12/2011 09:07:00 09:10:00 1002 13/12/2011 09:11:00 09:13:00 2002 13/12/2011 09:14:00 09:17:00 1002 13/12/2011 09:18:00 09:21:00 3002 13/12/2011 09:22:00 09:25:00 0`Now, i cant just PARTITION by the Count Column as you may have the same count value spanning multiple days like in my example. Now, i need to deterct islands within each Branch & Date and select the start and end of each island and the Count value, making sure to start a new island if the count changes.Cant quite get my head round this, so any help you can throw my way would be super.Cheers.JordonEDIT: Answered on StackOverflow: http://stackoverflow.com/questions/8592764/crosstabbing-rows-based-on-sequence-islands-in-a-list-of-times Knock Knock, Who's There?, sp underscore, sp underscore who?spid1, spid2, spid3....
Post #1225158
 Posted Thursday, December 22, 2011 5:42 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 1:40 PM Points: 42,046, Visits: 39,425
 jordonpilling (12/21/2011)EDIT: Answered on StackOverflow: http://stackoverflow.com/questions/8592764/crosstabbing-rows-based-on-sequence-islands-in-a-list-of-timesHi JeffLoving your solution, but i'm struggling to apply it to a scenario where the ID is a compund of multiple columns.Let me explain with the following example:`CREATE TABLE [dbo].[tbl_Example]( [Date] [date] NULL, [Time] [time](0) NULL, [Branch] [varchar](3) NULL, [Count] [int] NULL) ON [PRIMARY]GOINSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00907E0000000000 AS Time), N'001', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00CC7E0000000000 AS Time), N'001', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00087F0000000000 AS Time), N'001', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00447F0000000000 AS Time), N'001', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00807F0000000000 AS Time), N'001', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00BC7F0000000000 AS Time), N'001', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00F87F0000000000 AS Time), N'001', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x0034800000000000 AS Time), N'001', 1)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x0070800000000000 AS Time), N'001', 1)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00AC800000000000 AS Time), N'001', 1)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00E8800000000000 AS Time), N'001', 1)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x0024810000000000 AS Time), N'001', 2)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x0060810000000000 AS Time), N'001', 2)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x009C810000000000 AS Time), N'001', 2)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00D8810000000000 AS Time), N'001', 1)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x0014820000000000 AS Time), N'001', 1)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x0050820000000000 AS Time), N'001', 1)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x008C820000000000 AS Time), N'001', 1)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00C8820000000000 AS Time), N'001', 1)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x0004830000000000 AS Time), N'001', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x0040830000000000 AS Time), N'001', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x007C830000000000 AS Time), N'001', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00B8830000000000 AS Time), N'001', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00F4830000000000 AS Time), N'001', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x0030840000000000 AS Time), N'001', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x006C840000000000 AS Time), N'001', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00907E0000000000 AS Time), N'002', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00CC7E0000000000 AS Time), N'002', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00087F0000000000 AS Time), N'002', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00447F0000000000 AS Time), N'002', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00807F0000000000 AS Time), N'002', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00BC7F0000000000 AS Time), N'002', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00F87F0000000000 AS Time), N'002', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x0034800000000000 AS Time), N'002', 1)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x0070800000000000 AS Time), N'002', 1)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00AC800000000000 AS Time), N'002', 1)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00E8800000000000 AS Time), N'002', 1)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x0024810000000000 AS Time), N'002', 2)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x0060810000000000 AS Time), N'002', 2)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x009C810000000000 AS Time), N'002', 2)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00D8810000000000 AS Time), N'002', 1)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x0014820000000000 AS Time), N'002', 1)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x0050820000000000 AS Time), N'002', 1)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x008C820000000000 AS Time), N'002', 1)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00C8820000000000 AS Time), N'002', 3)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x0004830000000000 AS Time), N'002', 3)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x0040830000000000 AS Time), N'002', 3)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x007C830000000000 AS Time), N'002', 3)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00B8830000000000 AS Time), N'002', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x00F4830000000000 AS Time), N'002', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x0030840000000000 AS Time), N'002', 0)INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x13350B00 AS Date), CAST(0x006C840000000000 AS Time), N'002', 0)`I need to select data as follows:`Branch Date Start Time End Time Count====== ==== ========== ======== ========001 12/12/2011 09:00:00 09:06:00 0001 12/12/2011 09:07:00 09:10:00 1001 12/12/2011 09:11:00 09:13:00 2001 12/12/2011 09:14:00 09:18:00 1001 12/12/2011 09:19:00 09:25:00 0002 13/12/2011 09:00:00 09:06:00 0002 13/12/2011 09:07:00 09:10:00 1002 13/12/2011 09:11:00 09:13:00 2002 13/12/2011 09:14:00 09:17:00 1002 13/12/2011 09:18:00 09:21:00 3002 13/12/2011 09:22:00 09:25:00 0`Now, i cant just PARTITION by the Count Column as you may have the same count value spanning multiple days like in my example. Now, i need to deterct islands within each Branch & Date and select the start and end of each island and the Count value, making sure to start a new island if the count changes.Cant quite get my head round this, so any help you can throw my way would be super.Cheers.JordonEDIT: Answered on StackOverflow: http://stackoverflow.com/questions/8592764/crosstabbing-rows-based-on-sequence-islands-in-a-list-of-timesI'll take a look. Thanks for the link.As a side bar, counting overlapping dates is a whole lot different than counting gaps in a sequence. There's a solution that Ben-Gan posted on his site for what you ask and it's lightning quick. I'll try to find the link for you.{EDIT} I just noticed and I'm curious... what advantage do you find in maintaining separate DATE and TIME columns? --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 #1226054
 Posted Thursday, December 22, 2011 6:10 PM
 Valued Member Group: General Forum Members Last Login: Thursday, August 11, 2016 5:22 AM Points: 50, Visits: 451
 Jeff Moden (12/22/2011){EDIT} I just noticed and I'm curious... what advantage do you find in maintaining separate DATE and TIME columns?Hi JeffThe example i posted was an example of a much bigger system, a rota system to track staff bookings for 1600 employees at 150 locations.The business requirements was to maintain single day bookings with start and end times.When i designed the schema, i had 3 choices (that i could think of anyway),Option1 - Store Start and End Time as two DateTime values = DATALENGTH of 18 (including a smallintID)Option2 - Store Date and two Time columns with precision 0 = DATALENGTH of 11 (including a smallintID)Option3 - Store a start DateTime and an INT for duration = DATALENGTH of 14 (again, including a smallintID)as you can see, a Date and 2xTime(0) was the most efficient way to store the data, and it also made querying the data much easier. And also reduced development time on the front-end as i didn't have to worry about getting the user to pick two sets of times and enforce the date part to be the same yada yada.And, perhaps most of all, ive never used these data types in a large scale system and was interested to see how they held up, and i must say i am quite impressed so far. Why do you ask anyway? are there some shortfalls i am not aware off? Knock Knock, Who's There?, sp underscore, sp underscore who?spid1, spid2, spid3....
Post #1226056
 Posted Thursday, December 22, 2011 6:18 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 1:40 PM Points: 42,046, Visits: 39,425
 jordonpilling (12/22/2011)Why do you ask anyway? are there some shortfalls i am not aware off?Possibly but I don't know for sure. I'd have to see your related queries to know for sure.I found the link I was trying to find for you. Here 'tis...http://www.solidq.com/sqj/Pages/2011-March-Issue/Packing-Intervals.aspx --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 #1226057
 Posted Thursday, December 22, 2011 6:24 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 1:40 PM Points: 42,046, Visits: 39,425
 My appologies... you're problem IS the same problem as solving for sequence islands. Let me see what I can do. --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 #1226058
 Posted Thursday, December 22, 2011 7:14 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 1:40 PM Points: 42,046, Visits: 39,425
 I took a look at the link you posted. It's good code and it definitely solves your problem. Not sure why I missed the basis of your original request, but here's how the solution that you used works. http://www.sqlservercentral.com/articles/T-SQL/71550/ --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 #1226061

 Permissions