Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


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

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Goce Smilevski » An Efficient Set-based Solution for Islands...
Add to briefcase

An Efficient Set-based Solution for Islands and Gaps Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Goce Smilevski
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
Peet Schultz-223955
Posted Tuesday, September 27, 2005 7:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 1:46 AM
Points: 10, Visits: 102

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
SuperDBA-207096
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 02, 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
Jeff Moden
Posted Wednesday, January 02, 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: Yesterday @ 5:33 PM
Points: 32,902, Visits: 26,783
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #437872
jordonpilling
Posted Wednesday, December 21, 2011 8:00 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 22, 2013 3:08 AM
Points: 49, Visits: 374
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
Jeff Moden
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: Yesterday @ 5:33 PM
Points: 32,902, Visits: 26,783
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1226054
jordonpilling
Posted Thursday, December 22, 2011 6:10 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 22, 2013 3:08 AM
Points: 49, Visits: 374
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
Jeff Moden
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: Yesterday @ 5:33 PM
Points: 32,902, Visits: 26,783
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1226057
Jeff Moden
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: Yesterday @ 5:33 PM
Points: 32,902, Visits: 26,783
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1226058
Jeff Moden
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: Yesterday @ 5:33 PM
Points: 32,902, Visits: 26,783
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1226061
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.