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:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>Review --> Approve --> Presentation --> Close
</code>
<code>Review --> Approve --> Presentation --> Close </code>
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
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>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')
</code>
<code>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') </code>
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
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>-- 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;
</code>
<code>-- 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; </code>
-- 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

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>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'
</code>
<code>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' </code>
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

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>DECLARE @StatusFlow TABLE (Id INT,Name nvarchar(50));
INSERT INTO @StatusFlow (Id, Name)
VALUES (1, 'Review'),(2, 'Approve'),(3, 'Presentation'),(4, 'Close');
</code>
<code>DECLARE @StatusFlow TABLE (Id INT,Name nvarchar(50)); INSERT INTO @StatusFlow (Id, Name) VALUES (1, 'Review'),(2, 'Approve'),(3, 'Presentation'),(4, 'Close'); </code>
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

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>;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
</code>
<code>;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 </code>
;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

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>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')
;
</code>
<code>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') ; </code>
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

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>with #StatusRoute as(
select * from (values
('Review','Approve')
,('Review','Presentation')
,('Review','Close')
,('Approve','Presentation')
,('Approve','Close')
,('Presentation','Close')
)sr(FromStatus, ToStatus)
)
</code>
<code>with #StatusRoute as( select * from (values ('Review','Approve') ,('Review','Presentation') ,('Review','Close') ,('Approve','Presentation') ,('Approve','Close') ,('Presentation','Close') )sr(FromStatus, ToStatus) ) </code>
with #StatusRoute as(
select * from (values
 ('Review','Approve')
,('Review','Presentation')
,('Review','Close')
,('Approve','Presentation')
,('Approve','Close')
,('Presentation','Close')
)sr(FromStatus, ToStatus)
)
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>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
;
</code>
<code>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 ; </code>
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.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>; 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, '')
</code>
<code>; 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, '') </code>
; 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