I have CallLog table that has receiveddate column and closeddate column. I need to figure out total # of calls that were open each month. Some calls are open for multiple months. I am not a fulltime SQL Programmer so I am having trouble comming up with the logic to retrieve this info. Can anyone guide me in the right direction?
Please bear in mind this is really not optimised at all, and assumes you have a table called tblCalls with columns StartMonth and EndMonth.
Treat it as a guide only...
DECLARE @StartDate [datetime]
DECLARE @EndDate [datetime]
DECLARE @Months TABLE (CallsMonth [datetime] PRIMARY KEY CLUSTERED, CountOfCalls [int])
SET @StartDate = '20080101'
SET @EndDate = '20101010'
INSERT INTO @Months ([CallsMonth])
SELECT DATEADD(m, [number], @StartDate) FROM [master].dbo.[spt_values] WHERE [type] = 'p'
AND DATEADD(m, [number], @StartDate) < @EndDate
UPDATE @Months
SET CountOfCalls = [aggregatedCalls].[CallCount]
FROM @Months mOuter INNER JOIN
(SELECT [m].[CallsMonth], COUNT(*) AS CallCount
FROM @Months m INNER JOIN tblCalls st
ON [m].[CallsMonth] BETWEEN st.StartMonth AND st.EndMonth
GROUP BY [m].[CallsMonth]) aggregatedCalls
ON [aggregatedCalls].[CallsMonth] = [mOuter].[CallsMonth]
SELECT * FROM @Months
I am using a temporary table, #CallLog, in my example with the columns you specified. I am using a recursive CTE to generate a 'months' table with the beginning of the month and the beginning of the next month. I then do a CROSS JOIN between the months and the calls (joining every call to every month - to look for open calls in that month). The conditions for an open call are 1) the call was received before or during the given month and 2) the call was closed after the given month. If those conditions are wrong the query would need to be modified accordingly.
The variables @startMonth, @endMonth would probably become parameters to a stored procedure. The results show the month (as the first day of the month) and the number of open calls at the end of that month.
DECLARE @startMonth DATETIME;
DECLARE @endMonth DATETIME;
SET @startMonth = '01/01/2009';
SET @endMonth = '12/01/2009';
CREATE TABLE #CallLog( receivedDate DATETIME, closedDate DATETIME );
INSERT INTO #CallLog VALUES ('01/01/2009', '01/05/2009');
INSERT INTO #CallLog VALUES ('01/01/2009', '02/05/2009');
INSERT INTO #CallLog VALUES ('01/01/2009', '03/05/2009');
INSERT INTO #CallLog VALUES ('01/01/2009', '04/05/2009');
INSERT INTO #CallLog VALUES ('02/01/2009', '02/05/2009');
WITH months AS (
SELECT @startMonth beginMonth, DATEADD(MONTH,1,@startMonth) nextMonth
UNION ALL
SELECT DATEADD(MONTH, 1, beginMonth), DATEADD(MONTH, 2, beginMonth) nextMonth
FROM months
WHERE nextMonth <= @endMonth
)
SELECT months.beginMonth [month], COUNT(*) openCalls
FROM months
CROSS JOIN #CallLog
WHERE receivedDate <= beginMonth
AND closedDate >= nextMonth
GROUP BY months.beginMonth;
DROP TABLE #CallLog;
month openCalls
----------------------- -----------
2009-01-01 00:00:00.000 3
2009-02-01 00:00:00.000 2
2009-03-01 00:00:00.000 1
This is written using SQL Server 2005/2008 CTEs for the example, but they are not necessary for the solution.
WITH CallLog (CallLogId, ReceivedDate, ClosedDate)
AS
(
SELECT 1, CAST('01/01/2009' AS datetime), CAST('01/05/2009' AS datetime)
UNION
SELECT 2, CAST('01/10/2009' AS datetime), CAST('02/01/2009' AS datetime)
)
, Calendar (CalendarOrder, MonthLabel, MonthBegin, MonthEnd)
AS
(
SELECT 1, 'December, 2008', CAST('12/01/2008' AS datetime), CAST('12/31/2008' AS datetime)
UNION
SELECT 2, 'January, 2009', CAST('01/01/2009' AS datetime), CAST('01/31/2009' AS datetime)
UNION
SELECT 3, 'February, 2009', CAST('02/01/2009' AS datetime), CAST('02/28/2009' AS datetime)
UNION
SELECT 4, 'March, 2009', CAST('03/01/2009' AS datetime), CAST('03/31/2009' AS datetime)
)
SELECT Calendar.MonthLabel, COUNT(DISTINCT CallLog.CallLogId) AS OpenCallsCount
FROM Calendar
LEFT JOIN CallLog
ON CallLog.ClosedDate >= Calendar.MonthBegin AND CallLog.ReceivedDate <= Calendar.MonthEnd
GROUP BY Calendar.CalendarOrder, Calendar.MonthLabel
ORDER BY Calendar.CalendarOrder
.
To make the SELECT statement work, you just need 2 things:
1) Create a calendar table like this:
CREATE TABLE Calendar
(
CalendarOrder int,
MonthLabel varchar(30),
MonthBegin datetime,
MonthEnd datetime
)
INSERT INTO Calendar
(CalendarOrder, MonthLabel, MonthBegin, MonthEnd)
SELECT 1, 'December, 2008', CAST('12/01/2008' AS datetime), CAST('12/31/2008' AS datetime)
UNION
SELECT 2, 'January, 2009', CAST('01/01/2009' AS datetime), CAST('01/31/2009' AS datetime)
UNION
SELECT 3, 'February, 2009', CAST('02/01/2009' AS datetime), CAST('02/28/2009' AS datetime)
UNION
SELECT 4, 'March, 2009', CAST('03/01/2009' AS datetime), CAST('03/31/2009' AS datetime)
2) Use a unique identifier in your CallLog table. I used CallLogId in the example.
As long as you have those 2 things, then you can use this simple statement to get what you need:
SELECT Calendar.MonthLabel, COUNT(DISTINCT CallLog.CallLogId) AS OpenCallsCount
FROM Calendar
LEFT JOIN CallLog
ON CallLog.ClosedDate >= Calendar.MonthBegin AND CallLog.ReceivedDate <= Calendar.MonthEnd
GROUP BY Calendar.CalendarOrder, Calendar.MonthLabel
ORDER BY Calendar.CalendarOrder
.
Example Results:
December, 2008 0
January, 2009 2
February, 2009 1
March, 2009 0
How you do convert this to use it minute by minute?
