August 31, 2011 at 10:18 am
We have mfg jobs with several operations, and they always go in operation number sequence. I have been asked to pull a report that shows the first incomplete operation for each order. Using this script to build the table:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#JobOrder','U') IS NOT NULL DROP TABLE #JobOrder
GO
--===== Create the test table
CREATE TABLE #JobOrder(
JobNVARCHAR(15),
Operation NVARCHAR(3),
OperName NVARCHAR(10),
Complete SMALLINT
)
--===== Insert the test data into the test table
INSERT INTO #JobOrder
(Job, Operation, OperName, Complete)
SELECT '1108PIP059', '5', 'Header', 1 UNION ALL
SELECT '1108PIP059', '20', 'FWHI', 1 UNION ALL
SELECT '1108PIP059', '30', 'PIP', 1 UNION ALL
SELECT '1108PIP059', '34', 'PIP', 1 UNION ALL
SELECT '1108PIP059', '36', 'FOPM', 0 UNION ALL
SELECT '1108PIP059', '38', 'PIP', 0 UNION ALL
SELECT '1108PIP059', '40', 'PIP', 0 UNION ALL
SELECT '1108PIP059', '50', 'FINSP', 0 UNION ALL
SELECT '1108PIP059', '60', 'FWHR', 0
This is the expected result:
Job Operation OperName Complete
--------------- --- ---------- -
1108PIP059 36 FOPM 0
What is the best way to do this? Let me know if you need more information.
Thanks.
August 31, 2011 at 10:31 am
Since I'm assuming you might want to be able to pull up the firdt incomplete operation for any number of orders at the same time, you might care to leverage 2005's Row_number().
Something like this:
;With JobOperationCTE as (
Select Row_number() over (partition by job order by operation) RN,
* from #jobOrder
where complete=0)
select Job, Operation, OperName, Complete
from JobOperationCTE
where rn=1
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 31, 2011 at 11:00 am
What about
SELECT TOP 1 * FROM #JobOrder WHERE Complete = 0 ORDER BY Operation ASC
August 31, 2011 at 11:25 am
ZZartin (8/31/2011)
What aboutSELECT TOP 1 * FROM #JobOrder WHERE Complete = 0 ORDER BY Operation ASC
This will work only if the #jobOrder table ever hold one single job at a time. This only pulls a single row, not one per job.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 31, 2011 at 11:41 am
Or this:
; WITH Distinct_Jobs AS
(
SELECT DISTINCT Job
FROM #JobOrder
)
SELECT CrsAppOutput.Job , CrsAppOutput.OperName , CrsAppOutput.Operation , CrsAppOutput.Complete
FROM Distinct_Jobs OuterTable
CROSS APPLY
(
SELECT TOP 1 *
FROM #JobOrder InnerTable
WHERE OuterTable.Job = InnerTable.Job
AND InnerTable.Complete = 0
ORDER BY InnerTable.Operation ASC
) CrsAppOutput
I beleive the Row_Number will perform better as it is just a single pass on the entire table.
August 31, 2011 at 11:42 am
Matt Miller (#4) (8/31/2011)
ZZartin (8/31/2011)
What aboutSELECT TOP 1 * FROM #JobOrder WHERE Complete = 0 ORDER BY Operation ASC
This will work only if the #jobOrder table ever hold one single job at a time. This only pulls a single row, not one per job.
Ah yes good point, I was too focused on the sample data.
August 31, 2011 at 11:42 am
And Mr ( or Ms ) sdownen05 , thanks for setting up the problem fantastically 🙂 Right on the money ; appreciated 😎
August 31, 2011 at 12:17 pm
I'm glad I could help out. Thank you all very much for your solutions. As always, it is most appreciated.
Steve
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply