Checking to see if times overlap

Question

Post reply

Checking to see if times overlap

tan110

Hall of Fame

Points: 3855
More actions
January 27, 2010 at 1:13 pm

#216088

I have the following table:
CREATE TABLE [dbo].[VerizonDetailsFinal](
[From Number] [varchar](50) NULL,
[Usage Date] [varchar](50) NULL,
[ConnectTime] [time](7) NULL,
[endTime] [time](7) NULL,
[overlap] [bit] NULL
)
Some data from the table:
phoneNumberUsage DateConnectTime endTime overlap
201259339812/25/200911:54:35.000000011:55:29.0000000NULL
201259339812/25/200911:55:34.000000011:56:28.0000000NULL
201560849012/30/200913:24:55.000000013:25:55.0000000NULL
201560849012/30/200913:26:04.000000013:36:46.0000000NULL
201793264001/11/201008:44:35.000000008:49:17.0000000NULL
201793264001/11/201008:49:26.000000008:49:44.0000000NULL
202269700012/23/200913:59:02.000000014:00:02.0000000NULL
202269700012/23/200914:00:12.000000014:01:42.0000000NULL
202580820012/22/200914:52:48.000000014:54:06.0000000NULL
202580820012/22/200915:09:05.000000015:13:23.0000000NULL
I want set the overlap flag to 1 if the phoneNumber.endTime overlaps with the Connectime in the record below it, if its the same [phoneNumber] and same [Usage Date]. Whats the best way to go about this?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Could you repost the sample data as a series of INSERT INTO statements?

tan110 Hall of Fame Points: 3855 More actions · Answer 2

Insert into dbo.VerizonDetailsFinal ([From Number], [Usage Date], [ConnectTime], [endTime]) values ('2012593398','40172','11:54:35.0000000','11:55:29.0000000')

Insert into dbo.VerizonDetailsFinal ([From Number], [Usage Date], [ConnectTime], [endTime]) values ('2012593398','40172','11:55:34.0000000','11:56:28.0000000')

Insert into dbo.VerizonDetailsFinal ([From Number], [Usage Date], [ConnectTime], [endTime]) values ('2015608490','40177','13:24:55.0000000','13:25:55.0000000')

Insert into dbo.VerizonDetailsFinal ([From Number], [Usage Date], [ConnectTime], [endTime]) values ('2015608490','40177','13:26:04.0000000','13:36:46.0000000')

Insert into dbo.VerizonDetailsFinal ([From Number], [Usage Date], [ConnectTime], [endTime]) values ('2017932640','40189','08:44:35.0000000','08:49:17.0000000')

Insert into dbo.VerizonDetailsFinal ([From Number], [Usage Date], [ConnectTime], [endTime]) values ('2017932640','40189','08:49:26.0000000','08:49:44.0000000')

Insert into dbo.VerizonDetailsFinal ([From Number], [Usage Date], [ConnectTime], [endTime]) values ('2022697000','40170','13:59:02.0000000','14:00:02.0000000')

Insert into dbo.VerizonDetailsFinal ([From Number], [Usage Date], [ConnectTime], [endTime]) values ('2022697000','40170','14:00:12.0000000','14:01:42.0000000')

Insert into dbo.VerizonDetailsFinal ([From Number], [Usage Date], [ConnectTime], [endTime]) values ('2025808200','40169','14:52:48.0000000','14:54:06.0000000')

Insert into dbo.VerizonDetailsFinal ([From Number], [Usage Date], [ConnectTime], [endTime]) values ('2025808200','40169','15:09:05.0000000','15:13:23.0000000')

ohack SSCrazy Points: 2043 More actions · Answer 3

Hi,

Well this is how I'd do it:

;WITH x AS(

SELECT [From Number], [Usage Date], ConnectTime, endTime, overlap,

ROW_NUMBER() OVER(ORDER BY [From Number]) Id

FROM VerizonDetailsFinal

)

UPDATE x1

SET overlap = 1

FROM x x1

JOIN x x2 ON x1.Id = x2.Id - 1

AND x1.[From Number] = x2.[From Number]

AND x1.[Usage Date] = x2.[Usage Date]

AND x1.endTime >= x2.ConnectTime

I've used a Common Table Expression because you're otherwise lacking an Id column - if you've got an Id column somewhere or can add one then you wouldn't need that first bit. The other thing was that none of the data you provided actually did overlap.

tan110 Hall of Fame Points: 3855 More actions · Answer 4

tan110

Hall of Fame

Points: 3855

January 28, 2010 at 7:19 am

#1109990

Thats great! Thanks for all support.