SQL Query for a range of times

Question

This may have been answered here before, but I'm not even sure how to search for it, so my apologies if it's a duplicate.

I have a table of phone calls. Columns include the time the call started, the length of the call in seconds, and the "channel" the call came in on (an integer from 1-4). I'd like to know how often all 4 channels are occupied. How can I query this?

For example, consider the sample data:

CallTime   Length   Channel
12:00      22       1
12:05      15       2
12:10      20       3
12:15      10       4

In this scenario, all 4 channels were occupied for 7 minutes (until the first call ended). Is there a way to query this?

Thanks,

-Scott

score 0 · Answer 1 · answered Jun 26 '14 at 21:03

DECLARE @MyTable TABLE
(
    CallTime DATETIME,
    CallLength INT,
    Channel INT
)

INSERT INTO @MyTable
( CallTime, CallLength, Channel )
VALUES
( '06-26-2014 12:00:00', 22, 1 ),
( '06-26-2014 12:00:05', 15, 2 ),
( '06-26-2014 12:00:10', 20, 3 ),
( '06-26-2014 12:00:15', 10, 4 );

DECLARE @number_of_numbers INT = 100000;

;WITH
a AS (SELECT 1 AS i UNION ALL SELECT 1),
b AS (SELECT 1 AS i FROM a AS x, a AS y),
c AS (SELECT 1 AS i FROM b AS x, b AS y),
d AS (SELECT 1 AS i FROM c AS x, c AS y),
e AS (SELECT 1 AS i FROM d AS x, d AS y),
f AS (SELECT 1 AS i FROM e AS x, e AS y),
numbers AS 
(
    SELECT TOP(@number_of_numbers)
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS number
    FROM f
)


SELECT 
    d.CheckTime,
    MAX(CASE WHEN m.Channel = 1 THEN 1 ELSE 0 END) Channel1,
    MAX(CASE WHEN m.Channel = 2 THEN 2 ELSE 0 END) Channel2,
    MAX(CASE WHEN m.Channel = 3 THEN 3 ELSE 0 END) Channel3,
    MAX(CASE WHEN m.Channel = 4 THEN 4 ELSE 0 END) Channel4
FROM
(
    SELECT DATEADD(SECOND, n.number, '06/26/2014') CheckTime
    FROM numbers n
) AS d
INNER JOIN @MyTable m
    ON d.CheckTime >= m.CallTime AND d.CheckTime <= DATEADD(SECOND, m.CallLength, m.CallTime)
GROUP BY d.CheckTime

Here is an example where you create a table of numbers, and then use that to create time slots for each second of the day and then you can join against your table and using group by you can find all of the channels that were active.

Thanks for the response. I'm doing this in MySQL, so some of this doesn't parse. I'm trying to convert it, but I'm unclear on the main logic. What is this piece for?: `;WITH` `a AS (SELECT 1 AS i UNION ALL SELECT 1),` `b AS (SELECT 1 AS i FROM a AS x, a AS y),` `c AS (SELECT 1 AS i FROM b AS x, b AS y),` `d AS (SELECT 1 AS i FROM c AS x, c AS y),` `e AS (SELECT 1 AS i FROM d AS x, d AS y),` `f AS (SELECT 1 AS i FROM e AS x, e AS y),` `numbers AS ` `(` ` SELECT TOP(@number_of_numbers)` ` ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS number` ` FROM f` `)` — genevish, Jun 27 '14 at 19:14
that just creates a table of numbers, here is some number table code for mysql: http://stackoverflow.com/questions/9751318/creating-a-numbers-table-in-mysql — Kevin Cook, Jun 27 '14 at 19:20

SQL Query for a range of times

1 Answers1