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

[G One]

I need to use logic as:

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

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

Step 3: Else go to stpe 4.

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

Step 5: Populate both the dates(Date 2and minimum Date 1corresponding 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]

[David Boot-Olazabal]

Hi Gnatthani007,

You may try this formula instead:

case when [Date 2] Is Null THEN NULL

when Min([Date 1]) Over ([iD 1],[iD 2],[Date 2])=[Date 1] then Min([Date 1]) OVER ([iD 1],[iD 2],[Date 2])

Else Null

end

As you first identify the 'Date2 isNull', you then have to make sure that you only want the min Date1 displayed on the line where you actually have the earliest date per Id1, Id2, Date2. You can achieve this with the 'Min([Date 1]) Over ([iD 1],[iD 2],[Date 2])=[Date 1]' expression. You 'grant' this line with a value you are looking for. And then finally, as all other Date2 should be empty, you end the case statement with the 'Else Null' part.

Kind regards,

David

[G One]

Thanks David..!

 

above logic worked..