-
SQL Query Q: Counting every month going back a specific date
Hi All,
I have a query where I need to count ID's monthly going back August 1st of each month for 12 months (7/31 will be the last counting month for that year). Every August 1st, the counter is reset. This is essentially a running count where start date is 8/1 and end date is 7/31 and every month I aggregate the count of ID's going back till August 1st.
I would really appreciate if anyone has any pointers to writing a query to accomodate this.
Thank you.
Jbj
-
Explanation
Can you explain in detail what is your sample test data and expected output rather than saying in words.
-
Sample data looks like:
User ID Date
---- -- -------
A 1 4/20/08
B 2 8/18/08
B 3 4/7/09
B 4 4/1/09
C 5 11/5/08
D 6 5/17/08
D 7 4/4/09
D 8 7/25/08
D 9 12/31/08
E 10 3/15/09
F 11 9/25/08
F 12 9/01/08
The date parameter for counting the ID's is 8/1 through 7/31 every year. The ID's will be counted every 15th of the month. Iplan to set up a job to run the query every 15th of the month to accomodate that.
So for this year (August 01 2008 - through today), I need to display the following: (if I ran the report today)
User Count
---- ------
B 3
C 1
D 2
E 1
F 2
User 'A' does not show up on this report because it was before 8/31 I only count numbers from 8/31 through 7/31 every year.
Does this help? Much appreciated and look forward to your reply.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|