# Counting Records Backwards From Current Month/Yr To Oldest Including Only Each Month and its Previous Month(s)

• ### Question

• 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..this is going backwards from current month/yr to whatever month/yr is oldest

So, if i have data detail as:

```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!

Friday, July 19, 2019 4:01 PM

### All replies

• ```Declare @Sample Table(Member varchar(10), Period varchar(10));
Insert @Sample(Member, Period) Values
('A',                 'Jan'),
('A',                 'Jan'),
('A',                 'Jan'),
('A',                 'Mar'),
('A',                 'Mar'),
('A',                 'Apr');

;With Months As
(Select 1 As Nbr, 'Jan' As Name
Union All Select 2, 'Feb'
Union All Select 3, 'Mar'
Union All Select 4, 'Apr'
Union All Select 5, 'May'
-- and the rest of the months
Union All Select 12, 'Dec'),

SampleGroup As
(Select Member, Period, Count(*) As Cnt From @Sample Group By Member, Period)

Select s.Member, s.Period,
Sum(Cnt) Over(Partition By s.Member Order By m.Nbr Rows Between Unbounded Preceding And Current Row) As Count
From SampleGroup s
Inner Join Months m On s.Period = m.Name
Order By s.Member, m.Nbr;
```

Tom
Friday, July 19, 2019 5:02 PM
• ```;with mycte as (
select Member, Period, Cast(Period+' 1 2019' as date) dt
, Row_Number() Over(Partition by Member,Period Order by Cast(Period+' 1 2019' as date) ) rn
, Count(*) Over(Order by Cast(Period+' 1 2019' as date)) as cnt from @Sample
)
Select Member,Period,cnt from mycte
Where rn=1
Order by dt
```

Friday, July 19, 2019 6:03 PM
• Thanks Tom; going to have to digest this as not familiar with the line:

Over(Partition By s.Member Order By m.Nbr Rows Between Unbounded Preceding And Current Row

Sounds like its saying to sum the count of what's preceding the current row, but not familiar with that keyword..i think I have some of it, but i'll have to play with it..

Thanks for sending, much appreciated!

Friday, July 19, 2019 9:19 PM
• Hi Jesus,

I add some sample data for test.

```IF OBJECT_ID('test') IS NOT NULL drop table test
create table test(
member varchar(5),
period varchar(5))

insert into test values
('A','Jan'),
('A','Jan'),
('A','Jan'),
('A','Mar'),
('A','Mar'),
('A','Apr'),
('B','Apr'),
('B','Apr'),
('B','Apr'),
('B','Feb')

;with cte as (
select member,period,Month(cast(period +'1 2019' as datetime)) as mon, count (Month(cast(period +'1 2019' as datetime)))[total]
from test
group by period,member)

select a.member,a.period,sum(b.total)
from cte a
inner join cte b
on a.member=b.member and a.[mon] >= b.[mon]
group by a.member,a.period
order by a.member

/*
member period
------ ------ -----------
A      Apr    6
A      Jan    3
A      Mar    5
B      Apr    4
B      Feb    1
*/
```

Sabrina

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Monday, July 22, 2019 10:06 AM

```select t.Member,t.Period,max(t.rm) from (
select *, row_number() over (partition by member order by MONTH ('1' + Period + '00')) as rm
from (
select * from ( values
('A','Jan'),
('A','Jan'),
('A','Jan'),
('A','Mar'),
('A','Mar'),
('A','Apr'),
('B','Apr'),
('B','Apr'),
('B','Apr'),
('B','Feb') ) as dt (Member,period)) as f

) as t group by member,period order by Member,MONTH ('1' + Period + '00')```

Monday, July 22, 2019 1:28 PM