Hi there,
I'm writing a query for my work so that I can pull the time it takes for us to setup and bend a specific job. However, when I use the following query I get total hours for all jobs. Any ideas on where I'm going wrong? Thanks.
SELECT O.CustCode
,O.PONum
,ODet.ItemNo
,ODet.JobNo
,ODet.PartNo
,ODet.QtyToMake-ODet.QtyShipped2Cust as QtyOpen
,CONVERT(VARCHAR(10),ODet.EstimStartDate,110) as StartDate
,CONVERT(VARCHAR(10),ODet.DueDate,110) as DueDate
,Datediff(day,sysdatetime(),ODet.DueDate) as DaysDue
--,CAST(ROUND(Rout.CycleTime,2,0) AS NUMERIC(10,2)) AS CycleTime
--,Rout.CycleUnit
--,CAST(ROUND((Rout.CycleTime*ODet.QtyToMake-ODet.QtyShipped2Cust+SetupTime)/60,2,0) AS NUMERIC(10,2)) AS TimeRemHR
,Estim.AltPartNo
,Estim.User_text1 as Dedicated
,Odet.User_text1 as Planned
--,CAST(ROUND(Rout.CycleTime,10,2) AS NUMERIC(10,2))*ODet.QtyToMake-ODet.QtyShipped2Cust+CAST(ROUND(Rout.SetupTime,2,0) AS numeric(10,2)) AS TimeRemMin
,ODet.CurrentWorkCntr
,CAST((Odet.JobNotes) AS TEXT)
,ODet.JobOnHold
FROM CASBE.DBO.Routing Rout
inner join Casbe.dbo.OrderDet ODet with (nolock) ON ODet.PartNo = Rout.PartNo
inner join Casbe.dbo.Estim Estim with (nolock) ON ODet.PartNo = Estim.PartNo
inner join Casbe.dbo.Orders O with (nolock) ON Odet.OrderNo = O.OrderNo
left join (SELECT
[OrderRouting.jobNo]
,(select SUM(OrderRouting.TotHrsLeft) AS totalcncbendHours From Casbe.dbo.OrderRouting WHERE OrderRouting.OperCode = 'CNC Bending') AS totalcncbendHours
,(select SUM(OrderRouting.TotHrsLeft) AS totalsetupHours From Casbe.dbo.OrderRouting WHERE OrderRouting.OperCode = 'Setup tube') AS totalsetupHours) as TotalHrs inner join Casbe.dbo.OrderRouting ON Odet.JobNo = OrderRounting.JobNo
WHERE Rout.OperCode = 'CNC BENDING'
and Odet.Status = 'Open'
and ODet.Duedate >= 2000/01/01 and ODet.DueDate < (DATEADD(DAY,7,SYSDATETIME()))
--and [O].[CustCode] = 'SpaceX'
and ODet.ProdCode = 'Tube Assy'
and (Odet.CurrentWorkCntr in ('jobreview','stage', 'setup') or Odet.CurrentWorkCntr is Null)
--and Estim.AltPartNo like '%.500 X .028 ss%'
--10910
--and O.PONum = '650812'
--and ODet.JobNo = '10874-01'
and Odet.JobOnHold = 'N'
ORDER BY Odet.EstimStartDate ASC
,Estim.AltPartNo ASC
,Odet.User_Text1
,ODet.DueDate