CODEWITHSUNDEEP
sql-serversql-server-2005count
Kyrill
Kyrill

Reputation: 33

Counting active items over time periods based on activity start and duration

I'm working with MS SQL Server 2005 and have following problem. I have a telephone call log and need to analyze the line utilization over time. I'll try to simplify the task by omitting details and ask only the question I have problem with. Let say the calls are logged to table with columns: date and time the call started, call duration in minutes and the line# which was used. I need to calculate, how many lines were busy at each particular moment (each minute). Actually, I'll finally use larger time periods (15 minutes, 1/2 hour etc.) by doing some aggregation, but I'll have no problem with that.

So the example source table (for 3 lines) could have following contents:

StartDateTime, Duration, LineNr
2013-01-01 10:00:00, 5, 1
2013-01-01 10:01:00, 6, 2
2013-01-01 10:03:00, 3, 3
2013-01-01 10:08:00, 2, 1
2013-01-01 10:09:00, 3, 2
2013-01-01 10:11:00, 1, 1

The resulting table to be returned from the query should be following:

Period, BusyLineCount
...
2013-01-01 09:58:00, 0
2013-01-01 09:59:00, 0
2013-01-01 10:00:00, 1
2013-01-01 10:01:00, 2
2013-01-01 10:02:00, 2
2013-01-01 10:03:00, 3
2013-01-01 10:04:00, 3
2013-01-01 10:05:00, 2
2013-01-01 10:06:00, 1
2013-01-01 10:07:00, 0
2013-01-01 10:08:00, 1
2013-01-01 10:09:00, 2
2013-01-01 10:10:00, 1
2013-01-01 10:11:00, 2
2013-01-01 10:12:00, 0
2013-01-01 10:13:00, 0
...

How can I accomplish this?

P.S. This is my first posted question here, and English isn't my native language, so please don't mind if I do something wrong. Thanks in advance for your help.

Upvotes: 3

Views: 682

Answers (1)

Lamak
Lamak

Reputation: 70668

Here is a way using recursive CTE's. This will probably perform well for a small range of times, but you'll need to test it with larger ones. 

DECLARE @Start DATETIME, @End DATETIME
SET @Start = '20130101 09:58:00'
SET @End = '20130101 10:15:00'

;WITH TimePeriods AS
(
    SELECT @Start Period
    UNION ALL
    SELECT DATEADD(MINUTE,1,Period)
    FROM TimePeriods
    WHERE DATEADD(MINUTE,1,Period) <= @End
), LineUsage AS (
    SELECT StartDateTime, Duration, LineNr
    FROM YourTable
    UNION ALL
    SELECT DATEADD(MINUTE,1,StartDateTime), Duration-1, LineNr
    FROM LineUsage
    WHERE Duration-1 > 0
)
SELECT A.Period, COUNT(DISTINCT B.LineNr) BusyLineCount
FROM TimePeriods A
LEFT JOIN LineUsage B
  ON A.Period = B.StartDateTime
GROUP BY A.Period
ORDER BY A.Period
OPTION(MAXRECURSION 0)

Here is a sql fiddle with a live demo of this.

Upvotes: 3

Related Questions