I have table in SQL Server with these columns:
CREATE TABLE temp
(
T_DATE DATE,
T_TYPE VARCHAR(20),
T_AMT INT
);
Below is the sample data:
INSERT INTO TEMP VALUES('2020-10-19','M',230.23);
INSERT INTO TEMP VALUES('2020-10-19','P',1.23);
INSERT INTO TEMP VALUES('2020-10-20','M',130.23);
INSERT INTO TEMP VALUES('2020-10-20','P',2.23);
INSERT INTO TEMP VALUES('2020-10-21','M',23.23);
INSERT INTO TEMP VALUES('2020-10-21','S',10.23);
INSERT INTO TEMP VALUES('2020-10-22','M',220.23);
INSERT INTO TEMP VALUES('2020-10-22','T',0.23);
INSERT INTO TEMP VALUES('2020-10-23','M',830.23);
INSERT INTO TEMP VALUES('2020-10-23','P',30.23);
INSERT INTO TEMP VALUES('2020-10-26','M',230.23);
INSERT INTO TEMP VALUES('2020-10-26','P',10.23);
INSERT INTO TEMP VALUES('2020-10-27','M',230.23);
INSERT INTO TEMP VALUES('2020-10-27','S',13.23);
This is the output for SELECT * FROM TEMP;:
T_DATE T_TYPE T_AMT
-------------------------------
2020-10-19 M 230
2020-10-19 P 1
2020-10-20 M 130
2020-10-20 P 2
2020-10-21 M 23
2020-10-21 S 10
2020-10-22 M 220
2020-10-22 T 0
2020-10-23 M 830
2020-10-23 P 30
2020-10-26 M 230
2020-10-26 P 10
2020-10-27 M 230
2020-10-27 S 13
Here the requirement is, need to update the T_AMT column, below will be the logic.
Example:
for T_DATE = 2020-10-19 and T_TYPE = M, I need to check next working date in the same table like T_DATE = 2020-10-20 and T_TYPE = either P or S...
if next date T_TYPE is P, then T_AMT value of T_TYPE = M should be (previous date = 2020-10-19's T_TYPE = M PLUS next date = 2020-10-20 , T_AMT values).
Like this:
2020-10-19 M 230
2020-10-19 P 1
2020-10-20 M 230(PREVIOUS DATE T_TYPE VALUE (M)) + 2 (CURRENT DATE T_TYPE VALUE(P)) = 232
2020-10-20 P 2
similarly for T_TYPE values as S, but in case of S it should minus instead of plus.
If T_TYPE is neither P or S then nothing should do.
This logic should follow for all rows for T_TYPE = M for each date.....
The model above is a simplification of the real model to focus on a thread of values;
in reality the table includes multiple, distinct sets of data, with 2 additional columns T_ANO and T_CP that serve as a distinction between sets.
Although to simplify the problem a solution based only on the model above is sufficient,
I would appreciate an answer that, in addition, takes into account this separation (modifying each set independently of the others).
A full dataset with those columns is available in this db<>fiddle
If we think about which rows will change, we will deduce that every M row will be computed solely from the P rows + the first M row.
For example, M 2020-10-22 will be computed as M 2020-10-21 + P 2020-10-22; but M 2020-10-21 itself will have been computed as M 2020-10-20 + P 2020-10-21, with M 2020-10-20 itself depending on M 2020-10-19 (which is the first in line so we stop here).
Thus M 2020-10-22 = M 2020-10-19 + P 2020-10-20 + P 2020-10-21 + P 2020-10-22:
new M values will be a windowed sum over P and S values + the first M value.
-- In our new values tables, we shall not omit P and S, because they contribute to the total…
with newm as
(
select
t_date,
t_type,
start_amt + sum(case t_type when 'P' then t_amt when 'S' then - t_amt end) over (order by t_date, t_type desc) t_amt -- order by t_type desc will make P and S appear before the M they should update.
from
(
select top 1 t_date start_date, t_amt start_amt
from temp where t_type = 'M' order by t_date
) firstm
join temp on temp.t_date > start_date -- The first M does not change, and its P and S don't count.
)
update temp
set temp.t_amt = newm.t_amt
from temp join newm on newm.t_type = temp.t_type and newm.t_date = temp.t_date
where temp.t_type = 'M'; -- … but in the end update only M rows.
| T_DATE | T_TYPE | T_AMT |
|---|---|---|
| 2020-10-19 | P | 1 |
| 2020-10-19 | M | 230 |
| 2020-10-20 | P | 2 |
| 2020-10-20 | M | 232 |
| 2020-10-21 | S | 10 |
| 2020-10-21 | M | 222 |
| 2020-10-22 | T | 0 |
| 2020-10-22 | M | 222 |
| 2020-10-23 | P | 30 |
| 2020-10-23 | M | 252 |
| 2020-10-26 | P | 10 |
| 2020-10-26 | M | 262 |
| 2020-10-27 | S | 13 |
| 2020-10-27 | M | 249 |
(see it in a fiddle)
With the addition of T_ANO and T_CP to your question, we have to adapt to make the "set identifier" correctly passed through the chain.
The query then becomes (with /*>*/ and /*<*/ marking the additions, compared to the first solution):
with newm as
(
select
/*>*/temp.t_ano, temp.t_cp,/*<*/
t_date, t_type,
start_amt + sum(case t_type when 'P' then t_amt when 'S' then - t_amt end) over (/*>*/partition by temp.t_ano, temp.t_cp/*<*/ order by t_date, t_type desc) t_amt -- order by t_type desc will make P and S appear before the M they should update.
from
(
/*>*/
-- To get the top 1 per t_ano and t_cp, we could either
-- - continue with top 1 but in a correlated subquery
-- - group by t_ano and t_cp, min(t_date), and get the corresponding t_amt in a correlated subquery (possibly with top 1)
-- - distinct of a window over (partition by t_ano, t_cp order by t_date)
-- we'll choose it here:
select distinct
t_ano, t_cp,
first_value(t_date) over (partition by t_ano, t_cp order by t_date) start_date, -- = min(t_date) over (partition by without order by), but let's keep first_value to get it symetric with t_amt.
first_value(t_amt) over (partition by t_ano, t_cp order by t_date) start_amt
from temp where t_type = 'M'
/*<*/
) firstm
join temp on temp.t_date > start_date -- The first M does not change, and its P and S don't count.
/*>*/and temp.t_ano = firstm.t_ano and temp.t_cp = firstm.t_cp/*<*/
)
update temp
set temp.t_amt = newm.t_amt
from temp join newm on newm.t_type = temp.t_type and newm.t_date = temp.t_date
/*>*/and newm.t_ano = temp.t_ano and newm.t_cp = temp.t_cp/*<*/
where temp.t_type = 'M';