Extrapolate values and sum

Posted on

Question :

I have data in the form:

|Date      |Overdrawn|
|20/09/2018|1        |
|21/09/2018|1        |
|24/09/2018|0        |
|25/09/2018|1        |
|26/09/2018|1        |

I need to sum the number of overdrawn days with the following rule:

Missing dates (weekends) should be treated the same as the last date available.

i.e. If overdrawn on Friday, Saturday and Sunday should be counted as 2 days overdrawn. If Friday is not overdrawn, Saturday and Sunday should also be treated as not overdrawn.

In the example above date 20, 21, 22, 23, 25, 26 should be treated as overdrawn and the sum should return 6.

Can someone explain how to extrapolate the overdrawn value for the missing dates before summing?


Answer :

Try this one:

create table #test (
    date date,
    overdrawn tinyint

insert into #test values
    ('2018-09-20', 1),
    ('2018-09-21', 1),
    ('2018-09-24', 0),
    ('2018-09-25', 1),
    ('2018-09-26', 1)

select sum(
        case datepart(dw, date)
            when 6 then overdrawn * 3 --Friday
            else overdrawn
from #test

This solution is based off the fact that the overdrawn field can either be 0 or 1 only. You’ll have to include additional conditions if that’s not the case.

Leave a Reply

Your email address will not be published. Required fields are marked *