Check sequential order of status of items
-
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.
-
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
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')
- 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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - 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) - 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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
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
-
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
-
@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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply