question

swfsa avatar image
swfsa asked

Conditional Count Statement

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?

t-sqlsql-server-2000
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

How are you defining an open call? Is it that the reeiveddate value falls within a month - meaning the call was opened that month and the closeddate is immaterial? Or is it that the receiveddate falls within a month and the call has an empty closeddate? Assuming that it's counting calls that have a NULL closeddate, and the call had a receiveddate in an earlier month, would that call be counted once per month, or only in the month that it was opened? If you could post your table structure and some sample data that would be helpful.
0 Likes 0 ·
Matt Whitfield avatar image
Matt Whitfield answered

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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JoeJF avatar image
JoeJF answered

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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom Staab avatar image
Tom Staab answered

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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sp_lock avatar image
sp_lock answered

How you do convert this to use it minute by minute?

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.