Compare rows within the same table

Question

Compare rows within the same table

SQL Bee

Old Hand

Points: 367
More actions
November 21, 2024 at 7:04 pm

#4487426

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

Viewing 4 posts - 1 through 3 (of 3 total)

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

ScottPletcher SSC Guru Points: 101098 More actions · Answer 1

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".

SoCal_DBD Hall of Fame Points: 3382 More actions · Answer 2

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'

ScottPletcher SSC Guru Points: 101098 More actions · Answer 3

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".