November 4, 2014 at 8:26 am
Hi all,
I am hoping to produce some data based on throughputs, yields etc and I have found the best way for me to produce this data is to use two select queries. I am struggling to join one on to the other and wondered if someone would kindly take a look and assist me with this? I have left the queries below. I can confirm the wo.id in the first query is a match for st.worksorderid but when i try to join based on that, i end up with '''Msg 102, Level 15, State 1, Line 19
Incorrect syntax near 'worksorderid'.'''
I have removed the order by function because and tried several things but i am stumped on this one.
SELECT wo.id as 'WORKSORDER', wo.partid as 'PART', ro.machine as 'PROCESS',
bom.bomqty as 'RATIO', wo.actualstartdate as 'STARTDATE', wo.actualfinishdate as 'ENDDATE',
DATEDIFF(MINUTE, actualstartdate, actualfinishdate) / 60.0 as 'TOTAL RUNTIME',
wo.actualbookedtime as 'NET RUNTIME', wo.quantitycompleted as 'OUTPUT',
wo.status as 'STATUS' from worksorders as wo
left join routing as ro on wo.partid = ro.part
inner join bom on wo.partid = bom.bomparent
and wo.bommethod = bom.baltmethod
and bompos like '%10'
and wo.status = 'COMPLETE'
and ro.machine not like 'BM%'
and ro.machine not like 'MRI%'
order by wo.id desc
/*trying to join this below onto the above*/
select st.worksorderid, sum(st.quantity) as 'INPUT'
from stocktransactions as st
where st.type not like 'REP'
and st.source = 'MIS'
group by st.worksorderid
order by st.worksorderid desc
Thanks in advance.
November 4, 2014 at 8:43 am
I can't really see what you're trying to do. Maybe try this:
SELECT
wo.id as 'WORKSORDER',
wo.partid as 'PART',
ro.machine as 'PROCESS',
bom.bomqty as 'RATIO',
wo.actualstartdate as 'STARTDATE',
wo.actualfinishdate as 'ENDDATE',
DATEDIFF(MINUTE,actualstartdate,actualfinishdate) / 60.0 as 'TOTAL RUNTIME',
wo.actualbookedtime as 'NET RUNTIME',
wo.quantitycompleted as 'OUTPUT',
wo.status as 'STATUS',
(
SELECT sum(st.quantity)
FROM stocktransactions as st
WHERE st.type not like 'REP' and st.source = 'MIS'
and st.worksorderid = wo.id
) AS 'INPUT'
FROM
worksorders as wo
LEFT JOIN
routing as ro on wo.partid = ro.part
JOIN
bom on wo.partid = bom.bomparent
AND
wo.bommethod = bom.baltmethod
AND
bompos like '%10' and wo.status = 'COMPLETE' and ro.machine not like 'BM%' and ro.machine not like 'MRI%'
ORDER BY
wo.id desc
November 4, 2014 at 9:41 am
cheers that worked a treat 🙂
November 5, 2014 at 5:32 am
I don't like the join of BOM
you are filtering the join based on properties of RO - these feel to me like they should be in the WHERE clause.
If you were doing a LEFT JOIN to BOM then the join filter would make more sense
November 5, 2014 at 5:52 am
aaron.reese (11/5/2014)
I don't like the join of BOMyou are filtering the join based on properties of RO - these feel to me like they should be in the WHERE clause.
If you were doing a LEFT JOIN to BOM then the join filter would make more sense
Without testing, this will turn the left join to ro into an inner join. If the query generates correct output then the left join should be written as an inner join to make the intention clear.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 5, 2014 at 3:36 pm
Thanks everyone. Having tried the inner join in place of the left I still found my desired output. Thank you very much. 🙂
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply