Extrapolate values and sum

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?

Thanks

Answer 1

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
        end
    )
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.