Was column ever equal to x in sql

I have a dataset that looks similar to this:

UniqueRef   Description       Date
    1                  Y    14/04/2020
    2                  Y    02/04/2020
    2                  X    07/04/2020
    2                  Y    12/04/2020
    3                  X    16/04/2020
    3                  Y    24/04/2020
    4                  Y    24/04/2020
    4                  X    21/04/2020
    4                  X    14/05/2020
    4                  Y    23/03/2020

I want to check if the description was ever equal to X, grouped by unique ref. I would also like it to have a separate column for the date in which description was X. If the unique ref has been X more than once then the date would be the most recent.

which would give an output similar to:

UniqueRef   Description            Date        Check     CheckDate
        1                  Y    14/04/2020       No          NA
        2                  Y    02/04/2020       Yes         07/04/2020
        2                  X    07/04/2020       Yes         07/04/2020
        2                  Y    12/04/2020       Yes         07/04/2020
        3                  X    16/04/2020       Yes         16/04/2020
        3                  Y    24/04/2020       Yes         16/04/2020
        4                  Y    24/04/2020       Yes         14/05/2020
        4                  X    21/04/2020       Yes         14/05/2020
        4                  X    14/05/2020       Yes         14/05/2020
        4                  Y    23/03/2020       Yes         14/05/2020

Answer

You can use window functions:

select t.*,
       max(case when description = 'X' then 'Y' else 'N' end) over (partition by uniqueref) as ever_x,
       max(case when description = 'X' then date end) over (partition by uniqueref) as x_date
from t;

Advertisement

Answer