November 21, 2024 at 7:04 pm
I have a table. Structure in script below.
I have to compare and see if for a job id if the order of taskname is first 'Print' and then 'Distribute' based on the PrintDate column.
I have to select rows where the 'Distribute' is coming BEFORE the 'Print' task based on PrintDate column (eg: JobID 3 in script below as first Distribute and then only Print. )
--===SQL Script
create table dbo.tblPrintTask
(JobID int null,
TaskID int null,
TaskName nvarchar(255) null,
PrintDate date null)
insert into tblPrintTask VALUES
(1,100,'Print','01-10-2024'),
(1,101,'Distribute','01-12-2024'),
(2,100,'Print','01-08-2024'),
(2,101,'Distribute','01-10-2024'),
(3,100,'Distribute','01-10-2024'),
(3,101,'Print','01-15-2024')
select * from dbo.tblPrintTask
November 21, 2024 at 8:25 pm
SELECT JobID
FROM dbo.tblPrintTask
GROUP BY JobID
HAVING MAX(CASE WHEN TaskName = 'Distribute' THEN TaskID END) <
MAX(CASE WHEN TaskName = 'Print' THEN TaskID END)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 21, 2024 at 11:55 pm
Scott's code is definitely more concise. Here is another method leveraging LEAD
; WITH TaskData AS
(SELECT *,
NextTask = LEAD(TaskName, 1, NULL) OVER (PARTITION BY JobID ORDER BY PrintDate)
FROM dbo.tblPrintTask
)
SELECT JobID
FROM TaskData
WHERE TaskName = 'Distribute'
AND NextTask = 'Print'
November 22, 2024 at 12:50 am
The TaskNames would have to be contiguous for that method to work. I wanted to also handle some other TaskName appearing between "Distribute" and "Print", just in case.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 25, 2024 at 9:28 pm
So I'm a big fan of SARGability, which means I would take Scott's version and do it with a CTE.
WITH JobTypes AS (SELECT JobID, MAX(CASE WHEN TaskName = 'Distribute' THEN TaskID END) AS DistribID,
MAX(CASE WHEN TaskName = 'Print' THEN TaskID END) AS PrintID
FROM dbo.tblPrintTask)
SELECT pt.JobID, jt.DistribID, jt.PrintID
FROM dbo.tblPrintTask pt
INNER JOIN JobTypes jt
ON pt.JobID = jt.JobID
WHERE jt.DistribID < jt.PrintID;
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy