Query: Count of Membership between dates (2 columns)

Question

Query: Count of Membership between dates (2 columns)

esto4273

Old Hand

Points: 311
More actions
April 16, 2010 at 9:15 am

#386720

Does anyone have experience counting persons who are considered active for a duration between two dates?
The report will need to be run on a variable start/end parameter, so the report start date will often be between the membership dates.
To clarify, I cannot just count the number of when the member starts or expires, but the middle dates as well.
I know it would be possible to build rows for the reporting months between Date A and Date B, but I am looking for an alternative to save rowcount.
Any Ideas? :w00t:
PS (clarification below as suggested other poster)
Looking for if a person is active anytime within the report range, but not a simple grand total of people. It would be grouped by month, day or year dynamically, so they would be counted once for each month within the report range while they are still active.

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

For the best help, please read the first article I reference below in my signature block. Follow the instructions regarding what and how you should post table definitions, sample data. Also, to extend that, be sure to post the expected results based on the sample data as well.

Do this and many people will jump at the opportunity to help you, and you will get tested code in return.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

You need to explain the problem better.

Are you looking for someone who is active any time in the report range, someone who is active for the entire time of the report range, or something else?

esto4273 Old Hand Points: 311 More actions · Answer 3

Looking for if a person is active anytime within the report range, but not a simple grand total of people. It would be grouped by month, day or year dynamically, so they would be counted once for each month within the report range while they are still active.

Hope that helps clarify.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Actually, not really. Please take the time to read the article I refered you to earlier. You need to provide us with more information if you really want good help with this problem.

esto4273 Old Hand Points: 311 More actions · Answer 5

Sorry Lynn. I know you would like to see tried code, but I'm not aware of what is possible. guess I was wondering if someone ha da code sample to consider.

I'll see in a bit if I can't put a sample together.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

esto4273 (4/16/2010)
Sorry Lynn. I know you would like to see tried code, but I'm not aware of what is possible. guess I was wondering if someone ha da code sample to consider.
I'll see in a bit if I can't put a sample together.

Read the article, it will walk you through most of what you need to do. Just be sure to provide expected results as well so we have something to check against.