How to get previous rows in SQL Server

Question

Post reply

How to get previous rows in SQL Server

Krish319

Old Hand

Points: 352
More actions
June 9, 2022 at 10:04 am

#4046795

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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

DesNorton SSC-Insane Points: 24386 More actions · Answer 1

There is nothing in your table that links those records together (Like CustomerID).

Create some sample data

CREATE TABLE #SLA (
    CustomerID  int
  , DeptName    varchar(20)
  , Date        date
  , Description varchar(50)
);

INSERT INTO #SLA ( CustomerID, DeptName, Date, Description )
VALUES  ( 1, 'Level2', '2022-06-07', 'Ticket Breached SLA . SLA Expired' )
      , ( 1, 'Level1', '2022-06-06', 'Moved to hold' )
      , ( 1, 'Level1', '2022-06-05', 'TRANSFER' )
      , ( 1, 'Level1', '2022-06-04', 'Working' )
      , ( 1, 'Level1', '2022-06-03', 'Working' )
      ---------------------------------------------------
      , ( 2, 'Level2', '2022-06-07', 'Ticket Breached SLA . SLA Expired' )
      , ( 2, 'Level1', '2022-05-05', 'Moved to hold' )
      , ( 2, 'Level0', '2022-04-04', 'New' );

Query the data

WITH cteSLAexpired AS (
  SELECT *
  FROM #SLA AS s
  WHERE s.Description LIKE '%SLA Expired%'
)
SELECT p.*
FROM cteSLAexpired AS x
OUTER APPLY (SELECT TOP (2) s.*
             FROM #SLA AS s
             WHERE s.CustomerID = x.CustomerID
               AND s.Date < x.Date
             ORDER BY s.Date DESC
            ) AS p;

How to post data/code on a forum to get the best help.