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

avatar image

swfsa
28 2 2 4

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

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(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

avatar image

JoeJF
56 2 2

(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

avatar image

Tom Staab ♦
14.5k 7 14 19

(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

avatar image

sp_lock
10.5k 27 37 37

(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.

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:

x1069
x502

asked: Oct 19, 2009 at 06:34 PM

Seen: 2610 times

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

Copyright 2016 Redgate Software. Privacy Policy