Query to check status change of an item

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

  1. Define the statuses in the right order
  2. Derive all valid status transitions
  3. Identify invalid status transitions
  4. 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

Trang chủ Giới thiệu Sinh nhật bé trai Sinh nhật bé gái Tổ chức sự kiện Biểu diễn giải trí Dịch vụ khác Trang trí tiệc cưới Tổ chức khai trương Tư vấn dịch vụ Thư viện ảnh Tin tức - sự kiện Liên hệ Chú hề sinh nhật Trang trí YEAR END PARTY công ty Trang trí tất niên cuối năm Trang trí tất niên xu hướng mới nhất Trang trí sinh nhật bé trai Hải Đăng Trang trí sinh nhật bé Khánh Vân Trang trí sinh nhật Bích Ngân Trang trí sinh nhật bé Thanh Trang Thuê ông già Noel phát quà Biểu diễn xiếc khỉ Xiếc quay đĩa Dịch vụ tổ chức sự kiện 5 sao Thông tin về chúng tôi Dịch vụ sinh nhật bé trai Dịch vụ sinh nhật bé gái Sự kiện trọn gói Các tiết mục giải trí Dịch vụ bổ trợ Tiệc cưới sang trọng Dịch vụ khai trương Tư vấn tổ chức sự kiện Hình ảnh sự kiện Cập nhật tin tức Liên hệ ngay Thuê chú hề chuyên nghiệp Tiệc tất niên cho công ty Trang trí tiệc cuối năm Tiệc tất niên độc đáo Sinh nhật bé Hải Đăng Sinh nhật đáng yêu bé Khánh Vân Sinh nhật sang trọng Bích Ngân Tiệc sinh nhật bé Thanh Trang Dịch vụ ông già Noel Xiếc thú vui nhộn Biểu diễn xiếc quay đĩa Dịch vụ tổ chức tiệc uy tín Khám phá dịch vụ của chúng tôi Tiệc sinh nhật cho bé trai Trang trí tiệc cho bé gái Gói sự kiện chuyên nghiệp Chương trình giải trí hấp dẫn Dịch vụ hỗ trợ sự kiện Trang trí tiệc cưới đẹp Khởi đầu thành công với khai trương Chuyên gia tư vấn sự kiện Xem ảnh các sự kiện đẹp Tin mới về sự kiện Kết nối với đội ngũ chuyên gia Chú hề vui nhộn cho tiệc sinh nhật Ý tưởng tiệc cuối năm Tất niên độc đáo Trang trí tiệc hiện đại Tổ chức sinh nhật cho Hải Đăng Sinh nhật độc quyền Khánh Vân Phong cách tiệc Bích Ngân Trang trí tiệc bé Thanh Trang Thuê dịch vụ ông già Noel chuyên nghiệp Xem xiếc khỉ đặc sắc Xiếc quay đĩa thú vị
Trang chủ Giới thiệu Sinh nhật bé trai Sinh nhật bé gái Tổ chức sự kiện Biểu diễn giải trí Dịch vụ khác Trang trí tiệc cưới Tổ chức khai trương Tư vấn dịch vụ Thư viện ảnh Tin tức - sự kiện Liên hệ Chú hề sinh nhật Trang trí YEAR END PARTY công ty Trang trí tất niên cuối năm Trang trí tất niên xu hướng mới nhất Trang trí sinh nhật bé trai Hải Đăng Trang trí sinh nhật bé Khánh Vân Trang trí sinh nhật Bích Ngân Trang trí sinh nhật bé Thanh Trang Thuê ông già Noel phát quà Biểu diễn xiếc khỉ Xiếc quay đĩa
Thiết kế website Thiết kế website Thiết kế website Cách kháng tài khoản quảng cáo Mua bán Fanpage Facebook Dịch vụ SEO Tổ chức sinh nhật