September 16, 2015 at 9:54 am
Hello All,
I've a table that stores operationcode for each jobnumber. The jobnumber can have multiple operationcode. From the below DDL, I need to show all the jobs that have operation codes as 2001 and 2002. In the below DDL Jobnumber 80011 has both the operation codes 2001 and 2002 so this job will display on the report.
On the other hand Job 80021 only has operationcode 2001 and I do not want this job to show up on the report.
I need to show all the operationcodes for a job if it has operationcode 2001 and 2002.
How can I achieve this? Any help is appreciated
Thanks
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')
;
SELECT
J.DT_ID
, OperationCode
,EmployeeCode
,JobNumber
FROM @TEST_DATA J
order by JobNumber
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) A socialist is someone who will give you the shirt off *someone else's* back.
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) A socialist is someone who will give you the shirt off *someone else's* back.
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 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply