Query to find corresponding column value for every EMP_STATUS column value change to T from A

Question

Hello Team,

I have input table having data like below.

CID_WINID EMP_STATUS EFFECTIVE_DATE
C0042930 A 2021-08-31
C0042930 A 2021-09-30
C0042930 A 2021-10-31
C0042930 T 2021-11-06
C0042930 A 2021-11-30
C0042930 A 2021-12-31
C0042930 A 2022-01-31
C0042930 A 2022-02-28
C0042930 T 2022-03-31
C0042930 T 2022-04-30
C0042930 T 2022-05-31
C0042930 T 2022-06-30
C0042930 T 2022-07-31
C0042930 T 2022-08-31

I want to get output data like below with out using while loop and cte(cte and loop doesn't work in Azure SQL datawarehouse )

CID_WINID EMP_STATUS EFFECTIVE_DATE
C0042930 T 2021-11-06
C0042930 T 2022-03-31

Here, we should get all rows which are having EMP_STATUS 'T' and EMP_STATUS of previous rows are 'A'

• Note
• table structure is
  CID_WIND(varchar(200) Not NULL), Effective_DATE(date,not null), EMP_STATUS
• version -- Microsoft Azure SQL Data Warehouse - 10.0.15670.0 Sep 1 2022 04:02:01 Copyright (c) Microsoft Corporation

Answer 1

Hi @Tangirala DineshReddy , welcome to Microsoft Q&A forum.

Please try below, I checked it on SQL and Dedicated SQL Pool:

create table CID_WIND_TABLE(CID_WIND varchar(200) , Effective_DATE date, [EMP_STATUS] varchar(200));  
  
insert into CID_WIND_TABLE values ('C0042930','2021-08-31','A');  
insert into CID_WIND_TABLE values ('C0042930','2021-09-30','A');  
insert into CID_WIND_TABLE values ('C0042930','2021-10-31','A');  
insert into CID_WIND_TABLE values ('C0042930','2021-11-06','T');  
insert into CID_WIND_TABLE values ('C0042930','2021-11-30','A');  
insert into CID_WIND_TABLE values ('C0042930','2021-12-31','A');  
insert into CID_WIND_TABLE values ('C0042930','2022-01-31','A');  
insert into CID_WIND_TABLE values ('C0042930','2022-02-28','A');  
insert into CID_WIND_TABLE values ('C0042930','2022-03-31','T');  
insert into CID_WIND_TABLE values ('C0042930','2022-04-30','T');  
insert into CID_WIND_TABLE values ('C0042930','2022-05-31','T');  
insert into CID_WIND_TABLE values ('C0042930','2022-06-30','T');  
  
SELECT CID_WIND,Effective_DATE,[EMP_STATUS]  
FROM (  
    SELECT *,  
       case lag([EMP_STATUS]) over(partition by CID_WIND order by Effective_DATE) when [EMP_STATUS] then 0 else 1 end flag  
    FROM CID_WIND_TABLE    
  ) t  
WHERE flag = 1 and [EMP_STATUS] = 'T'  
ORDER BY Effective_DATE;  

Referenced Article: How to compare two consecutive rows values in SQL

Please let us know if this helps.