Counting Records Backwards From Latest Month Including All Previous Months

#1

Not sure i even titled this correct as unsure of the articulation of the problem in a small statement, but here goes!

So, I've got members and month periods and each member will show up as being associated with those various month periods. I want to count from how many times each member is in a particular month along with the total amount of times they showed up in previous months..

So, if i have data detail

Member Period
A Jan
A Jan
A Jan
A Mar
A Mar
A Apr

And each month previous to the last month will include all months prior to it, so in this example, Apr has Apr, Mar and Jan; Mar will have Mar and Jan and Jan only Jan; so the results i get will look like:

Member Period Count
A Jan 3
A Mar 5
A Apr 6

Thanks for any assistance!

#2

Hi,

Here you go:

SUM(cnt) OVER(ORDER BY UserName ROWS UNBOUNDED PRECEDING) AS RunningTotal

Sample as below:-

select , SUM(cnt) OVER(ORDER BY UserName ROWS UNBOUNDED PRECEDING) AS RunningTotal
from (
select UserName,Mth=MONTH(DateJoin),Yr=Year(DateJoin),cnt=count() from (
select UserName='A',DateJoin='2019-01-15' union all
select 'A','2019-01-15' union all
select 'A','2019-01-01' union all
select 'A','2019-03-05' union all
select 'A','2019-03-15' union all
select 'A','2019-04-11'
) A
group by UserName,MONTH(DateJoin),Year(DateJoin)
)B

Thanks,

Regards,
Micheale