Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

An Efficient Set-based Solution for Islands and Gaps Expand / Collapse
Author
Message
Posted Thursday, November 11, 2004 2:51 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:07 AM
Points: 10, Visits: 144

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

Post #223869
Posted Tuesday, October 25, 2005 11:19 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP 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-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:48 AM
Points: 36,759, Visits: 31,214
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #437872
Posted Wednesday, December 21, 2011 8:00 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, March 7, 2014 2:41 AM
Points: 50, Visits: 396
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

EDIT: 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-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:48 AM
Points: 36,759, Visits: 31,214
jordonpilling (12/21/2011)
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

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


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1226054
Posted Thursday, December 22, 2011 6:10 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, March 7, 2014 2:41 AM
Points: 50, Visits: 396
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....
Post #1226056
Posted Thursday, December 22, 2011 6:18 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:48 AM
Points: 36,759, Visits: 31,214
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1226057
Posted Thursday, December 22, 2011 6:24 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:48 AM
Points: 36,759, Visits: 31,214
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1226058
Posted Thursday, December 22, 2011 7:14 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:48 AM
Points: 36,759, Visits: 31,214
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1226061
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse