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

  • 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;

    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
    ;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(
    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


    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

    Monday, July 22, 2019 10:06 AM
  • I hope following script help you

    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
    ('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