I have the below requirement.
I should get the below output.
Assuming the ID column as Primary_key I have made the following sample query:
WHEN NEW_DATA.Status = OLD_DATA.STATUS
END AS Status_Change_ind,
FROM TABLENAME AS OLD_DATA
TABLENAME AS NEW_DATA
ON NEW_DATA.ID = OLD_DATA.ID
QUALIFY ROW_NUMBER() OVER(ORDER BY StartDate) = 1;
Please let me know if anything is ambigous!
Select IDR , STATUS ,
CASE WHEN STATUS = PREV_STATUS THEN 0 ELSE 1 END AS STATUS_CHANGE_IND , START_DT
Select IDR , Status ,
COALESCE(MAX(STATUS) OVER (PARTITION BY IDR ORDER BY START_DT ROWS BETWEEN 1 preceding and 1 Preceding) , 'NA' ) as PREV_STATUS,
The above query should work
SEL ID , STATUS , DT , MAX(STATUS) OVER(PARTITION BY ID ORDER BY DT ROWS 1 PRECEDING) AS COL1 , MIN(STATUS) OVER(PARTITION BY ID ORDER BY DT ROWS 1 PRECEDING) AS COL2 , CASE WHEN COL2 <> COL1 THEN 1 ELSE 0 END AS COL3 FROM MY_TABLE;