I am trying to find minimum/earliest Date 1 corresponding to Date 2 based on ID1, ID2 and Date 2.

I need to use logic as:

Step 1: I need to get Maximum of Date 1.

Step 2: If Date 2 is null for max(Date 1) then I should populate Date 1 and Date 2 both as a null.

Step 3: Else go to stpe 4.

Step 4: Get minimum/earliest Date 1 corresponding to Date 2 from step 2.

Step 5: Populate both the dates(Date 2 and minimum Date 1 corresponding to this Date 2)

 Please find below example for your reference:

 

Expected Result Date 1 and Expected Result Date 2 are the columns which I am looking for my output Data.

 

ID 1 ID 2 Date 1 Date 2 Expected Result Date 1 Expected Result Date 2
101 1001 05/03/2020 0:00 09/03/2019 0:00    
101 1001 04/17/2020 0:00 09/03/2019 0:00    
101 1001 02/17/2020 0:00 09/03/2019 0:00    
101 1001 02/03/2020 0:00 09/03/2019 0:00 02/03/2020 0:00 09/03/2019 0:00
101 1001 01/17/2020 0:00 12/18/2018 0:00    
101 1001 01/03/2020 0:00 12/18/2018 0:00    
           
102 1002 05/03/2020 0:00   Null Null
102 1002 04/17/2020 0:00      
102 1002 12/03/2019 0:00      
102 1002 06/17/2019 0:00 03/14/2016 0:00    
102 1002 06/03/2019 0:00 03/14/2016 0:00    
           
103 1003 05/03/2020 0:00 03/14/2020 0:00 05/03/2020 0:00 03/14/2020 0:00
103 1003 04/17/2020 0:00      
103 1003 04/03/2020 0:00      
           
104 1004 05/03/2020 0:00   Null Null
104 1004 04/17/2020 0:00      
104 1004 04/03/2020 0:00      
104 1004 03/17/2020 0:00      

 

 

I am tryinng below expression for my calculated column:

case  when [Date 2] Is Null THEN NULL 

when [Date 2]<=[Date 1] then Min([Date 1]) OVER ([ID 1],[ID 2],[Date 2])

end 

 

But here I am getting oldest Date 1 and Date 2 in my expected column If Date 2 is Null for Max of Date 1 Coz here it is checking for all Date 1 and not for only Max of Date 1.

 

Can anyone please help me out with this scenario.?

Thanks in Advance..!

(1) Answer

Login