T-SQL about self updating of a table

mytabi 0
Viewed 67

I have this table1:

date typeName Typecode Values1 Values2 Values3
2022-09-01 A 01 null 5 null
2022-09-02 A 01 null 10 null
2022-09-01 B 02 null 30 null
2022-09-02 B 02 null 10 null

And the table2:

date typeName Typecode Values1
2022-09-01 A 01 40
2022-09-01 B 02 50

I want to update table1 using the data from table2 such that

values3 = values1 - values2 and next day , values3 of previous date = values 1 or next day and so on.

date typeName Typecode Values1 Values2 Values3
2022-09-01 A 01 40 5 35
2022-09-02 A 01 35 10 25
2022-09-01 B 02 50 30 20
2022-09-02 B 02 20 10 10

How to write the T-SQL to do that?

sql-server sql
1 Answers

It isn't pretty., but wirks fine The CTE is , that we can get all needed Number s from table1 and table to get rolling

The Main SELECT use the LAG window function to get the last VaLues3, which is after the CTe not NULL anymore.

The COASLESCE is neede as for the first row of every PARTITION the LAG function will return NULL

WITH CTE AS
  (SELECT t1.[date], t1.[typeName],t1.[Typecode],t2.[Values1],t1.[Values2],t2.[Values1] -t1.[Values2] [Values3]
  FROM table1 t1 LEFT JOIN table2 t2 ON t1.[date] = t2.[date] AND  t1.[typeName] = t2.[typeName]
   AND t1.[Typecode]= t2.[Typecode])
SELECT
t1.[date], t1.[typeName],t1.[Typecode]
  ,  COALESCE(LAG(t2.[Values3]) OVER(PARTITION BY t2.[typeName], t2.[Typecode] ORDEr BY t1.[date]),t2.[Values1]) [value1]
  , t1.[Values2]
  , COALESCE(LAG(t2.[Values3]) OVER(PARTITION BY t2.[typeName], t2.[Typecode] ORDEr BY t1.[date]),t2.[Values1])  
   - t1.[Values2] as [Values3]
FROM table1 t1 LEFT JOIN CTE t2 ON t1.[date] = t2.[date] AND  t1.[typeName] = t2.[typeName]
   AND t1.[Typecode]= t2.[Typecode]
date typeName Typecode value1 Values2 Values3
2022-09-01 A 1 40 5 35
2022-09-02 A 1 35 10 25
2022-09-01 B 2 50 30 20
2022-09-02 B 2 20 10 10

fiddle

nbk0
Related