x

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?

more ▼

asked Oct 19, 2009 at 06:34 PM in Default

swfsa gravatar image

swfsa
28 2 2 2

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.
Oct 19, 2009 at 07:21 PM Jay Bonk
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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
more ▼

answered Oct 19, 2009 at 07:10 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Oct 19, 2009 at 07:50 PM

JoeJF gravatar image

JoeJF
56 1

I think the fact he tagged it as SQL Server 2000 means that this solution won't work as it uses a CTE.
Oct 20, 2009 at 06:06 AM Melvyn Harbour 1 ♦♦
(comments are locked)
10|1200 characters needed characters left

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 
more ▼

answered Oct 19, 2009 at 08:38 PM

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Oct 20, 2009 at 04:43 AM

sp_lock gravatar image

sp_lock
9.3k 25 28 31

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x990
x476

asked: Oct 19, 2009 at 06:34 PM

Seen: 2388 times

Last Updated: Oct 20, 2009 at 06:07 AM