September 16, 2015 at 10:08 am
This is probably the most straightforward, albeit perhaps not the very most efficient:
SELECT
J.DT_ID
,J.OperationCode
,J.EmployeeCode
,J.JobNumber
FROM @TEST_DATA J
INNER JOIN (
SELECT
J2.JobNumber
FROM @TEST_DATA J2
WHERE J2.OperationCode IN ('2001', '2002')
GROUP BY J2.JobNumber
HAVING MAX(CASE WHEN J2.OperationCode = '2001' THEN 1 ELSE 0 END) = 1 AND
MAX(CASE WHEN J2.OperationCode = '2002' THEN 1 ELSE 0 END) = 1
) AS O ON O.JobNumber = J.JobNumber
ORDER BY JobNumber
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".
September 16, 2015 at 10:10 am
GREAT job providing the setup.
in this case, since you have to check for two values,, i think you want to join the table against itself.
you can join the same table as two aliases.
a bit faster way is to use an EXISTS,and a correlatation to the outer table to test the second condition:
here's both examples:
SELECT
J.DT_ID
, J.OperationCode
,J.EmployeeCode
,J.JobNumber
FROM @TEST_DATA J
INNER JOIN @TEST_DATA X
ON J.JobNumber = X.JobNumber
WHERE X.OperationCode = '2002'
AND J.OperationCode = '2001'
SELECT
J.DT_ID
, OperationCode
,EmployeeCode
,JobNumber
FROM @TEST_DATA J
WHERE EXISTS(SELECT * FROM @TEST_DATA X WHERE OperationCode = '2002' AND J.JobNumber = X.JobNumber)
AND OperationCode = '2001'
order by JobNumber
Lowell
September 16, 2015 at 10:33 am
My understand was that all operation codes needed shown, not just the two being checked.
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".
September 16, 2015 at 1:58 pm
ScottPletcher & Lowell- Thank you very much for your help.
September 21, 2015 at 7:09 am
ScottPletcher,
Thank you for your help before. I've to create a new report where I have to show all the jobs that DO NOT have OperationCode 2001 and 2002. Any help on this is greatly appreciated.
Thanks
September 21, 2015 at 7:38 am
Any help is appreciated. Thanks again everyone.
September 21, 2015 at 7:55 am
Here's a simpler option to Scott's query that can be easily converted to the second report.
SELECT
J.DT_ID
,J.OperationCode
,J.EmployeeCode
,J.JobNumber
FROM @TEST_DATA J
WHERE J.JobNumber /*NOT*/ IN (
SELECT J2.JobNumber
FROM @TEST_DATA J2
WHERE J2.OperationCode IN ('2001', '2002')
GROUP BY J2.JobNumber
HAVING MIN(J2.OperationCode) = '2001' AND
MAX(J2.OperationCode) = '2002'
)
ORDER BY JobNumber
September 21, 2015 at 8:06 am
Thank you very much Luis.
September 21, 2015 at 8:35 am
Luis,
The query you suggested works fine. I've a small issue with it. If there is Jobnumber that has OperationCode 2001 it still shows up. In the below DDL there is a jobnumber 80031 that has operationcode 2001 and it still shows up. I only want to show jobs that do not have either of the OperationCode 2001, 2002 or both.
Thanks
Here is the new DDL
USE tempdb;
GO
DECLARE @TEST_DATA TABLE
(
DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
, OperationCodeVARCHAR(10) NOT NULL
, EmployeeCode VARCHAR(10) NOT NULL
, JobNumber VARCHAR(10) NOT NULL
);
INSERT INTO @TEST_DATA (OperationCode, EmployeeCode,JobNumber)
VALUES
('1100', 'AUT', '80011' )
,('1180', 'AUT', '80011')
,('1910', 'AUT', '80011')
,('2002', 'AUT', '80011')
,('7261', 'AUT', '80011')
,('2001', 'AUT', '80011')
,('1100', 'AUT', '80021')
,('1180', 'AUT', '80021')
,('1910', 'AUT', '80021')
,('2001', 'AUT', '80021')
,('7261', 'AUT', '80021')
,('8001', 'AUT', '80021')
,('8002', 'AUT', '80021')
,('8003', 'AUT', '80021')
,('1180', 'AUT', '80031')
,('1910', 'AUT', '80031')
,('2001', 'AUT', '80031')
,('7261', 'AUT', '80031')
,('8001', 'AUT', '80031')
,('8002', 'AUT', '80031')
,('8003', 'AUT', '80031')
;
SELECT
J.DT_ID
,J.OperationCode
,J.EmployeeCode
,J.JobNumber
FROM @TEST_DATA J
WHERE J.JobNumber NOT IN (
SELECT J2.JobNumber
FROM @TEST_DATA J2
WHERE J2.OperationCode IN ('2001', '2002')
GROUP BY J2.JobNumber
HAVING MIN(J2.OperationCode) = '2001' AND
MAX(J2.OperationCode) = '2002'
)
ORDER BY JobNumber
September 21, 2015 at 8:53 am
That's easier, just remove the GROUP BY (and HAVING) clause as that's only needed if you want to exclude the job numbers that have both operation codes.
September 21, 2015 at 2:58 pm
Thank you again Luis
Viewing 11 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply