Count and Sum consecutive records with minimum value

Question

I have a table that shows payments for clients summarized by months. It looks like this:

Client | Month1 | Month2 | Month3 | Month4 | Month5 | Month6  
------------------------------------------------------------
x1     | 100    | 200    | NULL   | 100    | 100    | 100  
x2     | 100    | 200    | NULL   | NULL   | 100    | 100  
x3     | NULL   | NULL   | 200    | 100    | 100    | 100  
x4     | NULL   | 200    | 300    | 100    | 100    | NULL  
x5     | 100    | 200    | 200    | 100    | 100    | 100  
x6     | NULL   | NULL   | NULL   | 100    | 100    | 100  
x7     | NULL   | 200    | 300    | 100    | 100    | 100  
x8     | NULL   | 200    | NULL   | 100    | 100    | NULL

I need to summarize the values of consecutive payments where the number of consecutive is >=3 and the interval is calculated backwards from the last month.

So all who have Months 6, 5 and 4 should be summarized as well as the ones which consecutive payments extend more into the past. With this in mind, and from the above example, clients 1, 3, 5, 6 and 7 should be in and for them, sum's should be:

X1 - Last 3 months  
X3 - Last 4 months  
X5 - Last 6 months  
X6 - Last 3 Months  
X7 - Last 5 months

So all months from the last one into the past, where consecutive is >=3, until the first break (month with no payment).

David, thanks for edit, i didnt know how to format the table. — Peraklo, Sep 25 '15 at 08:18
My original source table is just a big table with amounts and dates. I have summarized and grouped the amounts by months. I can filter out all the clients which do not have payments for the last 3 months, but other than that i dont even know where to start. Maybe to count the number of consecutive months, and then use that number somehow. — Peraklo, Sep 25 '15 at 08:22

A ツ · Answer 1 · 2015-09-25T11:31:30.697

2

maybe there is some fancy way to do it, but i don't see it at the moment.

i'd go for outer apply since you want to use the calculated column twice.

the case ends whenever it hits null.

select *
from data
cross apply (
   select cnt = case when month6 is null then 0
                     when month5 is null then 1
                     when month4 is null then 2
                     when month3 is null then 3
                     when month2 is null then 4
                     when month1 is null then 5
                end
)
where cnt>=3

edited Sep 25 '15 at 11:31

answered Sep 25 '15 at 09:42

A ツ

1,267
2
9
14

I like the logic, but the `apply` is totally unnecessary. – Gordon Linoff Sep 25 '15 at 10:51
@GordonLinoff `cross apply` and cte get the same execution plan. – A ツ Sep 25 '15 at 11:34
This did it for the counting of the consecutive payments. I used the @daniel lower script to sum it all up. – Peraklo Sep 25 '15 at 12:06

daniel · Answer 2 · 2015-09-25T10:10:52.583

Try below script. It is a bit long, you can rewrite it better.

select p1.Client, sum(p1.Amount) Amount
from
(
    select Client, MonthName, Amount
    from
    (
        select
            Client,
            isnull(Month1, 0) Month1,
            isnull(Month2, 0) Month2,
            isnull(Month3, 0) Month3,
            isnull(Month4, 0) Month4,
            isnull(Month5, 0) Month5,
            isnull(Month6, 0) Month6
        from Payment
    ) pm
    unpivot
    (
        Amount
        for MonthName in (Month1, Month2, Month3, Month4, Month5, Month6)
    ) unpvt
) p1
left join
(
    -- get last month with null value
    select Client, max(MonthName) MonthName
    from
    (
        select
            Client,
            isnull(Month1, 0) Month1,
            isnull(Month2, 0) Month2,
            isnull(Month3, 0) Month3,
            isnull(Month4, 0) Month4,
            isnull(Month5, 0) Month5,
            isnull(Month6, 0) Month6
        from Payment
    ) pm
    unpivot
    (
        Amount
        for MonthName in (Month1, Month2, Month3, Month4, Month5, Month6)
    ) unpvt
    where unpvt.Amount = 0
    group by unpvt.Client

) p2 on p2.Client = p1.Client and p1.MonthName <= p2.MonthName
where p2.Client is null
group by p1.Client
having count(p1.Client) >= 3

The above script can control consecutive month count by the having clause, it is more general. Below script works more specific.

select Client, sumpayment.Amount
from
(
    select
    Client,
    case
    when Month6 is null or Month5 is null or Month4 is null then 0
    when Month3 is null then Month6 + Month5 + Month4
    when Month2 is null then Month6 + Month5 + Month4 + Month3
    when Month1 is null then Month6 + Month5 + Month4 + Month3 + Month2
    else Month6 + Month5 + Month4 + Month3 + Month2 + Month1
    end as Amount
    from Payment
) sumpayment
where sumpayment.Amount > 0

Thanks, this solved my current problem. I will have to take a look into the upper script to see how to generalize the query since it will have to be used more in the future, and the count of consecutive months will not always be 3 and the month for which i am checking the data will not always be 6. In the event where i have to do the calculation for 24 months, this CASE WHEN would get mighty big. — Peraklo, Sep 25 '15 at 12:07
Actually, you can first understand the idea of the first script see can apply the idea to you raw data. — daniel, Sep 25 '15 at 12:19
One tips is you can have temp table or variable table to store intermediate data if possible, it can may shorten the script. — daniel, Sep 25 '15 at 15:34

score 1 · Accepted Answer · edited Sep 25 '15 at 11:20

1

A ツ's answer is very, very good, but the apply is totally unnecessary. Just use a subquery or CTE:

select d.*
from (select d.*,
             (case when month6 is null then 0
                   when month5 is null then 1
                   when month4 is null then 2
                   when month3 is null then 3
                   when month2 is null then 4
                   when month1 is null then 5
              end) as cnt
      from data d
     ) d
where cnt >= 3;

edited Sep 25 '15 at 11:20

Ralf de Kleine

11,464
5
45
87

answered Sep 25 '15 at 10:53

Gordon Linoff

1,242,037
58
646
786

Thanks for the suggestion. – Peraklo Sep 25 '15 at 12:10

Count and Sum consecutive records with minimum value

3 Answers3