Stack Overflow Asked by Kevin D. on November 17, 2021
Consider the tables:
WorkOrder Date
101 1/2/2020
101 1/2/2020
102 1/2/2020
102 1/3/2020
101 1/3/2020
103 1/4/2020
104 1/4/2020
104 1/5/2020
103 1/5/2020
104 1/5/2020
102 1/5/2020
WorkOrder Operation
101 Process
101 Run
102 Process
102 Run
101 Ship
103 Process
104 Process
104 Run
103 Run
104 Ship
102 Ship
If I were to run query1:
SELECT *
FROM table1
JOIN table2 on table1.WorkOrder = table2.WorkOrder
WHERE Date = '1/4/2020'
I want a query that would return all of the rows for open work orders in that date range (i.e. include the process, run, and ship). This would be the equivalent to what I want returned:
SELECT *
FROM table
JOIN table2 on table1.WorkOrder = table2.WorkOrder
WHERE WorkOrder = '102' AND Work Order = '103' AND Work Order = '104'
Therefore the desired output would be:
102 Process 1/2/2020
102 Run 1/3/2020
103 Process 1/4/2020
104 Process 1/4/2020
104 Run 1/5/2020
103 Ship 1/5/2020
104 Ship 1/5/2020
102 Ship 1/5/2020
But I don’t want to specify each of the work orders returned from query1. Also, how could this work with a date range:
SELECT *
FROM table
JOIN table2 on table1.WorkOrder = table2.WorkOrder
WHERE Date <= 'X' AND Date >= 'Y'
I am not sure if this is something you are looking for..
I am assuming 'Process' is considered as Open order. Below query is not tested by the way:
Select a.WorkOrder, a.Date, a.Operation yourtable a WHERE a.Operation IN
(Select Operation from yourtable where Date ='1/4/2020') ;
Answered by The AG on November 17, 2021
If you want all the rows for work orders that:
You can do:
select *
from t
where workorder in (
select a.workorder
from t a
join t b on b.workorder = a.workorder
join t c on c.workorder = a.workorder
where a.operation = 'Process'
and b.operation = 'Run'
and c.operation = 'Ship'
and a.date <= '2020-01-04'
and c.date >= '2020-01-04'
) x
Answered by The Impaler on November 17, 2021
(if you are on ORACLE database) You can use WITH clause to define a temporal table
WITH query1 as -- query1 is the name of this temporal table
(
SELECT *
FROM table
WHERE Date = '1/4/2020'
)
SELECT *
FROM query1 -- you can do querys in query1 (the temporal table)
WHERE WorkOrder = '102'
AND Work Order = '103'
AND Work Order = '104'
Answered by Jose Jimenez on November 17, 2021
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP