Check sequential order of status of items

Question

Post reply

Check sequential order of status of items

SQL Bee

Old Hand

Points: 383
More actions
September 28, 2024 at 9:09 am
Go to Answer
#4463309

There is a table tblItems with below structure ( i cannot change the below table structure)
Objective is to check if the sequential order of the statuses is correct or not.
Order of the statuses should be in this sequence : QualityCheck, Printing, Labelling, Packing, Dispatch
If the status is not in this order for any item then we need to print such items.
Eg: item id 102 in below table in not correct as initially quality check should have been done hence print out 102.

Phil Parkin

SSC Guru

Points: 246987

September 28, 2024 at 8:50 pm

Answer

#4463415

SQL Bee wrote:

DROP TABLE IF EXISTS #tblItems;
CREATE TABLE #tblItems (Rownr INT NULL, ItemID int NULL, Status nvarchar(50) NULL , StatusDate smalldatetime NULL)
INSERT INTO #tblItems ( Rownr, ItemID , Status , StatusDate ) VALUES (1,101,'QualityCheck','15-01-2024 07:02:00'), (2,101,'Printing','15-01-2024 10:20:00'), (3,101,'Labelling','15-01-2024 15:30:00'), (4,101,'Packing','16-01-2024 16:22:00'), (5,101,'Dispatch','16-01-2024 18:01:00'), (6,102,'Printing','22-01-2024 07:10:00'), (7,102,'Labelling','23-01-2024 11:00:00'), (8,102,'QualityCheck','25-01-2024 08:10:00')

Ah yes, there's another thing I should have mentioned! After composing your code, please try running it. Yours fails to run.

Here's a possible solution:

DROP TABLE IF EXISTS #tblItems;

CREATE TABLE #tblItems
(
    Rownr INT NOT NULL
   ,ItemID INT NOT NULL
   ,Status NVARCHAR(50) NOT NULL
   ,StatusDate DATETIME NOT NULL
);

INSERT #tblItems
(
    Rownr
   ,ItemID
   ,Status
   ,StatusDate
)
VALUES
(1, 101, 'QualityCheck', '20240115 07:02:00')
,(2, 101, 'Printing', '20240115 10:20:00')
,(3, 101, 'Labelling', '20240115 15:30:00')
,(4, 101, 'Packing', '20240116 16:22:00')
,(5, 101, 'Dispatch', '20240116 18:01:00')
,(6, 102, 'Printing', '20240122 07:10:00')
,(7, 102, 'Labelling', '20240123 11:00:00')
,(8, 102, 'QualityCheck', '20240125 08:10:00');

DROP TABLE IF EXISTS #Ordering;

CREATE TABLE #Ordering
(
    StatusFrom NVARCHAR(50) NOT NULL
   ,StatusTo NVARCHAR(50) NOT NULL
);

INSERT #Ordering
(
    StatusFrom
   ,StatusTo
)
VALUES
('QualityCheck', 'Printing')
,('Printing', 'Labelling')
,('Labelling', 'Packing')
,('Packing', 'Dispatch');

WITH results
AS (SELECT ti.Rownr
          ,ti.ItemID
          ,ti.Status
          ,NextStatus = LEAD (ti.Status, 1, NULL) OVER (PARTITION BY ti.ItemID ORDER BY ti.StatusDate)
          ,o.StatusTo
          ,ti.StatusDate
    FROM #tblItems ti
        LEFT JOIN #Ordering o
            ON o.StatusFrom = ti.Status)
SELECT r.Rownr
      ,r.ItemID
      ,r.Status
      ,r.NextStatus
      ,r.StatusTo
      ,r.StatusDate
FROM results r
WHERE r.NextStatus <> r.StatusTo
ORDER BY r.ItemID
        ,r.StatusDate;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246987 More actions · Answer 1

Can you provide your sample data in consumable format, please? Here is some skeleton code to help you get started.

DROP TABLE IF EXISTS #tblItems;

CREATE TABLE #tblItems (Rownr INT, ...)

INSERT #tblItems
(
    Rownr, ...
)
VALUES
(1, ...
    ), (2, ...)

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

SQL Bee Old Hand Points: 383 More actions · Answer 2

DROP TABLE IF EXISTS #tblItems;

CREATE TABLE #tblItems (Rownr INT NULL, ItemID int NULL, Status nvarchar(50) NULL , StatusDate smalldatetime NULL)

INSERT INTO #tblItems

(

Rownr, ItemID , Status , StatusDate

)

VALUES

(1,101,'QualityCheck','15-01-2024 07:02:00'),

(2,101,'Printing','15-01-2024 10:20:00'),

(3,101,'Labelling','15-01-2024 15:30:00'),

(4,101,'Packing','16-01-2024 16:22:00'),

(5,101,'Dispatch','16-01-2024 18:01:00'),

(6,102,'Printing','22-01-2024 07:10:00'),

(7,102,'Labelling','23-01-2024 11:00:00'),

(8,102,'QualityCheck','25-01-2024 08:10:00')

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 3

Phil Parkin wrote:

Ah yes, there's another thing I should have mentioned! After composing your code, please try running it. Yours fails to run.

The code probably worked perfectly on the OPs machine. People forget that language settings change the date format and that the MDY format is mostly for the U.S. and a lot of the world uses the DMY format. Using SET DATEFORMAT DMY; makes the OP's code run perfectly. I also point out the issue in the article at the first link in my signature line below.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 246987 More actions · Answer 4

Jeff Moden wrote:

Phil Parkin wrote:
Ah yes, there's another thing I should have mentioned! After composing your code, please try running it. Yours fails to run.
The code probably worked perfectly on the OPs machine. People forget that language settings change the date format and that the MDY format is mostly for the U.S. and a lot of the world uses the DMY format. Using SET DATEFORMAT DMY; makes the OP's code run perfectly. I also point out the issue in the article at the first link in my signature line below.

Thanks, Jeff. As I was coding on my local PC which is set up in regional UK format, I stupidly assumed that SQL Server would use that.

I should've checked, I know! Apologies to the OP for the overly harsh comment.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Steve Collins SSCrazy Points: 2174 More actions · Answer 5

Hmmm... Phil's query returns 1 row (Rownr=7) for ItemId=102. The topic says: "If the status is not in this order for any item then we need to print such items." It could be interpreted to mean the query needs to return 3 rows for ItemId=102 since none of the Status'es is in the correct ordering of QualityCheck, Printing, Labelling, Packing, Dispatch

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Steve Collins SSCrazy Points: 2174 More actions · Answer 6

Yeah, it's not clear. The next sentence implies only 102 needs to be "printed" out. I'm picturing one of those giant dot matrix line printers that used to fill up a whole room. If there are no duplicates and the 2 columns (ItemId, Status) are a unique key for the table then possibly something like this. Afaik, Phil's code also assumes the 2 columns (ItemId, Status) are a unique key

with unq_cte as (
    select ItemId, min(Rownr) min_rownr
    from #tblItems
    group by ItemId)
select *
from unq_cte u
     cross join (values (1, 'QualityCheck'), 
                        (2, 'Printing'),
                        (3, 'Labelling'),
                        (4, 'Packing'),
                        (5, 'Dispatch'))
                        v(Rownr, [Status]) 
     join #tblItems t on u.ItemId=t.ItemId
                         and v.Rownr+u.min_rownr-1=t.Rownr
                         and v.[Status]<>t.[Status];

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Phil Parkin SSC Guru Points: 246987 More actions · Answer 7

@SQL_Bee, are you planning on posting back with an update on this thread?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel