Extrapolate values and sum

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

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.

• @Tom did you try something? Any reason for the downvote? Sep 26, 2018 at 7:33
• This solution is also assuming that Saturday and Sunday are always missing and the only weekdays missing. In reality, though, I suspect you might, on the one hand, have an odd Saturday or Sunday present in the table and, on the other hand, have a different weekday missing (for holidays with a fixed date). Sep 27, 2018 at 13:15
• You are right, but that's what I understood from the question. The OP never said that weekends could not be missing nor that other weekdays might be. That's why (i think) the overdrawn field is 0. With that said, i think that we need some clarifications from the OP. Sep 27, 2018 at 13:51