sql - Ranking / Case Statement referencing a CTE Table

Question

I have the following case expression that I need help revising - I am not sure how to properly format this case statement:

,case when Ranking(statusing_Facility_CD) <> Ranking-1(Statusing_Facility_CD) 
 and Ranking(Equipment_Status_Type_CD)='ENR' and Ranking-1(Equipment_Status_Type_CD)='ARV' 
 then DATEDIFF(hh,Ranking(Status_Time,Ranking-1(Status_Time) end as ENR_TO_ARV

The Result I am going for is to have another column named 'ENR_ARV_Time' that would return the datediff(hh) for the Statusing_Facility_CD where the equipment_Status_type_cd = 'ENR':

Equipment_Unit_NB|Statusing_Facility_CD| Status_Time      | Equipment_Status_Type_CD| Ranking 
117349                  402             2020-12-23 10:37:26          ARR     1      
117349                  422             2020-12-23 05:07:17          ENR     2      
117349                  422             2020-12-22 22:45:05          LDD     3      
117349                  422             2020-12-21 20:22:05          LDG     4      
117349                  422             2020-12-21 14:22:37          UAD     5      
117349                  422             2020-12-21 08:25:44          ARV     6      
117349                  621             2020-12-21 02:03:45          ENR     7      
117349                  621             2020-12-20 17:18:44          ARR     8      
117349                  347             2020-12-20 04:51:25          ENR     9      
117349                  347             2020-12-20 03:37:45          LDD    10  

question from:https://stackoverflow.com/questions/65902432/ranking-case-statement-referencing-a-cte-table

Answer 1

Waitting for answers