Conditional Count Statement

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?