I have here a query that shows three columns::
SELECT CLIENT_NO, LOB_CD ,TO_DATE(CREATE_DT,'MMYYYY') AS PURCHASE_DT ,ROW_NUMBER () OVER(PARTITION BY CLIENT_NOORDER BY CREATE_DT ASC) AS RN FROM CLIENTS_T
now, I have to solve this:
- if the client has 2 RN and either purchase date is below 2019, then the tag should be "OLD Client"
- If the client has 1 RN and the purchase date is below 2019, then should still be tagged as "Old Client" else, if the purchase date is above 2019 then should be tagged as "2019 new client" granted that no purchase was done in 2020, 2021 and 2022.
- same goes with 2020, 2021 and 2022. If there are no purchase the prior year, they should be tagged as "NEW".
Is there a way to come up with this? here is the table output, with desired tagging with remarks:
Hope someone can help.