SQLTeam.com | Weblogs | Forums

Help with Job Time Query


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.

,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
--,CAST(ROUND((Rout.CycleTime*ODet.QtyToMake-ODet.QtyShipped2Cust+SetupTime)/60,2,0) AS NUMERIC(10,2)) AS TimeRemHR
,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
,CAST((Odet.JobNotes) AS TEXT)

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
,(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

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%'
--and O.PONum = '650812'
--and ODet.JobNo = '10874-01'
and Odet.JobOnHold = 'N'

ORDER BY Odet.EstimStartDate ASC
,Estim.AltPartNo ASC


I would suggest braking it down to smaller sections and finding the problem.