Forum Discussion

SqlPuzzle's avatar
SqlPuzzle
Copper Contributor
Dec 03, 2024

sql query to check status change of an item

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

I have to select rows where the status change is wrong. Flow should be as this:
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')

1 Reply

  • rodgerkong's avatar
    rodgerkong
    Iron Contributor
    Dec 03, 2024
    1. Create a table with Table Value Constructor to assign a int value to Status string. The table named SI, Column I is the int value.
    2. Use CTE create a dataset which contains 2 column calculated from window function. One column contains sequence values partition by PrID which named SN. Another one contains the difference of I between current row and the previous row which named Diff.
    3. Column SN will be used to construct PIVOT table. Obviously, the row with Diff value negative or zero has issue.
    4. Filter all PrID that have Diff issue, and PIVOT them by SN will get result.

    Here is code

    WITH C  --CTE table
AS
(
	SELECT PS.*, SI.I
		, ROW_NUMBER() OVER (PARTITION BY PS.PrID ORDER BY StatusID) AS SN  --Will used in PIVOT
		, SI.I - LAG(SI.I, 1, 0) OVER (PARTITION BY PS.PrID ORDER BY StatusID) AS Diff  --Find row has issue
	FROM 
		(VALUES(1, 'REVIEW'), (2, 'APPROVE'), (3, 'PRESENTATION'), (4, 'CLOSE')) AS SI(I, S)  --Assign int value to status 
	INNER JOIN #tblPresentationStatus PS
	ON PS.PrStatus = SI.S
)
SELECT 
	PrID, 
	[1] AS [Status1], 
	[2] AS [Status2], 
	[3] AS [Status3], 
	[4] AS [Status4]
FROM
(
	SELECT PrID, PrStatus, SN
	FROM 
	C WHERE PrID IN 
		( SELECT PrID FROM C WHERE Diff<=0 ) --Get PrID that has issue
)D
PIVOT 
(
	 MIN(PrStatus) FOR SN IN ([1],[2],[3],[4])
)PP

     

Resources