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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply