Click here to Skip to main content
14,427,400 members
   

Database

 
PinnedHOW TO ANSWER A QUESTION PinPopular
Chris Maunder16-Jul-09 4:10
cofounderChris Maunder16-Jul-09 4:10 
PinnedHow to get an answer to your question PinPopular
Chris Maunder10-Nov-05 17:30
cofounderChris Maunder10-Nov-05 17:30 
QuestionOracle SQL query to MS-SQL Pin
Member 951930620-Jan-20 23:37
MemberMember 951930620-Jan-20 23:37 
AnswerRe: Oracle SQL query to MS-SQL Pin
Richard Deeming21-Jan-20 2:43
communityengineerRichard Deeming21-Jan-20 2:43 
Questionsplit time then group by 15 min mongodb Pin
Member 1300557210-Jan-20 22:56
MemberMember 1300557210-Jan-20 22:56 
QuestionMessage Closed Pin
8-Jan-20 0:12
Memberqweitmen8-Jan-20 0:12 
QuestionSQL Query with a Loop Pin
Bobby Underwood6-Jan-20 5:58
MemberBobby Underwood6-Jan-20 5:58 
AnswerRe: SQL Query with a Loop Pin
#realJSOP6-Jan-20 6:04
mva#realJSOP6-Jan-20 6:04 
AnswerRe: SQL Query with a Loop Pin
Bobby Underwood6-Jan-20 6:11
MemberBobby Underwood6-Jan-20 6:11 
AnswerRe: SQL Query with a Loop Pin
#realJSOP6-Jan-20 6:16
mva#realJSOP6-Jan-20 6:16 
GeneralRe: SQL Query with a Loop Pin
Bobby Underwood6-Jan-20 6:22
MemberBobby Underwood6-Jan-20 6:22 
select 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.JobNum and SetupCost.OprSeq = jo.OprSeq and SetupCost.AssemblySeq = jo.AssemblySeq

where jh.JobEngineered = 1 and jh.JobComplete = 0 and jo.OpComplete = 0
AnswerRe: SQL Query with a Loop Pin
Jörgen Andersson7-Jan-20 6:52
communityengineerJörgen Andersson7-Jan-20 6:52 
QuestionMessage Closed Pin
2-Jan-20 1:00
Memberaskinranks2-Jan-20 1:00 
QuestionMessage Closed Pin
2-Jan-20 0:59
Memberaskinranks2-Jan-20 0:59 
Questionincorrect syntax near begin expecting external for Alter Procedure Pin
simpledeveloper27-Dec-19 10:23
Membersimpledeveloper27-Dec-19 10:23 
AnswerRe: incorrect syntax near begin expecting external for Alter Procedure Pin
Jörgen Andersson28-Dec-19 0:14
communityengineerJörgen Andersson28-Dec-19 0:14 
QuestionAdding multiple columns with condition Pin
simpledeveloper26-Dec-19 15:58
Membersimpledeveloper26-Dec-19 15:58 
AnswerRe: Adding multiple columns with condition Pin
Mycroft Holmes26-Dec-19 20:58
MemberMycroft Holmes26-Dec-19 20:58 
AnswerRe: Adding multiple columns with condition Pin
Jörgen Andersson27-Dec-19 4:40
communityengineerJörgen Andersson27-Dec-19 4:40 
GeneralRe: Adding multiple columns with condition Pin
simpledeveloper27-Dec-19 6:29
Membersimpledeveloper27-Dec-19 6:29 
GeneralRe: Adding multiple columns with condition Pin
Jörgen Andersson27-Dec-19 6:32
communityengineerJörgen Andersson27-Dec-19 6:32 
GeneralRe: Adding multiple columns with condition Pin
simpledeveloper27-Dec-19 9:44
Membersimpledeveloper27-Dec-19 9:44 
QuestionMessage Closed Pin
25-Dec-19 5:46
MemberMember 1469959425-Dec-19 5:46 
QuestionMessage Closed Pin
25-Dec-19 5:46
MemberMember 1469959425-Dec-19 5:46 
QuestionCrystal Report to SQL server 2012 database Pin
Member 1447460719-Dec-19 11:39
MemberMember 1447460719-Dec-19 11:39 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.