I have a table dbo.tblPresentationStatus
(SQL script for table at the bottom).
I have to select rows where the status change is wrong. Correct flow
is as below:
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 a query to return presentations with the wrong status flow. So expected output as per records given in script attached should be:
PrID | Status1 | Status2 | Status3 | Status 4 |
---|---|---|---|---|
103 | REVIEW | PRESENTATION | APPROVE | CLOSE |
101 | APPROVE | REVIEW | NULL | NULL |
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')
0
- Define the statuses in the right order
- Derive all valid status transitions
- Identify invalid status transitions
- Get all transitions of presentations with invalid transitions and pivot
-- Define statuses in the right order
CREATE TABLE #tblStatus
(
StatusID int NOT NULL,
PrStatus nvarchar(100) NOT NULL
)
INSERT INTO #tblStatus VALUES
(1, 'REVIEW'), (2, 'APPROVE'), (3, 'PRESENTATION'), (4, 'CLOSE')
-- Derive all valid status transitions
select fromStatus.PrStatus FromStatus, toStatus.PrStatus ToStatus
into #ValidTransitions
from #tblStatus fromStatus
cross apply (
select * from #tblStatus
where StatusID > fromStatus.StatusID
) as toStatus
-- Identify invalid status transitions
;with StatusTransitions as (
select *, LAG(PrStatus, 1) OVER (PARTITION BY PrID ORDER BY StatusDate,StatusID) PreviousStatus
from #tblPresentationStatus
)
select st.*
into #InvalidTransitions
from StatusTransitions st
left join #ValidTransitions vt
on st.PreviousStatus = vt.FromStatus
and st.PrStatus = vt.ToStatus
where
PreviousStatus is not null
and vt.FromStatus is null
-- Get all transitions of presentations with invalid transitions and pivot
;with AllStatusChangesOfPresentationsWithBadStatusChanges as (
select PrId, PrStatus, ROW_NUMBER() OVER (PARTITION BY PrID ORDER BY StatusDate,StatusID) StatusChangeNo
from #tblPresentationStatus ps
where exists(select 1 from #InvalidTransitions where ps.PrID = PrID)
)
SELECT PrId,
[1] as Status1, [2] as Status2, [3] as Status3, [4] as Status4
FROM AllStatusChangesOfPresentationsWithBadStatusChanges AS SourceTable
PIVOT (
MAX(PrStatus) FOR StatusChangeNo IN
([1], [2], [3], [4])
) AS PivotTable;
PrId | Status1 | Status2 | Status3 | Status4 |
---|---|---|---|---|
101 | APPROVE | REVIEW | NULL | NULL |
103 | REVIEW | PRESENTATION | APPROVE | CLOSE |
Another way is to string_agg the statusses ordered by the date, and check if they are equal to your expected result
select t.PrID,
t.PrName,
t.Statusses
from ( select p.PrID,
p.PrName,
(select string_agg(ps.PrStatus, ',') within group (order by ps.StatusDate)
from tblPresentationStatus ps
where ps.PrID = p.PrID
) as statusses
from tblPresentation p
) t
where t.Statusses <> 'REVIEW,APPROVE,PRESENTATION,CLOSE'
and t.Statusses <> 'REVIEW,PRESENTATION,CLOSE'
result is
PrID | PrName | Statusses |
---|---|---|
101 | PrB | APPROVE,REVIEW |
103 | PrD | REVIEW,PRESENTATION,APPROVE,CLOSE |
See this dbFiddle
If you really need the statusses in columns, that requires some work with this approach, see this dbFiddle
Maybe you can improve that using pivot
I put your flow in order with table variable
DECLARE @StatusFlow TABLE (Id INT,Name nvarchar(50));
INSERT INTO @StatusFlow (Id, Name)
VALUES (1, 'Review'),(2, 'Approve'),(3, 'Presentation'),(4, 'Close');
After that, just compare the Order Of Flow versus Order Of Row
And show line by line which is the mistake flow
;With T
as
(
select
pres.statusID
,pres.prID
,pres.prStatus
,pres.statusDate
,flow.id as currentOrderFlow
,dense_rank() over (partition by PrID ORDER BY StatusDate, flow.Id) as line
from #tblPresentationStatus pres
inner join @StatusFlow flow on flow.Name = pres.PrStatus
)
select * , (IIF(line <> currentOrderFlow,1,0)) as wrongFlow
From T
Complete flow in db fiddle
db fiddle
1
I suggest creating a explicit table of allowed status changes. Or apply this set of rows as a CTE.
See example
create table #StatusRoute (FromStatus nvarchar(100), ToStatus nvarchar(100));
insert into #StatusRoute values
('Review','Approve')
,('Review','Presentation')
,('Review','Close')
,('Approve','Presentation')
,('Approve','Close')
,('Presentation','Close')
;
Or
with #StatusRoute as(
select * from (values
('Review','Approve')
,('Review','Presentation')
,('Review','Close')
,('Approve','Presentation')
,('Approve','Close')
,('Presentation','Close')
)sr(FromStatus, ToStatus)
)
select PrId
,max(case when rn=1 then PrStatus end) Status1
,max(case when rn=2 then PrStatus end) Status2
,max(case when rn=3 then PrStatus end) Status3
,max(case when rn=4 then PrStatus end) Status4
,count(isError)errorQty
,string_agg(case when isError>0 then cast(rn+1 as varchar) end,',') ErrorList
from(
select *
,sum(case when PrStatus<>'CLOSE' and nextStatus is not null and ToStatus is null
then rn end)
over(partition by PrId) qtyErrors
,case when PrStatus<>'CLOSE' and nextStatus is not null and ToStatus is null
then rn
end isError
from(
select *
,lead(PrStatus)over(partition by PrId order by StatusDate) nextStatus
,row_number()over(partition by PrId order by StatusDate) rn
from #tblPresentationStatus
)a
left join #StatusRoute sr on a.PrStatus=sr.FromStatus and a.nextStatus=sr.ToStatus
)b
group by PrId
-- having count(isError)>0
;
PrId | Status1 | Status2 | Status3 | Status4 | errorQty | ErrorList |
---|---|---|---|---|---|---|
100 | REVIEW | APPROVE | PRESENTATION | CLOSE | 0 | null |
101 | APPROVE | REVIEW | null | null | 1 | 2 |
102 | REVIEW | PRESENTATION | CLOSE | null | 0 | null |
103 | REVIEW | PRESENTATION | APPROVE | CLOSE | 1 | 3 |
fiddle
Source data for test:
StatusID | PrID | PrStatus | StatusDate |
---|---|---|---|
1 | 100 | REVIEW | 2024-01-01 00:00:00.000 |
2 | 100 | APPROVE | 2024-01-02 00:00:00.000 |
3 | 100 | PRESENTATION | 2024-01-03 07:00:00.000 |
4 | 100 | CLOSE | 2024-01-03 10:00:00.000 |
5 | 101 | APPROVE | 2024-01-01 00:00:00.000 |
6 | 101 | REVIEW | 2024-01-03 10:00:00.000 |
7 | 102 | REVIEW | 2024-01-01 00:00:00.000 |
8 | 102 | PRESENTATION | 2024-01-02 00:00:00.000 |
9 | 102 | CLOSE | 2024-01-03 10:00:00.000 |
10 | 103 | REVIEW | 2024-01-01 00:00:00.000 |
11 | 103 | PRESENTATION | 2024-01-02 00:00:00.000 |
12 | 103 | APPROVE | 2024-01-03 00:00:00.000 |
13 | 103 | CLOSE | 2024-01-04 00:00:00.000 |
You may try to number the rows using the appropriate ORDER BY
clauses to find the wrong order and PIVOT
the results to get the expected output.
; WITH OrderCTE AS (
SELECT
StatusID, PrID, PrStatus,
ROW_NUMBER() OVER (
PARTITION BY PrID
ORDER BY StatusID
) AS Rn1,
ROW_NUMBER() OVER (
PARTITION BY PrID
ORDER BY
CASE
WHEN PrStatus = 'REVIEW' THEN 1
WHEN PrStatus = 'APPROVE' THEN 2
WHEN PrStatus = 'PRESENTATION' THEN 3
WHEN PrStatus = 'CLOSE' THEN 4
ELSE 5
END
) AS Rn2
FROM #tblPresentationStatus
), PivotCTE AS (
SELECT
PrID,
MAX(CASE WHEN Rn1 = 1 THEN PrStatus END) AS Status1,
MAX(CASE WHEN Rn1 = 2 THEN PrStatus END) AS Status2,
MAX(CASE WHEN Rn1 = 3 THEN PrStatus END) AS Status3,
MAX(CASE WHEN Rn1 = 4 THEN PrStatus END) AS Status4,
MAX(CASE WHEN Rn2 = 1 THEN PrStatus END) AS Correct1,
MAX(CASE WHEN Rn2 = 2 THEN PrStatus END) AS Correct2,
MAX(CASE WHEN Rn2 = 3 THEN PrStatus END) AS Correct3,
MAX(CASE WHEN Rn2 = 4 THEN PrStatus END) AS Correct4
FROM OrderCTE
GROUP BY PrID
)
SELECT PrID, Status1, Status2, Status3, Status4
FROM PivotCTE
WHERE
ISNULL(Status1, '') <> ISNULL(Correct1, '') OR
ISNULL(Status2, '') <> ISNULL(Correct2, '') OR
ISNULL(Status3, '') <> ISNULL(Correct3, '') OR
ISNULL(Status4, '') <> ISNULL(Correct4, '')
1