May 6, 2009 at 5:17 am
Hi
I was wondering if some can help with a query?
We have a telephone calling system and I am wanting to show the conncurrent/active calls.
CallDetails table has the following columns:
Call_ID int
CallStart datetime
CallEnd datetime
I am wanting to show "by minute" for each day the number of concurrent (active calls) between a specific date
Example data
Call_ID CallStart CallEnd
1 01/01/2009 09:00:00 01/01/2009 09:01:30
2 01/01/2009 09:01:00 01/01/2009 09:03:30
3 01/01/2009 09:02:00 01/01/2009 09:05:30
the data I want to show should look like this..
| Date_Minute | Concurrent_Calls |
|---|---|
| 01/01/2009 09:00:00 | 1 |
| 01/01/2009 09:01:00 | 2 (as ID 1 ended @ 09:01:30 and ID 2 started @ 09:03:30) |
| 01/01/2009 09:02:00 | 2 (as ID 2 ended @ 09:03:30 and ID 2 started @ 09:02:00) |
| 01/01/2009 09:03:00 | 2 (as ID 2 ended @ 09:03:30 and ID 2 started @ 09:02:00) |
| 01/01/2009 09:04:00 | 1 ( as ID 3 ended @ 09:05:30) |
| 01/01/2009 09:05:00 | 1 ( as ID 3 ended @ 09:05:30) |
Any help would be appreciated...
Rgds
JL
May 6, 2009 at 5:32 am
Your requirement can be solved using a Numbers or Tally table.
Refer to the below article on Numbers table and the final example has pointers for your requirement.
May 6, 2009 at 6:26 am
Thank you for the quick response.
Is not the list of dates i am having trouble with, it is the Conncurrent/Active calls.
Cheers
JL
May 6, 2009 at 6:58 am
Hi
Try this. Use a Tally table to build up your time table and CROSS APPLY to get the current active calls.
-- Some sample calls
DECLARE @calls TABLE (Id INT, StartDate DATETIME, EndDate DATETIME)
INSERT INTO @calls
SELECT 1, '01/01/2009 09:00:00', '01/01/2009 09:01:30'
UNION ALL SELECT 2, '01/01/2009 09:01:00', '01/01/2009 09:03:30'
UNION ALL SELECT 3, '01/01/2009 09:02:00', '01/01/2009 09:05:30'
--==============
-- Build a time table
DECLARE @time TABLE (Id INT, CurrentTime DATETIME, CurrentCalls INT)
DECLARE @from DATETIME
DECLARE @to DATETIME
-- Just add time values from 09:00 to 10:00
SELECT
@from = '2009-01-01 09:00:00',
@to = '2009-01-01 10:00:00'
INSERT INTO @time
SELECT
N,
DATEADD(MINUTE, N - 1, @from),
NULL
FROM Tally
WHERE DATEADD(MINUTE, N - 1, @from) <= @to
ORDER BY N
-- Update time table to set the current active calls
UPDATE t SET
CurrentCalls = c.CurrentCalls
FROM @time t
CROSS APPLY
(
SELECT COUNT(*) CurrentCalls
FROM @calls
WHERE t.CurrentTime BETWEEN StartDate AND EndDate
) c
--===============
-- Result
SELECT * FROM @time
Greets
Flo
May 6, 2009 at 7:09 am
PERFECT.....
Works an absolute treat!!!
JL
May 6, 2009 at 7:13 am
Glad that I could help! 🙂
May 6, 2009 at 8:59 am
Flo
One last thing 😉
What is need if I want to group the CurrentTime and Current calls by Department?
i.e.
Sales 2009-01-02 07:47:00.000 23
Sales 2009-01-02 07:48:00.000 21
Sales 2009-01-02 07:49:00.000 2
Acc 2009-01-02 07:47:00.000 2
Acc 2009-01-02 07:48:00.000 3
Acc 2009-01-02 07:49:00.000 7
Cheers
JL
May 6, 2009 at 12:04 pm
Hi JL
Try this:
-- Some sample calls
DECLARE @calls TABLE (Id INT, StartDate DATETIME, EndDate DATETIME, Department VARCHAR(10))
INSERT INTO @calls
SELECT 1, '01/01/2009 09:00:00', '01/01/2009 09:01:30', 'Sales'
UNION ALL SELECT 2, '01/01/2009 09:01:00', '01/01/2009 09:03:30', 'Acc'
UNION ALL SELECT 3, '01/01/2009 09:02:00', '01/01/2009 09:05:30', 'Sales'
--==============
-- Build a time table
DECLARE @time TABLE (Id INT, CurrentTime DATETIME, AccCalls INT, SalesCalls INT)
DECLARE @from DATETIME
DECLARE @to DATETIME
-- Just add time values from 09:00 to 10:00
SELECT
@from = '2009-01-01 09:00:00',
@to = '2009-01-01 10:00:00'
INSERT INTO @time (Id, CurrentTime)
SELECT
N,
DATEADD(MINUTE, N - 1, @from)
FROM dbo.Tally
WHERE DATEADD(MINUTE, N - 1, @from) <= @to
ORDER BY N
-- Update time table to set the current active calls
UPDATE t SET
AccCalls = ISNULL(c.AccCalls, 0),
SalesCalls = ISNULL(c.SalesCalls, 0)
FROM @time t
CROSS APPLY
(
SELECT
SUM(CASE WHEN Department = 'Acc' THEN 1 ELSE 0 END) AccCalls,
SUM(CASE WHEN Department = 'Sales' THEN 1 ELSE 0 END) SalesCalls
FROM @calls
WHERE t.CurrentTime BETWEEN StartDate AND EndDate
) c
--===============
-- Result
SELECT * FROM @time
Greets
Flo
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply