Compare rows within the same table

Question

Compare rows within the same table

SQL Bee

Mr or Mrs. 500

Points: 532
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 5 posts - 1 through 5 (of 5 total)

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

ScottPletcher SSC Guru Points: 101192 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: 3387 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: 101192 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".

Brandie Tarvin SSC Guru Points: 173105 More actions · Answer 4

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;

This reply was modified 6 months, 4 weeks ago by Brandie Tarvin.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Compare rows within the same table

Cookies on SQLServerCentral