Stack Overflow Asked by usef juan on February 13, 2021
I have worked on this issue and looked for similar threads but could not find any solution yet.
I have tried couple ways which i will explain and give an example of the data and what needs to be achieved:
I have a table that looks like this :
Code:
drop table if exists #temp
create table #temp
(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10),
[date] date,
dateindex int,
totals money,
adjustement money
)
insert into #temp
values ('customer1', 'customer1', 'customer1', '01-01-2020', 1, 0, 100),
('customer1', 'customer1', 'customer1', '01-02-2020', 1, 0, 200),
('customer1', 'customer1', 'customer1', '01-03-2020', 1, 50, 0),
('customer1', 'customer1', 'customer1', '01-03-2020', 2, 100, 0),
('customer2', 'customer2', 'customer2', '01-04-2020', 1, 0, 150),
('customer2', 'customer2', 'customer2', '01-05-2020', 2, 0, 300),
('customer2', 'customer2', 'customer2', '01-06-2020', 1, 50, 0),
('customer2', 'customer2', 'customer2', '01-06-2020', 2, 100, 0)
We have two numeric columns: totals
and adjustments
, the rest is unique attributes for a customer and a date.
When adjustments is different from 0, that is fine and we don’t need to change those numbers.
When totals is different from 0, that is when I want to do a calculation and hopefully update the result of the Adjustments
column in the same row (when total is <> 0, adjustments is always = 0).
The calculation to replace those 0 adjustments should be : the current total value – all previous adjustments (running total)
This is what I have tried :
select
*,
case
when totals <> 0
then totals - sum(adjustement) over (partition by col1,col2,col3
order by [date] asc, dateindex asc
rows between unbounded preceding and 1 preceding)
else adjustement
end as 'Calculation'
from
#temp
Row 3 returns the correct result : 50 – 300 = -250
Now, when you have totals on the same day (row 3 and 4 ), same date but different index (index used to know which transaction came first), the window function is doing row4 = 100 -(100+200+0) =-200 when it needs to be 100 – (100+200-250+0) = 50 .
The previous -250 is not included because its not the actual data .I need to find way to update the table while doing the calculation so that -250 can be updated on the table before moving the next row.
This calculation is done for each customer and that is why I am partitioning by customer columns.
I really appreciate any hints or advice on this and what could be the best approach
Thanks !
I am not quite sure how the third index would work on a date. But for the second index (as in your example), you can do the calculation twice:
select t.* ,
(case when totals <> 0 and dateindex = 2
then totals - sum(calculation) over (partition by col1, col2, col3
order by date, dateindex
rows between unbounded preceding and 1 preceding
)
else calculation
end) as Calculation_2
from (select t.*,
(case when totals <> 0 and dateindex = 1
then totals - sum(adjustement) over (partition by col1,col2,col3
order by [date] asc,dateindex asc rows between unbounded preceding and 1 preceding)
when dateindex = 1 then adjustement
end) as Calculation
from temp t
) t;
I suspect the third calculation just requires another level of subquery.
Here is a db<>fiddle.
Answered by Gordon Linoff on February 13, 2021
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP