How do I do multiple CASE WHEN, using LEAD() to look at an entire row in SQL?-CodePudding

I have a set of data that I need to manually adjust a column (inserting 0) for the row trailing a specific row, based on a record in one specific column. For example:

My desired output:

TYPE	ObjectID	DATE	AMT1	AMT2	AMT3	TOTAL AMT
C	1234	3/1	2	2	2	4
A	1234	3/2	5	0	0	3
B	1234	3/3	2	3	0	0

Looking at the TYPE column, only when the type of the current row is 'A', I want whatever is in the AMT2 column below it to be populated in the TOTAL AMT column for that row. Otherwise, I want the TOTAL AMT column to be a total of AMT1 and AMT3. Then on top of that, I need the TOTAL AMT column to have 0 hardcoded into it for the row below TYPE 'A' (instead of summing AMT1 and AMT3).

I used the LEAD() function to get the first half of my problem handled (retrieving the value from AMT2 for the TOTAL AMT column), but I'm struggling with how to get it in another CASE WHEN (if that's even the right approach) that will allow me to hardcode the 0 in for the specific row needed. Any help is greatly appreciated!

Here is the code I'm using for retrieving the AMT2 from the below row:

CASE WHEN TYPE = 'A' THEN LEAD(AMT2, 1) OVER (PARTITION BY ObjectID ORDER BY DATE ASC)      
     ELSE (AMT1   AMT3)
END AS TOTAL AMT

CodePudding user response：

It appears you just need a nested case expression:

case when 
  type = 'A' then 
    lead(AMT2) over (partition by ObjectID order by DATE)      
else 
  case when lag(type) over (partition by ObjectID order by DATE)='A' then 
    0 
  else
    AMT1   AMT3 
  end
end as TOTALAMT;

Demo Fiddle

CodePudding user response：

You can do it by using lead and case (result here)

select *,case type when 'A' then lead(amt2,1) over (partition by objectid order by date) else amt1 amt3 end as total
from t1