2

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

0

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? – Mattia Nocerino Sep 26 '18 at 7:33
  • 1
    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). – Andriy M Sep 27 '18 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. – Mattia Nocerino Sep 27 '18 at 13:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.