# An Efficient Set-based Solution for Islands and Gaps

• Comments posted to this topic are about the item An Efficient Set-based Solution for Islands and Gaps

• Hi there

Is 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.

Regards

Peet

• 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

• 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. 😛

` 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.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• EDIT: Answered on StackOverflow: http://stackoverflow.com/questions/8592764/crosstabbing-rows-based-on-sequence-islands-in-a-list-of-times

Hi Jeff

Loving 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]`

`GO`

`INSERT [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 0`

`001 12/12/2011 09:07:00 09:10:00 1`

`001 12/12/2011 09:11:00 09:13:00 2`

`001 12/12/2011 09:14:00 09:18:00 1`

`001 12/12/2011 09:19:00 09:25:00 0`

`002 13/12/2011 09:00:00 09:06:00 0`

`002 13/12/2011 09:07:00 09:10:00 1`

`002 13/12/2011 09:11:00 09:13:00 2`

`002 13/12/2011 09:14:00 09:17:00 1`

`002 13/12/2011 09:18:00 09:21:00 3`

`002 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.

Jordon

Knock Knock, Who's There?, sp underscore, sp underscore who?
spid1, spid2, spid3....

• jordonpilling (12/21/2011)

Hi Jeff

Loving 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]`

`GO`

`INSERT [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 0`

`001 12/12/2011 09:07:00 09:10:00 1`

`001 12/12/2011 09:11:00 09:13:00 2`

`001 12/12/2011 09:14:00 09:18:00 1`

`001 12/12/2011 09:19:00 09:25:00 0`

`002 13/12/2011 09:00:00 09:06:00 0`

`002 13/12/2011 09:07:00 09:10:00 1`

`002 13/12/2011 09:11:00 09:13:00 2`

`002 13/12/2011 09:14:00 09:17:00 1`

`002 13/12/2011 09:18:00 09:21:00 3`

`002 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.

Jordon

I'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.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• 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 Jeff

The 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....

• 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.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• 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.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• 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.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• this seems a bit disconnected from the original motivating article as the only link in the article points to a generic Microsoft search page not the orginal motivating and perhaps context explaining article.

so doesn't do much to make clear what you or anyone are trying to achieve without a laborious dive into the code. I find it detracts from the normal value of ssc articles. and subsequently makes it very unlikely many would dive in unless already deeply familiar with the terminology of islands and gaps.

did microsoft yank the original article? http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro04/html/sp04d8.asp?

The only thing I noticed is that the code for Listing 4 and Listing 7 are the wrong way round.

• Testing and Results

I used the UDF from the  script I contributed .

Please fix link to  script  so I can compare my gaps query.

• This reply was modified 2 years, 5 months ago by  JediSQL.

Sincerely,
Daniel

Viewing 13 posts - 1 through 12 (of 12 total)