SQL Query Q: Counting every month going back a specific date

[Joshi]

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

[srikanth.mss]

Can you explain in detail what is your sample test data and expected output rather than saying in words.

[Joshi]

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.