SQL Query with a Loop
-
I am trying to set up a SQL query and I have a select statement that I need to setup a loop in it. It looks like this: select jods.Company , case when jos.SetupComplete = 0 then -- Here is where I need to loop through the rows to get a calculation totaled up with the below equation (((jods.SetupLabRate * jods.CrewSize) * jos.EstSetHours) * (1 - (jos.SetupPctComplete/100))) else 0 end as SetupLabCost any thoughts on this?
-
I am trying to set up a SQL query and I have a select statement that I need to setup a loop in it. It looks like this: select jods.Company , case when jos.SetupComplete = 0 then -- Here is where I need to loop through the rows to get a calculation totaled up with the below equation (((jods.SetupLabRate * jods.CrewSize) * jos.EstSetHours) * (1 - (jos.SetupPctComplete/100))) else 0 end as SetupLabCost any thoughts on this?
You have to show us the entire query. It appears as if you've setup a
join, but we can't see it. Typically, if you need a loop in ajoin, it's implemented as a sub-query, something like this:select...
from mytable as a
join (select col1, col2, col3 from mytable) AS b on b.col = a.col
...I can't be more specific based on your original question.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
I am trying to set up a SQL query and I have a select statement that I need to setup a loop in it. It looks like this: select jods.Company , case when jos.SetupComplete = 0 then -- Here is where I need to loop through the rows to get a calculation totaled up with the below equation (((jods.SetupLabRate * jods.CrewSize) * jos.EstSetHours) * (1 - (jos.SetupPctComplete/100))) else 0 end as SetupLabCost any thoughts on this?
I also tried something like this and still not working: , case when jos.SetupComplete = 0 then --for ($i=0; $i<$count_row;$i++) --{ -- (((jods.SetupLabRate * jods.CrewSize) * jos.EstSetHours) * (1 - (jos.SetupPctComplete/100))) --} else 0 end as SetupLabCost2
-
I also tried something like this and still not working: , case when jos.SetupComplete = 0 then --for ($i=0; $i<$count_row;$i++) --{ -- (((jods.SetupLabRate * jods.CrewSize) * jos.EstSetHours) * (1 - (jos.SetupPctComplete/100))) --} else 0 end as SetupLabCost2
Again, without the associated table/view schemas, along with the entire query, we can't really help you, short of speculating as to what *might* be the proper approach.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
Again, without the associated table/view schemas, along with the entire query, we can't really help you, short of speculating as to what *might* be the proper approach.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013select jh.Company, jh.JobClosed, jh.JobComplete, jh.JobEngineered, jh.JobReleased , jh.JobNum, jh.PartNum, jh.RevisionNum, jh.PartDescription, jh.ProdQty, jh.IUM , jh.StartDate as JobStartDate, jh.DueDate as JobDueDate, jh.ReqDueDate, jh.ProdCode, pg.Description as ProdDesc , jh.JobFirm , jh.Plant, pl.Name as Site , jo.StartDate as OpStartDate, jo.DueDate as OpDueDate, jo.OpCode , jo.OprSeq, jo.OpDesc , jo.RunQty, jo.QtyCompleted , SetupCost.SetupLabRate, SetupCost.SetupBurRate, ProdCost.ProdLabRate, ProdCost.ProdBurRate , pp.MfgLotSize, jo.SetupComplete, jo.SetUpCrewSize, jo.EstSetHours, SetupCost.OpenSetupHrs, jo.SetupPctComplete, jo.ProdCrewSize, jo.EstProdHours, jo.ProdStandard , SetupCost.SetupLabCost , SetupCost.SetupBurCost , jo.StdFormat from JobHead jh inner join ProdGrup pg on pg.Company = jh.Company and pg.ProdCode = jh.ProdCode inner join Erp.PartPlant pp on pp.Company = jh.Company and pp.Plant = jh.Plant and pp.PartNum = jh.PartNum inner join Erp.Plant pl on pl.Company = pp.Company and pl.Plant = pp.Plant inner join JobOper jo on jo.Company = jh.Company and jo.JobNum = jh.JobNum left outer join (select jods.Company, jods.JobNum, jods.OprSeq, jods.AssemblySeq, pps.MfgLotSize, Sum(jods.SetupLabRate) as SetupLabRate, Sum(jods.SetupBurRate) as SetupBurRate , case when jos.SetupComplete = 0 then (Sum(jods.SetupLabRate) * jos.EstSetHours ) * (1 - (jos.SetupPctComplete / 100)) else 0 end as SetupLabCost --This is the area needing the loop below*********************************** , case when jos.SetupComplete = 0 then for ($i=0; $i<$count_row;$i++) { (((jods.SetupLabRate * jods.CrewSize) * jos.EstSetHours) * (1 - (jos.SetupPctComplete/100))) } else 0 end as SetupLabCost2 from Erp.JobOpDtl jods inner join JobHead jhs on jhs.Company = jods.Company and jhs.JobNum = jods.JobNum inner join JobOper jos on jos.Company = jods.Company and jos.JobNum = jods.JobNum and jos.AssemblySeq = jods.AssemblySeq and jos.OprSeq = jods.OprSeq left outer join Erp.PartPlant pps on pps.Company = jhs.Company and pps.PartNum = jhs.PartNum and pps.Plant = jhs.Plant where jods.SetupOrProd <> 'P' group by jods.Company, jods.JobNum, jods.AssemblySeq, jods.OprSeq, pps.MfgLotSize, jos.EstSetHours, jos.SetupComplete, jos.SetupPctComplete ) as SetupCost on SetupCost.Company = jo.Company and SetupCost.JobNum = jo.Job
-
I am trying to set up a SQL query and I have a select statement that I need to setup a loop in it. It looks like this: select jods.Company , case when jos.SetupComplete = 0 then -- Here is where I need to loop through the rows to get a calculation totaled up with the below equation (((jods.SetupLabRate * jods.CrewSize) * jos.EstSetHours) * (1 - (jos.SetupPctComplete/100))) else 0 end as SetupLabCost any thoughts on this?
You can not do loops in SQL. I have a feeling that what you need is as simple as:
SUM(CASE
WHEN jos.SetupComplete = 0
THEN (((jods.SetupLabRate * jods.CrewSize) * jos.EstSetHours) * (1 - (jos.SetupPctComplete / 100)))
ELSE 0
END) AS SetupLabCostOtherwise you need to tell us what you're trying to achieve
Wrong is evil and must be defeated. - Jeff Ello
