Advanced Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Advanced Query

Author

Topic

jazzin31
Starting Member

4 Posts

Posted - 2004-08-09 : 09:29:30

I am having a problem coming up with a solution for the max number of records during a time span (start and end time). The key here is overlapping records. If I have 3 records all with different start and end times, they all may or may not overlap at some point. Any ideas?? Thanks,

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-09 : 09:39:17

so how do you intend to handle overlaps??

select Date between '2004-07-01' and '2004-08-01'

will give you dates that are in that interval.

Go with the flow & have fun! Else fight the flow :)

jazzin31
Starting Member

4 Posts

Posted - 2004-08-09 : 10:40:25

The overlaps are what I am trying to count. Essentially the max number of overlaps in one day.

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-08-09 : 12:12:10

Can you post the DDL (basically the CREATE TABLE), some INSERT statements, and the output that you'd like to see? I'm pretty fuzzy on understanding what you want to show, and guessing probably isn't a good thing.

-PatP

jazzin31
Starting Member

4 Posts

Posted - 2004-08-09 : 13:28:47

Based on the code below, the output I want is the max # of record datetime overlaps, which is 3. I hope this helps. If not I'll go into more detail.

Here's the sample:

DECLARE @test TABLE (
recid int PRIMARY KEY IDENTITY(1,1),
recstart datetime,
recend datetime
)

INSERT INTO @test (recstart, recend)
VALUES ('8/9/2004 7:00', '8/9/2004 8:00')
INSERT INTO @test (recstart, recend)
VALUES ('8/9/2004 8:00', '8/9/2004 17:00')
INSERT INTO @test (recstart, recend)
VALUES ('8/9/2004 8:15', '8/9/2004 12:00')
INSERT INTO @test (recstart, recend)
VALUES ('8/9/2004 8:30', '8/9/2004 9:00')
INSERT INTO @test (recstart, recend)
VALUES ('8/9/2004 17:30', '8/9/2004 19:00')
INSERT INTO @test (recstart, recend)
VALUES ('8/9/2004 18:00', '8/9/2004 18:30')

jazzin31
Starting Member

4 Posts

Posted - 2004-08-09 : 15:25:19

I think I've got it... Anyone see any problems or other ways not using a function?

Use this select statement:

SELECT MAX(dbo.fx_overlap(recid, recstart, recend)) from a_test

with this function:

ALTER FUNCTION fx_overlap
(
@recid int,
@recstart datetime,
@recend datetime
)
RETURNS INT AS
BEGIN

DECLARE @tmp int
SET @tmp = (
SELECT COUNT(*)
FROM a_test a
WHERE (a.recid <> @recid AND
((a.recstart >= @recstart AND a.recstart < @recend)
OR (a.recend > @recstart AND a.recend <= @recend)
OR (a.recstart < @recstart AND a.recend > @recend))
)
)

RETURN @tmp + 1

END

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-08-09 : 15:55:55

I count four overlaps because the 07:00-08:00 overlaps for a minute with 08:00-17:00. If you agree with my count, you could use:

SELECT Count(*)
   FROM @test AS a
   WHERE EXISTS (SELECT *
      FROM @test AS b
      WHERE (b.recstart <> a.recstart OR b.recend <> a.recend)
         AND b.recstart BETWEEN a.recstart AND a.recend)

-PatP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources