Sql Query to check status change of an item

Transact-SQL
1

I have a table dbo.tblPresentationStatus (sql script at the bottom )

I have to select rows where the status change is wrong.
Review --> approve --> presentation --> close

From 'Review' the status for a specific presentation ID can change to either 'Approve' or 'Presentation' or 'Close'

From 'Approve' the status for a specific presentation ID can change to either 'Presentation' or 'Close'

From 'Presentation' the status for a specific presentation ID can change to only 'Close'

I want to write query to return Presentations with wrong status flow. So expected output as per records given in sql script at the bottom should be :

PrID | Status1 | Status2 | Status3 | Status 4

103 | REVIEW | PRESENTATION | APPROVE |CLOSE

101 | APPROVE | REVIEW | NULL | NULL

-----------------------------------sql script-------------------------------------------

DROP TABLE IF EXISTS #tblPresentation
CREATE TABLE #tblPresentation
(
PrID int NOT NULL,
PrName nvarchar(100) NULL
)

INSERT INTO #tblPresentation VALUES
(100,'PrA'),
(101,'PrB'),
(102,'PrC'),
(103,'PrD')

DROP TABLE IF EXISTS #tblPresentationStatus
CREATE TABLE #tblPresentationStatus
(
StatusID int NOT NULL,
PrID int NOT NULL,
PrStatus nvarchar(100) NOT NULL,
StatusDate datetime NOT NULL
)

INSERT INTO #tblPresentationStatus VALUES

--PRESENTATION ID 100
(1,100,'REVIEW','2024-01-01 00:00:00.00'),
(2,100,'APPROVE','2024-01-02 00:00:00.00'),
(3,100,'PRESENTATION','2024-01-03 07:00:00.00'),
(4,100,'CLOSE','2024-01-03 10:00:00.00'),

--PRESENTATION ID 101
(5,101,'APPROVE','2024-01-01 00:00:00.00'),
(6,101,'REVIEW','2024-01-03 10:00:00.00'), --wrong status change from 'APPROVE' to back ward status of ' REVIEW ' is not allowed

--PRESENTATION ID 102
(7,102,'REVIEW','2024-01-01 00:00:00.00'),
(8,102,'PRESENTATION','2024-01-02 00:00:00.00'),
(9,102,'CLOSE','2024-01-03 10:00:00.00'),

--PRESENTATION ID 103
(10,103,'REVIEW','2024-01-01 00:00:00.00'),
(11,103,'PRESENTATION','2024-01-02 00:00:00.00'),
(12,103,'APPROVE','2024-01-03 00:00:00.00'), --wrong status change from 'PRESENTATION' to back ward status of ' APPROVE' is not allowed
(13,103,'CLOSE','2024-01-04 00:00:00.00')

2

this query will return you the PrID that does not fulfilled the requirement. From there you should be able to produce the required result using PIVOT

; with cte as
(
  select *, NextStatus = LEAD(PrStatus) OVER (PARTITION BY PrID ORDER BY StatusDate)
  from  #tblPresentationStatus p
)
select *
from   cte c
where  (c.PrStatus = 'REVIEW'  and NextStatus not in ('APPROVE', 'PRESENTATION', 'CLOSE'))
or     (c.PrStatus = 'APPROVE' and NextStatus not in ('PRESENTATION', 'CLOSE'))
or     (c.PrStatus = 'PRESENTATION' and NextStatus not in ('CLOSE'))  
order by PrID, StatusDate
4 
Hi 

Another Way 

WITH StatusFlow
   AS (  SELECT   PrID
                , PrStatus
                , ROW_NUMBER() OVER ( PARTITION BY PrID ORDER BY StatusDate ) AS StatusOrder
           FROM   #tblPresentationStatus )
   , cte_2
   AS (  SELECT   PrID
                , MAX(CASE WHEN StatusOrder = 1 THEN PrStatus END) AS Status1
                , MAX(CASE WHEN StatusOrder = 2 THEN PrStatus END) AS Status2
                , MAX(CASE WHEN StatusOrder = 3 THEN PrStatus END) AS Status3
                , MAX(CASE WHEN StatusOrder = 4 THEN PrStatus END) AS Status4
           FROM   StatusFlow
          GROUP BY PrID )
SELECT   *
  FROM   cte_2
 WHERE   (  Status1 = 'REVIEW'  AND  Status2 = 'PRESENTATION' AND  Status3    = 'APPROVE' )
         OR 
		 (  Status1 = 'APPROVE' AND  Status2 = 'REVIEW' );