Crosstabbing rows based on sequence islands in a list of times

Question

Just writing the question title hurt my head!

Please bear with me! Please see the following DDL & Dummy Data:

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(0x00C4860000000000 AS Time), N'001', 0)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x0000870000000000 AS Time), N'001', 0)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x003C870000000000 AS Time), N'001', 0)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x0078870000000000 AS Time), N'001', 0)
INSERT [dbo].[tbl_Example] ([Date], [Time], [Branch], [Count]) VALUES (CAST(0x12350B00 AS Date), CAST(0x00B4870000000000 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)

Now, i need to render the dummy data as follows:

Branch  Date        Start Time  End Time    EntryCount
======  ====        ==========  ========    ========
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:20:00    0
001     12/12/2011  09:35:00    09:39: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

As you can see, i need to extract the island start and end time as seperate columns, making care to split islands based on the Branch, Date & EntryCount. Ive seen clever solutions to do this when your dealing with a simple numeric sequence - but i cant get them to fit in this scenario. I'm interested to see how you folks would approach this.

I'm using SQL Server 2008+

A screenshot showing the Island boundres, either because the branch changed, the date, or the Count

enter image description here

Two questions please: What do you mean by 'island'? I've never heard that before in SQL. Also, how does [tbl_Example].[Time] relate to the StartTime and EndTime? — PowerUser, Dec 21 '11 at 16:32
(note that from 2008 you have the [table value constructor](http://msdn.microsoft.com/en-us/library/dd776382.aspx) available) — AakashM, Dec 21 '11 at 16:33
@Power see http://msdn.microsoft.com/en-us/library/aa175780%28v=sql.80%29.aspx for gaps and islands — AakashM, Dec 21 '11 at 16:33
@PowerUser, Islanding is a common term used to identify the problem of spotting sequences & gaps, for instance, 1,2,3,6,7,8,12,13 has 3 islands, 1-3, 6-8 & 12-13. Examples of such issues: http://msdn.microsoft.com/en-us/library/aa175780(v=sql.80).aspx & http://stackoverflow.com/questions/472732/how-to-group-ranged-values-using-sql-server etc The StartTime and EndTime will be the [Time] value as it was at the start and end of each island. I'm sure there is a better way to explain this, but ive reached the char limit lol — HeavenCore, Dec 21 '11 at 16:37
I've had similar problems, but didn't know there was a term for it. If the SQL approach fails, could you write a script to loop thru the source table? (this would be a last resort) — PowerUser, Dec 21 '11 at 16:49
@PowerUser Aye, although the system i'm working on will be generating this data based on millions of rows, i really want to avoid recursive methods and stick to set based method if i can - i'm sure it's possible - just cant quite nail it. — HeavenCore, Dec 21 '11 at 16:53
@HeavenCore - Do you have a maximum of one row per `Date,Time`? — Martin Smith, Dec 21 '11 at 16:54
@Martin Smith I'm afraid not, the same date & time could exist for multiple branches — HeavenCore, Dec 21 '11 at 16:55
@HeavenCore - So is `Date,Time,Branch` constrained to be unique? Also what denotes the end of an island? If you have a gap in minutes but after the gap the next row has the same value as the previous one is that all one island? — Martin Smith, Dec 21 '11 at 16:59
@Martin Smith Yes, This data is actually the result of a huuuuge CTE that compares opening hours to rota systems to flag times where shops are open and nobody has been booked to work, so it does not have constraints perse, but yes, the data should be structured in a way that means Date, Time & Branch are unique. — HeavenCore, Dec 21 '11 at 17:02
@HeavenCore - And what about my other question? If there is a gap in the data for a `branch` but then the next minute for which data is present has the same `count` as the previous one is that all one island? — Martin Smith, Dec 21 '11 at 17:08
@Martin Smith Apologies, I missed the 2nd part of your question. An island should be split whenever the count changes, even if this is within a branch & date. — HeavenCore, Dec 21 '11 at 17:17
That wasn't the question but I realise from the data that the gap from `09:20:00` to `09:35:00` is treated as starting a new island so answered myself. — Martin Smith, Dec 21 '11 at 17:22

score 2 · Accepted Answer · answered Dec 21 '11 at 17:24

;WITH T
     AS (SELECT *,
                DATEPART(HOUR, Time) * 60 + 
                DATEPART(MINUTE, Time) -
                ROW_NUMBER() OVER (PARTITION BY Branch, 
                                                Date, 
                                                Count 
                                        ORDER BY Time) AS Grp
         FROM   [dbo].[tbl_Example])
SELECT Branch,
       Date,
       MIN(Time) AS [Start Time],
       MAX(Time) AS [End Time],
       [Count]   AS EntryCount
FROM   T
GROUP  BY Branch,
          Date,
          [Count],
          Grp
ORDER  BY Branch,
          Date,
          [Start Time]

That sir, is grade-a quality - thankyou so much for your help \o/ — HeavenCore, Dec 21 '11 at 17:34

Crosstabbing rows based on sequence islands in a list of times

1 Answers1

Linked