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 7 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply