Count records that where active each month

Question

Post reply

Count records that where active each month

rod-935188

SSC Enthusiast

Points: 185
More actions
August 1, 2011 at 4:54 pm

#241559

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.
--EDIT--
Problem solved - Used a Tempory calendar table to identify required months and an inner join with a Between directive.
Thanks for all sujestions and help.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

I would use a calendar table for this kind of query.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Here's mine if it helps faster (canadian holidays, but that's easy to fix).

http://www.sqlservercentral.com/Forums/Attachment8839.aspx

rod-935188 SSC Enthusiast Points: 185 More actions · Answer 3

Thanks, though I will probably use a tempory table for this.

The calenar code may come in handy for another project and I appreciate the support.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

rod-935188 (8/1/2011)
Thanks, though I will probably use a tempory table for this.
The calenar code may come in handy for another project and I appreciate the support.

The calendar table IS the "temp" table. No need to build1 from scratch!

rod-935188 SSC Enthusiast Points: 185 More actions · Answer 5

Sorry. I did not look closely enough at your sql.

For my purposes, the temp table is much simpler.

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 )

SELECTDATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, -2, GETDATE())), 0)

and repeat the inserts for as many months as needed.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

WHY??????????????

Load why script or any other you wish to use and see what it builds. All you need is to do Select Whatever FROM dbo.Calendar WHERE dt BETWEEN @Start AND @End.

Done. The end. Nothing else to do with that code, ever.