July 28, 2011 at 6:51 am
Hello All,
I've got an interesting problem for you that I've not figured out yet.
I need to develop a script, preferably one that can go into a TVF (either inline or multi-step), that can find a certain type of time concurrency. I need to find the IDs and of all entries in the following table that have more than 4 (i.e. >= 5) with an overlapping time. Start and End times are inclusive (i.e. if row 1 StartTime = row 2 EndTime then they are concurrent). Entries are considered Concurrent if the ReferenceIDs between 2 rows are the same and the times range or the time boundaries intersect. There is another wrinkle to this, take a look at Reference ID = 3 where it only has a time concurrent result of 3 for any given origin ID, while Reference IDs 2 and 4 return a 5.
Preferably I'd like to do the following: For a given ID return the maximum number of concurrent entries as well as the IDs of the concurrent entries.
Sample Table Definition
GO
CREATE TABLE dbo.TestTimes
(
ID numeric(18, 0) NOT NULL IDENTITY (1, 1),
StartTime datetime NOT NULL,
EndTime datetime NOT NULL,
ReferenceID numeric(18, 0) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.TestTimes ADD CONSTRAINT
PK_TestTimes PRIMARY KEY CLUSTERED
(
ID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Now for some sample data. This is the easiest case of showing 5 concurrent entries
INSERT INTO dbo.TestTimes ( [StartTime], [EndTime], [ReferenceID] )
VALUES ( '1/3/2011 1:00 PM', '1/3/2011 2:00 PM', 4)
INSERT INTO dbo.TestTimes ( [StartTime], [EndTime], [ReferenceID] )
VALUES ( '1/3/2011 1:00 PM', '1/3/2011 2:00 PM', 4)
INSERT INTO dbo.TestTimes ( [StartTime], [EndTime], [ReferenceID] )
VALUES ( '1/3/2011 1:00 PM', '1/3/2011 2:00 PM', 4)
INSERT INTO dbo.TestTimes ( [StartTime], [EndTime], [ReferenceID] )
VALUES ( '1/3/2011 1:00 PM', '1/3/2011 2:00 PM', 4)
INSERT INTO dbo.TestTimes ( [StartTime], [EndTime], [ReferenceID] )
VALUES ( '1/3/2011 1:00 PM', '1/3/2011 2:00 PM', 4)
Additional sample data, this is more complicated. Reference ID = 2 has 5 concurrent entries, Reference ID = 3 only has 3 concurrent entries.
INSERT INTO dbo.TestTimes ( [StartTime], [EndTime], [ReferenceID] )
VALUES ( '1/1/2011 1:00 PM', '1/1/2011 3:00 PM', 2)
INSERT INTO dbo.TestTimes ( [StartTime], [EndTime], [ReferenceID] )
VALUES ( '1/1/2011 1:00 PM', '1/1/2011 1:30 PM', 2)
INSERT INTO dbo.TestTimes ( [StartTime], [EndTime], [ReferenceID] )
VALUES ( '1/1/2011 1:15 PM', '1/1/2011 1:45 PM', 2)
INSERT INTO dbo.TestTimes ( [StartTime], [EndTime], [ReferenceID] )
VALUES ( '1/1/2011 1:30 PM', '1/1/2011 3:00 PM', 2)
INSERT INTO dbo.TestTimes ( [StartTime], [EndTime], [ReferenceID] )
VALUES ( '1/1/2011 12:00 PM', '1/1/2011 1:30 PM', 2)
INSERT INTO dbo.TestTimes ( [StartTime], [EndTime], [ReferenceID] )
VALUES ( '1/2/2011 1:00 PM', '1/2/2011 5:00 PM', 3)
INSERT INTO dbo.TestTimes ( [StartTime], [EndTime], [ReferenceID] )
VALUES ( '1/2/2011 1:00 PM', '1/2/2011 1:30 PM', 3)
INSERT INTO dbo.TestTimes ( [StartTime], [EndTime], [ReferenceID] )
VALUES ( '1/2/2011 1:15 PM', '1/2/2011 1:45 PM', 3)
INSERT INTO dbo.TestTimes ( [StartTime], [EndTime], [ReferenceID] )
VALUES ( '1/2/2011 2:00 PM', '1/2/2011 3:00 PM', 3)
INSERT INTO dbo.TestTimes ( [StartTime], [EndTime], [ReferenceID] )
VALUES ( '1/2/2011 2:30 PM', '1/2/2011 5:00 PM', 3)
July 28, 2011 at 7:55 am
After I posted I realized I hadn't included any sample code, so here is the sample I've got that doesn't work correctly. A coworker wrote this originally but it doesn't return correctly in the case of Reference ID 3.
SELECT ReferenceID, COUNT(*)
FROM (SELECT atl.ReferenceID
FROM dbo.[tFN_TimesFiltered](@StartTime, @EndTime ) atl
WHERE atl.ID <> @ID AND
atl.ReferenceID = @ReferenceID
GROUP BY ReferenceID
HAVING COUNT(*) > 5
The TVF is an Inline TVF that simply filters the TestTimes table based on a start time and an end time supplied.
i.e. the following query
SELECT
[ID], [StartTime], [EndTime], [ReferenceID]
FROM
dbo.[TestTimes]
WHERE
[StartTime] <= @EndTime AND
[EndTime] >= @StartTime
July 28, 2011 at 10:59 am
Hey guys,
Score another victory for Jeff and Tally Tables. That is the solution. Using the previously dictated table and entries here's the code. Yes I'm going to clean it up some to put into an iTVF and then allow more than just "top 1" to be returned (i.e additional filtering). I know that the STUFF can cause hidden-RBAR but it will only be executed on at most a couple of rows (only MAX values of COUNT if there is more than 1) to prevent hidden performance issues. Also I'm using a previously created Tally table with about 100K rows, so even if a start/end span many days it will work (100K minutes = about 69.4 days)
/*
valid values for what we were looking at:
ID REF ID Expected Count
1 4 5
6 2 5
11 3 3
*/
DECLARE @StartTime AS DATETIME
DECLARE @EndTime AS DATETIME
DECLARE @ID AS NUMERIC(18, 0)
DECLARE @ReferenceID AS NUMERIC(18, 0)
SET @ID = 6
SELECT
@StartTime = [StartTime],
@EndTime = [EndTime],
@ReferenceID = [ReferenceID]
FROM
dbo.TestTimes
WHERE
[ID] = @ID
SELECT
TOP 1
a.[MinuteDate],
a.[Count],
STUFF((SELECT ',' + CAST([ID] AS VARCHAR(MAX))
FROM dbo.TestTimes
WHERE [StartTime] <= A.[MinuteDate] AND
[EndTime] >= A.[MinuteDate]
FOR XML PATH('')), 1, 1, '')
FROM
(
SELECT
dates.[MinuteDate], [Count] = COUNT(*) + 1
FROM
(
SELECT
[MinuteDate] = DATEADD(MINUTE, t.Number - 1, @StartTime)
FROM
dbo.Tally t
WHERE
DATEADD(MINUTE, t.Number - 1, @StartTime) <= @EndTime) dates INNER JOIN
dbo.TestTimes tt ON tt.EndTime >= dates.[MinuteDate] AND tt.StartTime <= dates.[MinuteDate]
WHERE
tt.[ID] <> @ID AND
tt.[ReferenceID] = @ReferenceID
GROUP BY
dates.[MinuteDate]) AS a
ORDER BY a.[Count] DESC
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply