Table modification/recalculation with SQL (reduce with look ahead and look back)

user_15296356 0
Viewed 49

it was quite difficult to name the question in a proper way, as I have a following case. Consider table:

A       B     C
1-Jan   0.1   1
2-Jan   0.1   0
3-Jan   0.5   0
4-Jan   0.3   1

Which says there is some amount (B) of typical income for specific dates (A). And there is a flag for date being weekend (C). As 2-Jan and 3-Jan are weekends, I can only expect respective amounts on 4-Jan and have a need to convert table above to the following representation (desired result):

A1      B1    C1
1-Jan   0.1 
4-Jan   0.1   02-Jan
4-Jan   0.5   03-Jan
4-Jan   0.3

Which obviously says I may expect income from 2&3 Jan on 4-Jan (That's why I have column C1). Overall logic is that each weekend estimate adds to the next working day (look ahead), unless last day of the month is a weekend, then I have to add it's value to the last working day (look back).

Solving this with some sort of loop is possible and I could manage it. But I'd be glad to know if there is more efficient solution (something, I can put in IF within SELECT), as there is big amount of data (meaning, there are thousand of values in some column D per day). So looping may be quite expensive solution. Also, while having C1 for reference and testing would be nice, only A1 and B1 are absolutely necessary.

I did my best to find similar topic here, but as I don't know how to properly name this solution (look ahead mainly refers to Regex), I was unable to do it. Thanks in advance.

tsql sql-server sql
2 Answers

You can run a select as:

select min(case when c = 1 then a end) over (order by a desc) as a1,
       b as b1,
       (case when c = 0 then a end) as c1
from t;

Here is a db<>fiddle.

user_11440350

Thanks to the brilliant guidance from Gordon Linoff, my teammate managed to came with final solution, allowing both, move weekend days forward to the next working day and also move last non-working day of the month back, to the last working day.

    select  * from t
select case when t1.a1 is null then t2.a1 else t1.a1 end as d, sum (t1.b1 )as e from (
select t.*,
       min(case when c = 1 then a end) over (order by a desc) as a1,
       b as b1,
       (case when c = 0 then a end) as c1
from t
) t1 join (
select t.*,
       max(case when c = 1 then a end) over (order by a asc) as a1,
       b as b1,
       (case when c = 0 then a end) as c1
from t)
t2 on t1.a=t2.a
group by (case when t1.a1 is null then t2.a1 else t1.a1 end)
order by  (case when t1.a1 is null then t2.a1 else t1.a1 end)

As a result, with the input:

a           b   c
2021-01-01  0.1 1
2021-01-02  0.1 0
2021-01-03  0.5 0
2021-01-04  0.3 1
2021-01-05  0.1 1
2021-01-06  0.1 0
2021-01-07  0.5 1
2021-01-08  0.3 1
2021-01-09  0.1 0
2021-01-10  0.5 0

we get:

d           e
2021-01-01  0.1
2021-01-04  0.9
2021-01-05  0.1
2021-01-07  0.6
2021-01-08  0.9
user_152963560
Related