How to get previous rows in SQL Server

Question

-2

I have a table data like below. Now I need to find the previous 2 rows where I have SLA Expired in my description.

DeptName Date Descrption

Level2 7/6/2022 Ticket Breached SLA . SLA Expired
Level1 6/6/2022 Moved to hold
Level1 5/6/2022 Transfer
Level1 4/6/2022 Working
Level1 3/6/2022 Working
Output :

DeptName Date Descrption
Level1 6/6/2022 Moved to hold
Level1 5/6/2022 Transfer

Answer 1

create table test (DeptName varchar(20), Date date, description varchar(200))  
  
insert into test values('Level2',getdate()-2,'Ticket Breached SLA . SLA Expired')  
,('Level1',getdate()-3,'Moved to hold')  
,('Level1',getdate()-4,'Transfer')  
,('Level1',getdate()-5,'Working')  
,('Level1',getdate()-6,'Working')  
  
  
;with mycte as (  
select *,row_number() Over(order by Date desc) rn from test  
)  
,mycte2 as (  
select DeptName,Date, description,rn, d.rnSLA from mycte  
cross apply (select rn rnSLA from mycte where Description like'%SLA Expired%') d  
)  
select  DeptName,Date, description from mycte2  
where rn in (rnSLA+1,rnSLA+2)  
  
  
drop table test  

Answer 2

Hi @Learner
You can use LAG Function to get previous row data. Please check this query :
;WITH CTE AS
(
SELECT DeptName,Date,description
,LAG(description,1)OVER(ORDER BY Date DESC) OFF_SET_1
,LAG(description,2)OVER(ORDER BY Date DESC) OFF_SET_2
FROM test
)
SELECT DeptName,Date,description
FROM CTE
WHERE (OFF_SET_1 LIKE '%SLA Expired%') OR (OFF_SET_2 LIKE '%SLA Expired%')

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.