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

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	AMT3	AMT4	TOTAL AMT
C	1234	3/1	2	2	2	4
A	1234	3/2	5	0	0	3
B	1234	3/3	2	2	3	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. If the amount in the AMT2 column is 0, then I need the amount in column AMT4. Otherwise, I want the TOTAL AMT column to be a total of AMT1 and AMT3. Then on top of all 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 part of my problem handled (retrieving the value from AMT2 for the TOTAL AMT column), but I’m struggling with how to get the rest in another CASE WHEN (if that’s even the right approach) that will allow me to get AMT4 and 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

Answer

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

Advertisement

Answer