sql - count active records grouping by month

Question

0.00/5 (No votes)

See more:

Using TSQL, how would I get a count of active records, grouped by month where I have a OpenOn date and a ClosedOn date? (ClosedOn of Null means the record is still active).

E.G.
If I have:

OpenOn        ClosedOn
*********     **********
2011-01-02    2011-01-30
2011-01-04    2011-03-15
2011-01-05
2011-02-01    2011-03-10
2011-03-02    2011-03-20

Then I should get

  Month       Total
  ******      *****
  2011-01-01  3
  2011-02-01  3
  2011-03-01  4
 ******************

I.E.
Records 1,2 and 3 were active during Jan,
Records 2,3 and 4 were active during Feb, and
Records 2,3,4 and 5 were active during March.

Posted 31-Jul-11 19:45pm

RCoate

Updated 31-Jul-11 20:08pm

v2

Add a Solution

2 solutions

Solution 1

Have a look here[^].

Posted 31-Jul-11 19:54pm

Abhinav S

Comments

RCoate 1-Aug-11 1:57am

I know how to group by dates. It is counting active records that span more than one month that I am having issues with.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RCoate · Accepted Answer · 2011-08-01T16:32:00

Problem solved.

I used a temporary table to identify the month groups I was interested in then an inner join with a between directive to identify the correct data.

I.E.

SQL

DECLARE @Calendar TABLE(theMonth DATETIME);
INSERT INTO @Calendar  ( theMonth )
SELECT  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
INSERT INTO @Calendar ( theMonth )
SELECT  DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, -1, GETDATE())), 0)
INSERT INTO @Calendar ( theMonth )
SELECT  DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, -2, GETDATE())), 0)
INSERT INTO @Calendar ( theMonth )
SELECT  DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, -3, GETDATE())), 0)


SELECT	calendar.theMonth,
	COUNT(1) AS total
FROM    SourceTable AS t
	INNER JOIN @Calendar AS calendar 
        ON (calendar.theMonth 
              BETWEEN DATEADD(MONTH,DATEDIFF(MONTH,0, t.OpenOn),0) 
  	      AND CASE
		    WHEN t.ClosedOn IS NULL THEN GETDATE()
		    ELSE t.ClosedOn
	         END)
GROUP BY calendar.theMonth,
ORDER BY calendar.theMonth DESC