Qasim0787 Qasim0787 - 3 months ago 9
SQL Question

How to pickup up a record based on second column is null

Hi I have following table:

Lets say Todays Date is 01-Sep-2016

ID Change_Dt Cancel_Dt
1 1/1/2015 NULL
2 1/1/2015 1/1/2015
3 1/1/2015 2/1/2015
3 3/1/2015 NULL
4 1/1/2015 12/1/2015
4 1/1/2016 9/1/2016
4 10/1/2016 NULL
5 10/1/2016 NULL
6 9/1/2016 9/1/2016
7 1/1/2015 NULL
7 1/1/2016 9/1/2016


Requirement: If across all records if there is atleast one record for an ID where Change_Dt <= Today's date and Cancel_Dt is Null
then Y
else N

Required Results:

ID Results

1 Y
2 N
3 Y
4 N
5 N
6 N
7 Y


Any help on this will be great.

My code that I tried:

select m.ID,
Case when(m.Cancel_Dt is null and m.Create_Dt < = '01-SEP-2016')
then 'Y'
else 'N'
end Indicators

from mytable m

Answer

You can use the CASE you wrote with a small change. Utilizing the MAX over the CASE, within yet another CASE will allow you to pick the correct value.

SELECT ID,
    CASE WHEN 
        MAX(case when(m.Cancel_Dt is null and m.Create_Dt < = '01-SEP-2016')
            then 1
            else 0
        end) = 1 THEN 'Y' 
    ELSE 'N'
    END Indicators 
FROM my_records
GROUP BY ID

Note that you don't technically need to use 1 and 0 since Y > N however this make the query clearer (at least to me).