SQL skills: Step-by-step reduction

SQL skills: Step-by-step reduction

Opposite to tiered Accumulation

 

-- 1 create a temporary table with the following data:

Select * into # T
From (select 'a1' T1, 440 T2, 1 T3 Union
Select 'a2 'T1, 445 T2, 2 T3 Union
Select 'a3 'T1, 253 T2, 3 T3 Union
Select 'a1' T1, 509 T2, 4 T3 Union
Select 'a5 'T1, 376 T2, 5 T3 Union
Select 'a6 'T1, 142 T2, 6 T3)

Select * from # T

 

-- 2 result
/*
T1 T2 T3
--------------------------
A_1 440 1
A2 445 2
A3 253 3
A4 509 4
A5 376 5
A6 142 6
*/

-- 3 step-by-step reduction

Select B1 .*,
B1.t2-(select top 1 T2 from # T B2 where b1.t3 <b2.t3) Result
From # T B1

 

-- 4 Results
/*
T1 T2 T3 result
-------------------------------------
A1 440 1-5
A2 445 2 192
A3 253 3-256
A4 509 4 133
A5 376 5 234
A6. 142 6 null
*/