Need Analytical window query help

Database
Enthusiast

Need Analytical window query help

Hi All,

I have the below requirement.

Incoming data

ID  Status         Start Date

1   Active              7-Aug 

1   Suspended     8-Aug 

1   Active              9-Aug 

1   Active             10-Aug 

1   Suspended     11-Aug 

I should get the below output.

ID  Status      Status_Change_ind     Start Date

1   Active                       0                      7-Aug 

1   Suspended              1                       8-Aug 

1   Active                       1                       9-Aug 

1   Active                       0                      10-Aug 

1   Suspended              1                      11-Aug 

When ever a status Change for the same Primary Key i have to generate  Status_Change_ind as 1 . When no change,  Status_Change_ind has to be 0. More than 1 record can come at the same time. So i have to use a Window function order by start date.Please help me with the Query.

4 REPLIES
Enthusiast

Re: Need Analytical window query help

Assuming the ID column as Primary_key I have made the following sample query:

SELECT 
ID,
Status,
CASE
WHEN NEW_DATA.Status = OLD_DATA.STATUS
THEN 1
ELSE 0
END AS Status_Change_ind,
StartDate
FROM TABLENAME AS OLD_DATA
LEFT JOIN
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!

Khurram
Enthusiast

Re: Need Analytical window query help

Thank You for the reply. I do not want to use Self join .I want to do it using Window functions.

Enthusiast

Re: Need Analytical window query help

Select IDR , STATUS , 
CASE WHEN STATUS = PREV_STATUS THEN 0 ELSE 1 END AS STATUS_CHANGE_IND , START_DT
FROM
(
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,
Cnt
from
YOUR_TABLE
) TMP

The above query should work

Thanks

Manik

Enthusiast

Re: Need Analytical window query help

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;