Skip to content

Instantly share code, notes, and snippets.

/gist:0ffa317b3243a8aa39d1e7eabf72a2bb

Created October 23, 2017 18:55
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save anonymous/0ffa317b3243a8aa39d1e7eabf72a2bb to your computer and use it in GitHub Desktop.
Save anonymous/0ffa317b3243a8aa39d1e7eabf72a2bb to your computer and use it in GitHub Desktop.
Download ZIP
Union Gone Wrong
Raw
gistfile1.txt
with cte as
(
SELECT
sub.StepNo,
sub.PartNo,
sub.WorkCntr,
sub.WorkCntr as owork,
sub.WhereUsed as WhereUsed,
sub.JobNo,
sub.TicketDate,
(sub.CycleTime + sub.SetupTime) * sub.ActualPayRate as ttLaborCost,
(sub.CycleTime + sub.SetupTime) * sub.BurdenRate as mimicBurdenCost,
sub.SetupTime as ttSetupTime,
sub.CycleTime as ttCycleTime,
sub.PiecesFinished,
sub.PiecesScrapped,
sub.ActualPayRate,
sub.BurdenRate,
sub.SetupTime,
sub.TimeUnit,
sub.CycleTime,
sub.CycleUnit,
sub.MachRun as RoutMachRun,
sub.MachRun,
sub.UnattendOp,
sub.DueDate,
sub.QtyToMake,
(sub.QtyToMake - sub.QtyShipped2Stock) as NumOpen,
CASE
WHEN sub.MachRun <> sub.MachRun Then 'Incorrect Ratio'
ELSE ''
END as IncorrectRatio,
ROUND(cast (sub.PctEff as FLOAT),2) as pctEff,
ROUND(cast (sub.ScrapPct as FLOAT),2) as scrapEff,
(ROUND(cast((sub.QtyToMake - sub.QtyShipped2Stock) as FLOAT)/sub.QtyToMake,3)) as Prorate,
sub.POTotalCost,
ROW_NUMBER() OVER(PARTITION BY sub.Stepno, sub.JobNo ORDER BY sub.StepNo, sub.TicketDate) as RowNum,
ROW_NUMBER() OVER(PARTITION BY sub.JobNo ORDER BY sub.JobNo) as RowNum2
FROM (
SELECT
tt.StepNo,
o.PartNo,
tt.WorkCntr,
o.WorkCntr as owork,
matl.WhereUsed as WhereUsed,
tt.JobNo,
tt.TicketDate,
(tt.CycleTime + tt.SetupTime) * tt.ActualPayRate as ttLaborCost,
(tt.CycleTime + tt.SetupTime) * tt.BurdenRate as mimicBurdenCost,
tt.SetupTime as ttSetupTime,
tt.CycleTime as ttCycleTime,
tt.PiecesFinished,
tt.PiecesScrapped,
tt.ActualPayRate,
tt.BurdenRate,
o.SetupTime,
o.TimeUnit,
o.CycleTime,
o.CycleUnit,
o.MachRun as RoutMachRun,
tt.MachRun,
o.UnattendOp,
od.DueDate,
od.QtyToMake,
od.QtyShipped2Stock,
o.ScrapPct,
(od.QtyToMake - od.QtyShipped2Stock) as NumOpen,
CASE
WHEN o.MachRun <> tt.MachRun Then 'Incorrect Ratio'
ELSE ''
END as IncorrectRatio,
ROUND(cast (o.PctEff as FLOAT),2) as pctEff,
ROUND(cast (o.ScrapPct as FLOAT),2) as scrapEff,
(ROUND(cast((od.QtyToMake - od.QtyShipped2Stock) as FLOAT)/od.QtyToMake,3)) as Prorate,
POTotalCost.POTotalCost,
ROW_NUMBER() OVER(PARTITION BY tt.Stepno, tt.JobNo ORDER BY tt.StepNo, tt.TicketDate) as RowNum,
ROW_NUMBER() OVER(PARTITION BY tt.JobNo ORDER BY tt.JobNo) as RowNum2
FROM
TimeTicketDet as tt
LEFT JOIN
OrderRouting as o
on tt.JobNo = o.JobNo and tt.StepNo = o.Stepno
LEFT JOIN
OrderDet as od
on tt.JobNo = od.JobNo and o.PartNo = od.PartNo
LEFT JOIN
(
SELECT DISTINCT
matl1.SubPartNo,
STUFF((
SELECT ', ' + matl2.PartNo
FROM Materials as matl2
WHERE matl2.SubPartNo = matl1.SubPartNo
FOR XML PATH('')
),1,1,'') as WhereUsed
FROM
Materials as matl1
) matl
ON matl.SubPartNo = o.PartNo
LEFT JOIN
(
SELECT
poTotalCost.JobNo,
SUM(poTotalCost.TotalCost) as poTotalCost
FROM
(
SELECT
JobNo,
CASE
WHEN StockUnit = 'LOT' Then SUM(StockingCost*1)
ELSE SUM(StockingCost * QtyPosted1)
END as TotalCost
FROM JobMaterials
GROUP BY JobNo,StockUnit
) poTotalCost
GROUP BY poTotalCost.JobNo
) POTotalCost
ON POTotalCost.JobNo = tt.JobNo
UNION ALL
SELECT
tt.StepNo,
o.PartNo,
tt.WorkCntr,
o.WorkCntr as owork,
matl.WhereUsed as WhereUsed,
tt.JobNo,
tt.TicketDate,
(tt.CycleTime + tt.SetupTime) * tt.ActualPayRate as ttLaborCost,
(tt.CycleTime + tt.SetupTime) * tt.BurdenRate as mimicBurdenCost,
tt.SetupTime as ttSetupTime,
tt.CycleTime as ttCycleTime,
tt.PiecesFinished,
tt.PiecesScrapped,
tt.ActualPayRate,
tt.BurdenRate,
o.SetupTime,
o.TimeUnit,
o.CycleTime,
o.CycleUnit,
o.MachRun as RoutMachRun,
tt.MachRun,
o.UnattendOp,
od.DueDate,
od.QtyToMake,
od.QtyShipped2Stock,
o.ScrapPct,
(od.QtyToMake - od.QtyShipped2Stock) as NumOpen,
CASE
WHEN o.MachRun <> tt.MachRun Then 'Incorrect Ratio'
ELSE ''
END as IncorrectRatio,
ROUND(cast (o.PctEff as FLOAT),2) as pctEff,
ROUND(cast (o.ScrapPct as FLOAT),2) as scrapEff,
(ROUND(cast((od.QtyToMake - od.QtyShipped2Stock) as FLOAT)/od.QtyToMake,3)) as Prorate,
POTotalCost.POTotalCost,
ROW_NUMBER() OVER(PARTITION BY tt.Stepno, tt.JobNo ORDER BY tt.StepNo, tt.TicketDate) as RowNum,
ROW_NUMBER() OVER(PARTITION BY tt.JobNo ORDER BY tt.JobNo) as RowNum2
FROM
TimeTicketDet as tt
LEFT JOIN
OrderRouting as o
on tt.JobNo = o.JobNo and tt.StepNo = o.Stepno
LEFT JOIN
OrderDet as od
on tt.JobNo = od.JobNo and o.PartNo = od.PartNo
LEFT JOIN
(
SELECT DISTINCT
matl1.SubPartNo,
STUFF((
SELECT ', ' + matl2.PartNo
FROM Materials as matl2
WHERE matl2.SubPartNo = matl1.SubPartNo
FOR XML PATH('')
),1,1,'') as WhereUsed
FROM
Materials as matl1
) matl
ON matl.SubPartNo = o.PartNo
LEFT JOIN
(
SELECT
poTotalCost.JobNo,
SUM(poTotalCost.TotalCost) as poTotalCost
FROM
(
SELECT
JobNo,
CASE
WHEN StockUnit = 'LOT' Then SUM(StockingCost*1)
ELSE SUM(StockingCost * QtyPosted1)
END as TotalCost
FROM JobMaterials
GROUP BY JobNo,StockUnit
) poTotalCost
GROUP BY poTotalCost.JobNo
) POTotalCost
ON POTotalCost.JobNo = tt.JobNo
) as sub
)
SELECT *,
--PO Total Cost
CASE
WHEN RowNum2 = 1 Then poTotalCost
ELSE 0
END as actualPOTotalCost,
--TT LaborCost with Split (WIP Mimic)
CASE
WHEN MachRun = 2 AND ttSetupTime > 0 AND UnattendOp = 'N' Then ((ttLaborCost-(ttSetupTime*ActualPayRate))/2)+ttSetupTime*ActualPayrate
WHEN MachRun = 2 AND UnattendOp = 'N' Then ttLaborCost/2
WHEN UnattendOp = 'Y' Then ttLaborCost - (ttCycleTime*ActualPayRate)
ELSE ttLaborCost
END as mimicLaborCost,
--************************************************************routMachRun***********************************************************************
--************************************************************routMachRun***********************************************************************
--************************************************************routMachRun***********************************************************************
--************************************************************routMachRun***********************************************************************
--************************************************************routMachRun***********************************************************************
--************************************************************routMachRun***********************************************************************
--************************************************************routMachRun***********************************************************************
--************************************************************routMachRun***********************************************************************
--COMBINED Hours w/ pctEff
--With setup
CASE
WHEN CycleUnit = 'S' AND TimeUnit = 'M' AND RowNum = 1 AND RoutMachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)+(SetupTime/60))/(pctEff/100))
WHEN CycleUnit = 'S' AND TimeUnit = 'M' AND RowNum = 1 AND RoutMachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)+(SetupTime/60))/(pctEff/100))
WHEN CycleUnit = 'S' AND TimeUnit = 'H' AND RowNum = 1 AND RoutMachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)+(SetupTime))/(pctEff/100))
WHEN CycleUnit = 'S' AND TimeUnit = 'H' AND RowNum = 1 AND RoutMachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)+(SetupTime))/(pctEff/100))
WHEN CycleUnit = 'H' AND TimeUnit = 'H' AND RowNum = 1 AND RoutMachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped))/(pctEff/100)) + SetupTime)
WHEN CycleUnit = 'H' AND TimeUnit = 'H' AND RowNum = 1 AND RoutMachRun = 2 Then (((((CycleTime)*(PiecesFinished+PiecesScrapped))/(pctEff/100))) + SetupTime)
WHEN TimeUnit = 'H' AND RowNum = 1 AND RoutMachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/60)/(pctEff/100)) + SetupTime)
WHEN TimeUnit = 'H' AND RowNum = 1 AND RoutMachRun = 2 Then (((((CycleTime)*(PiecesFinished+PiecesScrapped)/60)/(pctEff/100))) + SetupTime)
WHEN CycleUnit = 'H' AND RowNum = 1 AND RoutMachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped))/(pctEff/100)) + (SetupTime/60))
WHEN CycleUnit = 'H' AND RowNum = 1 AND RoutMachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped))/(pctEff/100)) + (SetupTime/60))
WHEN RowNum = 1 AND RoutMachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/60)+(SetupTime/60))/(pctEff/100))
WHEN RowNum = 1 AND RoutMachRun = 2 Then (((((CycleTime)*(PiecesFinished+PiecesScrapped)/60)+(SetupTime/60))/(pctEff/100)))
--Without setup
WHEN CycleUnit = 'S' AND TimeUnit = 'M' AND RowNum > 1 AND RoutMachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600))/(pctEff/100))
WHEN CycleUnit = 'S' AND TimeUnit = 'M' AND RowNum > 1 AND RoutMachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600))/(pctEff/100))
WHEN CycleUnit = 'S' AND TimeUnit = 'H' AND RowNum > 1 AND RoutMachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600))/(pctEff/100))
WHEN CycleUnit = 'S' AND TimeUnit = 'H' AND RowNum > 1 AND RoutMachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600))/(pctEff/100))
WHEN CycleUnit = 'H' AND TimeUnit = 'H' And RowNum > 1 AND RoutMachRun = 1 Then (((CycleTime)*(PiecesFinished+PiecesScrapped)))/(pctEff/100)
WHEN CycleUnit = 'H' AND TimeUnit = 'H' And RowNum > 1 AND RoutMachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)))/(pctEff/100))
WHEN TimeUnit = 'H' AND RowNum > 1 AND RoutMachRun = 1 Then (((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100)
WHEN TimeUnit = 'H' AND RowNum > 1 AND RoutMachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100))
WHEN CycleUnit = 'H' AND RowNum > 1 AND RoutMachRun = 1 Then (((CycleTime)*(PiecesFinished+PiecesScrapped)))/(pctEff/100)
WHEN CycleUnit = 'H' AND RowNum > 1 AND RoutMachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)))/(pctEff/100))
WHEN CycleUnit = 'M' AND TimeUnit = 'M' AND RowNum > 1 AND RoutMachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped))/60)/(pctEff/100))
WHEN CycleUnit = 'M' AND TimeUnit = 'M' AND RowNum > 1 AND RoutMachRun = 2 Then (((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100)
WHEN RowNum > 1 AND RoutMachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100))
WHEN RowNum > 1 AND RoutMachRun = 2 Then (((((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100)))
END as estLaborHoursROUT,
--COMBINED Hours w/ pctEff & Split
--With setup
CASE
WHEN CycleUnit = 'S' AND TimeUnit = 'M' AND RowNum = 1 AND RoutMachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)+(SetupTime/60))/(pctEff/100))
WHEN CycleUnit = 'S' AND TimeUnit = 'M' AND RowNum = 1 AND RoutMachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)/2+(SetupTime/60))/(pctEff/100))
WHEN CycleUnit = 'S' AND TimeUnit = 'H' AND RowNum = 1 AND RoutMachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)+(SetupTime))/(pctEff/100))
WHEN CycleUnit = 'S' AND TimeUnit = 'H' AND RowNum = 1 AND RoutMachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)/2+(SetupTime))/(pctEff/100))
WHEN CycleUnit = 'H' AND TimeUnit = 'H' AND RowNum = 1 AND RoutMachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped))/(pctEff/100)) + SetupTime)
WHEN CycleUnit = 'H' AND TimeUnit = 'H' AND RowNum = 1 AND RoutMachRun = 2 Then (((((CycleTime)*(PiecesFinished+PiecesScrapped))/(pctEff/100)))/2 + SetupTime)
WHEN TimeUnit = 'H' AND RowNum = 1 AND RoutMachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/60)/(pctEff/100)) + SetupTime)
WHEN TimeUnit = 'H' AND RowNum = 1 AND RoutMachRun = 2 Then (((((CycleTime)*(PiecesFinished+PiecesScrapped)/60)/(pctEff/100)))/2 + SetupTime)
WHEN CycleUnit = 'H' AND RowNum = 1 AND RoutMachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped))/(pctEff/100)) + (SetupTime/60))
WHEN CycleUnit = 'H' AND RowNum = 1 AND RoutMachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped))/(pctEff/100))/2 + (SetupTime/60))
WHEN RowNum = 1 AND RoutMachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/60)+(SetupTime/60))/(pctEff/100))
WHEN RowNum = 1 AND RoutMachRun = 2 Then (((((CycleTime)*(PiecesFinished+PiecesScrapped)/60)/2+(SetupTime/60))/(pctEff/100)))
--Without setup
WHEN CycleUnit = 'S' AND TimeUnit = 'M' AND RowNum > 1 AND RoutMachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600))/(pctEff/100))
WHEN CycleUnit = 'S' AND TimeUnit = 'M' AND RowNum > 1 AND RoutMachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)/2)/(pctEff/100))
WHEN CycleUnit = 'S' AND TimeUnit = 'H' AND RowNum > 1 AND RoutMachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600))/(pctEff/100))
WHEN CycleUnit = 'S' AND TimeUnit = 'H' AND RowNum > 1 AND RoutMachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)/2)/(pctEff/100))
WHEN CycleUnit = 'H' AND TimeUnit = 'H' And RowNum > 1 AND RoutMachRun = 1 Then (((CycleTime)*(PiecesFinished+PiecesScrapped)))/(pctEff/100)
WHEN CycleUnit = 'H' AND TimeUnit = 'H' And RowNum > 1 AND RoutMachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)))/(pctEff/100))/2
WHEN TimeUnit = 'H' AND RowNum > 1 AND RoutMachRun = 1 Then (((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100)
WHEN TimeUnit = 'H' AND RowNum > 1 AND RoutMachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100))/2
WHEN CycleUnit = 'H' AND RowNum > 1 AND RoutMachRun = 1 Then (((CycleTime)*(PiecesFinished+PiecesScrapped)))/(pctEff/100)
WHEN CycleUnit = 'H' AND RowNum > 1 AND RoutMachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)))/(pctEff/100))/2
WHEN CycleUnit = 'M' AND TimeUnit = 'M' AND RowNum > 1 AND RoutMachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped))/60)/(pctEff/100))
WHEN CycleUnit = 'M' AND TimeUnit = 'M' AND RowNum > 1 AND RoutMachRun = 2 Then (((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100)/2
WHEN RowNum > 1 AND RoutMachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100))
WHEN RowNum > 1 AND RoutMachRun = 2 Then (((((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100)))/2
END as splitLaborHoursROUT,
--COMBINED Labor using Hours w/ pctEff & Split
--With setup
CASE
WHEN CycleUnit = 'S' AND TimeUnit = 'M' AND RowNum = 1 AND RoutMachRun = 1 AND UnattendOp = 'N' Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)+(SetupTime/60))/(pctEff/100))*ActualPayRate
WHEN CycleUnit = 'S' AND TimeUnit = 'M' AND RowNum = 1 AND RoutMachRun = 2 AND UnattendOp = 'N' Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)/2+(SetupTime/60))/(pctEff/100))*ActualPayRate
WHEN CycleUnit = 'S' AND TimeUnit = 'H' AND RowNum = 1 AND RoutMachRun = 1 AND UnattendOp = 'N' Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)+(SetupTime))/(pctEff/100))*ActualPayRate
WHEN CycleUnit = 'S' AND TimeUnit = 'H' AND RowNum = 1 AND RoutMachRun = 2 AND UnattendOp = 'N'Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)/2+(SetupTime))/(pctEff/100))*ActualPayRate
WHEN CycleUnit = 'H' AND TimeUnit = 'H' AND RowNum = 1 AND RoutMachRun = 1 AND UnattendOp = 'N' Then ((((CycleTime)*(PiecesFinished+PiecesScrapped))/(pctEff/100)) + SetupTime)*ActualPayRate
WHEN CycleUnit = 'H' AND TimeUnit = 'H' AND RowNum = 1 AND RoutMachRun = 2 AND UnattendOp = 'N' Then (((((CycleTime)*(PiecesFinished+PiecesScrapped))/(pctEff/100)))/2 + SetupTime)*ActualPayRate
WHEN TimeUnit = 'H' AND RowNum = 1 AND RoutMachRun = 1 AND UnattendOp = 'N' Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/60)/(pctEff/100)) + SetupTime)*ActualPayRate
WHEN TimeUnit = 'H' AND RowNum = 1 AND RoutMachRun = 2 AND UnattendOp = 'N' Then (((((CycleTime)*(PiecesFinished+PiecesScrapped)/60)/(pctEff/100)))/2 + SetupTime)*ActualPayRate
WHEN CycleUnit = 'H' AND RowNum = 1 AND RoutMachRun = 1 AND UnattendOp = 'N' Then ((((CycleTime)*(PiecesFinished+PiecesScrapped))/(pctEff/100)) + (SetupTime/60))*ActualPayRate
WHEN CycleUnit = 'H' AND RowNum = 1 AND RoutMachRun = 2 AND UnattendOp = 'N' Then ((((CycleTime)*(PiecesFinished+PiecesScrapped))/(pctEff/100))/2 + (SetupTime/60))*ActualPayRate
WHEN RowNum = 1 AND RoutMachRun = 1 AND UnattendOp = 'N' Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/60)+(SetupTime/60))/(pctEff/100))*ActualPayRate
WHEN RowNum = 1 AND RoutMachRun = 2 AND UnattendOp = 'N' Then (((((CycleTime)*(PiecesFinished+PiecesScrapped)/60)/2+(SetupTime/60))/(pctEff/100)))*ActualPayRate
WHEN TimeUnit = 'M' AND RowNum = 1 AND UnattendOp = 'Y' Then ((((SetupTime/60))/(pctEff/100)))*ActualPayRate
WHEN TimeUnit = 'H' AND RowNum = 1 AND UnattendOp = 'Y' Then ((((SetupTime))/(pctEff/100)))*ActualPayRate
--Without setup
WHEN CycleUnit = 'S' AND TimeUnit = 'M' AND RowNum > 1 AND RoutMachRun = 1 AND UnattendOp = 'N' Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600))/(pctEff/100))*ActualPayRate
WHEN CycleUnit = 'S' AND TimeUnit = 'M' AND RowNum > 1 AND RoutMachRun = 2 AND UnattendOp = 'N' Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)/2)/(pctEff/100))*ActualPayRate
WHEN CycleUnit = 'S' AND TimeUnit = 'H' AND RowNum > 1 AND RoutMachRun = 1 AND UnattendOp = 'N' Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600))/(pctEff/100))*ActualPayRate
WHEN CycleUnit = 'S' AND TimeUnit = 'H' AND RowNum > 1 AND RoutMachRun = 2 AND UnattendOp = 'N' Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)/2)/(pctEff/100))*ActualPayRate
WHEN CycleUnit = 'H' AND TimeUnit = 'H' And RowNum > 1 AND RoutMachRun = 1 AND UnattendOp = 'N' Then (((CycleTime)*(PiecesFinished+PiecesScrapped)))/(pctEff/100)*ActualPayRate
WHEN CycleUnit = 'H' AND TimeUnit = 'H' And RowNum > 1 AND RoutMachRun = 2 AND UnattendOp = 'N' Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)))/(pctEff/100))/2*ActualPayRate
WHEN TimeUnit = 'H' AND RowNum > 1 AND RoutMachRun = 1 AND UnattendOp = 'N' Then (((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100)*ActualPayRate
WHEN TimeUnit = 'H' AND RowNum > 1 AND RoutMachRun = 2 AND UnattendOp = 'N' Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100))/2*ActualPayRate
WHEN CycleUnit = 'H' AND RowNum > 1 AND RoutMachRun = 1 AND UnattendOp = 'N' Then (((CycleTime)*(PiecesFinished+PiecesScrapped)))/(pctEff/100)*ActualPayRate
WHEN CycleUnit = 'H' AND RowNum > 1 AND RoutMachRun = 2 AND UnattendOp = 'N' Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)))/(pctEff/100))/2*ActualPayRate
WHEN CycleUnit = 'M' AND TimeUnit = 'M' AND RowNum > 1 AND RoutMachRun = 1 AND UnattendOp = 'N' Then ((((CycleTime)*(PiecesFinished+PiecesScrapped))/60)/(pctEff/100))*ActualPayRate
WHEN CycleUnit = 'M' AND TimeUnit = 'M' AND RowNum > 1 AND RoutMachRun = 2 AND UnattendOp = 'N' Then (((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100)/2*ActualPayRate
WHEN RowNum > 1 AND RoutMachRun = 1 AND UnattendOp = 'N' Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100))*ActualPayRate
WHEN RowNum > 1 AND RoutMachRun = 2 AND UnattendOp = 'N' Then (((((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100)))/2*ActualPayRate
WHEN TimeUnit = 'M' AND RowNum > 1 AND UnattendOp = 'Y' Then 0
WHEN TimeUnit = 'H' AND RowNum > 1 AND UnattendOp = 'Y' Then 0
END as estLaborCostROUT,
--COMBINED Burden w/ pctEff
--With setup
CASE
WHEN CycleUnit = 'S' AND TimeUnit = 'M' AND RowNum = 1 AND RoutMachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)+(SetupTime/60))/(pctEff/100))*BurdenRate
WHEN CycleUnit = 'S' AND TimeUnit = 'M' AND RowNum = 1 AND RoutMachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)+(SetupTime/60))/(pctEff/100))*BurdenRate
WHEN CycleUnit = 'S' AND TimeUnit = 'H' AND RowNum = 1 AND RoutMachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)+(SetupTime))/(pctEff/100))*BurdenRate
WHEN CycleUnit = 'S' AND TimeUnit = 'H' AND RowNum = 1 AND RoutMachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)+(SetupTime))/(pctEff/100))*BurdenRate
WHEN CycleUnit = 'H' AND TimeUnit = 'H' AND RowNum = 1 AND RoutMachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped))/(pctEff/100)) + SetupTime)*BurdenRate
WHEN CycleUnit = 'H' AND TimeUnit = 'H' AND RowNum = 1 AND RoutMachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped))/(pctEff/100)) + SetupTime)*BurdenRate
WHEN TimeUnit = 'H' AND RowNum = 1 AND RoutMachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/60)/(pctEff/100)) + SetupTime)*BurdenRate
WHEN TimeUnit = 'H' AND RowNum = 1 AND RoutMachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/60)/(pctEff/100)) + SetupTime)*BurdenRate
WHEN CycleUnit = 'H' AND RowNum = 1 AND RoutMachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped))/(pctEff/100)) + (SetupTime/60))*BurdenRate
WHEN RowNum = 1 AND RoutMachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/60)+(SetupTime/60))/(pctEff/100))*BurdenRate
WHEN RowNum = 1 AND RoutMachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/60)+(SetupTime/60))/(pctEff/100))*BurdenRate
--Without setup
WHEN CycleUnit = 'S' AND TimeUnit = 'M' AND RowNum > 1 AND RoutMachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600))/(pctEff/100))*BurdenRate
WHEN CycleUnit = 'S' AND TimeUnit = 'M' AND RowNum > 1 AND RoutMachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600))/(pctEff/100))*BurdenRate
WHEN CycleUnit = 'S' AND TimeUnit = 'H' AND RowNum > 1 AND RoutMachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600))/(pctEff/100))*BurdenRate
WHEN CycleUnit = 'S' AND TimeUnit = 'H' AND RowNum > 1 AND RoutMachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600))/(pctEff/100))*BurdenRate
WHEN CycleUnit = 'H' AND TimeUnit = 'H' And RowNum > 1 AND RoutMachRun = 1 Then (((CycleTime)*(PiecesFinished+PiecesScrapped)))/(pctEff/100)*BurdenRate
WHEN CycleUnit = 'H' AND TimeUnit = 'H' And RowNum > 1 AND RoutMachRun = 2 Then (((CycleTime)*(PiecesFinished+PiecesScrapped)))/(pctEff/100)*BurdenRate
WHEN TimeUnit = 'H' AND RowNum > 1 AND RoutMachRun = 1 Then (((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100)*BurdenRate
WHEN TimeUnit = 'H' AND RowNum > 1 AND RoutMachRun = 2 Then (((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100)*BurdenRate
WHEN CycleUnit = 'H' AND RowNum > 1 AND RoutMachRun = 1 Then (((CycleTime)*(PiecesFinished+PiecesScrapped)))/(pctEff/100)*BurdenRate
WHEN CycleUnit = 'H' AND RowNum > 1 AND RoutMachRun = 2 Then (((CycleTime)*(PiecesFinished+PiecesScrapped)))/(pctEff/100)*BurdenRate
WHEN CycleUnit = 'M' AND TimeUnit = 'M' AND RowNum > 1 AND RoutMachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped))/60)/(pctEff/100))*BurdenRate
WHEN CycleUnit = 'M' AND TimeUnit = 'M' AND RowNum > 1 AND RoutMachRun = 1 Then (((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100)*BurdenRate
WHEN RowNum > 1 AND RoutMachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100))*BurdenRate
WHEN RowNum > 1 AND RoutMachRun = 2 Then (((((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100)))*BurdenRate
END as estBurdenCostROUT,
--************************************************************ttMachRun***********************************************************************
--************************************************************ttMachRun***********************************************************************
--************************************************************ttMachRun***********************************************************************
--************************************************************ttMachRun***********************************************************************
--************************************************************ttMachRun***********************************************************************
--************************************************************ttMachRun***********************************************************************
--************************************************************ttMachRun***********************************************************************
--************************************************************ttMachRun***********************************************************************
--COMBINED Hours w/ pctEff
--With setup
CASE
WHEN CycleUnit = 'S' AND TimeUnit = 'M' AND RowNum = 1 AND MachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)+(SetupTime/60))/(pctEff/100))
WHEN CycleUnit = 'S' AND TimeUnit = 'M' AND RowNum = 1 AND MachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)+(SetupTime/60))/(pctEff/100))
WHEN CycleUnit = 'S' AND TimeUnit = 'H' AND RowNum = 1 AND MachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)+(SetupTime))/(pctEff/100))
WHEN CycleUnit = 'S' AND TimeUnit = 'H' AND RowNum = 1 AND MachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)+(SetupTime))/(pctEff/100))
WHEN CycleUnit = 'H' AND TimeUnit = 'H' AND RowNum = 1 AND MachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped))/(pctEff/100)) + SetupTime)
WHEN CycleUnit = 'H' AND TimeUnit = 'H' AND RowNum = 1 AND MachRun = 2 Then (((((CycleTime)*(PiecesFinished+PiecesScrapped))/(pctEff/100))) + SetupTime)
WHEN TimeUnit = 'H' AND RowNum = 1 AND MachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/60)/(pctEff/100)) + SetupTime)
WHEN TimeUnit = 'H' AND RowNum = 1 AND MachRun = 2 Then (((((CycleTime)*(PiecesFinished+PiecesScrapped)/60)/(pctEff/100))) + SetupTime)
WHEN CycleUnit = 'H' AND RowNum = 1 AND MachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped))/(pctEff/100)) + (SetupTime/60))
WHEN CycleUnit = 'H' AND RowNum = 1 AND MachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped))/(pctEff/100)) + (SetupTime/60))
WHEN RowNum = 1 AND MachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/60)+(SetupTime/60))/(pctEff/100))
WHEN RowNum = 1 AND MachRun = 2 Then (((((CycleTime)*(PiecesFinished+PiecesScrapped)/60)+(SetupTime/60))/(pctEff/100)))
--Without setup
WHEN CycleUnit = 'S' AND TimeUnit = 'M' AND RowNum > 1 AND MachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600))/(pctEff/100))
WHEN CycleUnit = 'S' AND TimeUnit = 'M' AND RowNum > 1 AND MachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600))/(pctEff/100))
WHEN CycleUnit = 'S' AND TimeUnit = 'H' AND RowNum > 1 AND MachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600))/(pctEff/100))
WHEN CycleUnit = 'S' AND TimeUnit = 'H' AND RowNum > 1 AND MachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600))/(pctEff/100))
WHEN CycleUnit = 'H' AND TimeUnit = 'H' And RowNum > 1 AND MachRun = 1 Then (((CycleTime)*(PiecesFinished+PiecesScrapped)))/(pctEff/100)
WHEN CycleUnit = 'H' AND TimeUnit = 'H' And RowNum > 1 AND MachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)))/(pctEff/100))
WHEN TimeUnit = 'H' AND RowNum > 1 AND MachRun = 1 Then (((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100)
WHEN TimeUnit = 'H' AND RowNum > 1 AND MachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100))
WHEN CycleUnit = 'H' AND RowNum > 1 AND MachRun = 1 Then (((CycleTime)*(PiecesFinished+PiecesScrapped)))/(pctEff/100)
WHEN CycleUnit = 'H' AND RowNum > 1 AND MachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)))/(pctEff/100))
WHEN CycleUnit = 'M' AND TimeUnit = 'M' AND RowNum > 1 AND MachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped))/60)/(pctEff/100))
WHEN CycleUnit = 'M' AND TimeUnit = 'M' AND RowNum > 1 AND MachRun = 2 Then (((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100)
WHEN RowNum > 1 AND MachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100))
WHEN RowNum > 1 AND MachRun = 2 Then (((((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100)))
END as estLaborHoursTT,
--COMBINED Hours w/ pctEff & Split
--With setup
CASE
WHEN CycleUnit = 'S' AND TimeUnit = 'M' AND RowNum = 1 AND MachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)+(SetupTime/60))/(pctEff/100))
WHEN CycleUnit = 'S' AND TimeUnit = 'M' AND RowNum = 1 AND MachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)/2+(SetupTime/60))/(pctEff/100))
WHEN CycleUnit = 'S' AND TimeUnit = 'H' AND RowNum = 1 AND MachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)+(SetupTime))/(pctEff/100))
WHEN CycleUnit = 'S' AND TimeUnit = 'H' AND RowNum = 1 AND MachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)/2+(SetupTime))/(pctEff/100))
WHEN CycleUnit = 'H' AND TimeUnit = 'H' AND RowNum = 1 AND MachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped))/(pctEff/100)) + SetupTime)
WHEN CycleUnit = 'H' AND TimeUnit = 'H' AND RowNum = 1 AND MachRun = 2 Then (((((CycleTime)*(PiecesFinished+PiecesScrapped))/(pctEff/100)))/2 + SetupTime)
WHEN TimeUnit = 'H' AND RowNum = 1 AND MachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/60)/(pctEff/100)) + SetupTime)
WHEN TimeUnit = 'H' AND RowNum = 1 AND MachRun = 2 Then (((((CycleTime)*(PiecesFinished+PiecesScrapped)/60)/(pctEff/100)))/2 + SetupTime)
WHEN CycleUnit = 'H' AND RowNum = 1 AND MachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped))/(pctEff/100)) + (SetupTime/60))
WHEN CycleUnit = 'H' AND RowNum = 1 AND MachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped))/(pctEff/100))/2 + (SetupTime/60))
WHEN RowNum = 1 AND MachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/60)+(SetupTime/60))/(pctEff/100))
WHEN RowNum = 1 AND MachRun = 2 Then (((((CycleTime)*(PiecesFinished+PiecesScrapped)/60)/2+(SetupTime/60))/(pctEff/100)))
--Without setup
WHEN CycleUnit = 'S' AND TimeUnit = 'M' AND RowNum > 1 AND MachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600))/(pctEff/100))
WHEN CycleUnit = 'S' AND TimeUnit = 'M' AND RowNum > 1 AND MachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)/2)/(pctEff/100))
WHEN CycleUnit = 'S' AND TimeUnit = 'H' AND RowNum > 1 AND MachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600))/(pctEff/100))
WHEN CycleUnit = 'S' AND TimeUnit = 'H' AND RowNum > 1 AND MachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)/2)/(pctEff/100))
WHEN CycleUnit = 'H' AND TimeUnit = 'H' And RowNum > 1 AND MachRun = 1 Then (((CycleTime)*(PiecesFinished+PiecesScrapped)))/(pctEff/100)
WHEN CycleUnit = 'H' AND TimeUnit = 'H' And RowNum > 1 AND MachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)))/(pctEff/100))/2
WHEN TimeUnit = 'H' AND RowNum > 1 AND MachRun = 1 Then (((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100)
WHEN TimeUnit = 'H' AND RowNum > 1 AND MachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100))/2
WHEN CycleUnit = 'H' AND RowNum > 1 AND MachRun = 1 Then (((CycleTime)*(PiecesFinished+PiecesScrapped)))/(pctEff/100)
WHEN CycleUnit = 'H' AND RowNum > 1 AND MachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)))/(pctEff/100))/2
WHEN CycleUnit = 'M' AND TimeUnit = 'M' AND RowNum > 1 AND MachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped))/60)/(pctEff/100))
WHEN CycleUnit = 'M' AND TimeUnit = 'M' AND RowNum > 1 AND MachRun = 2 Then (((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100)/2
WHEN RowNum > 1 AND MachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100))
WHEN RowNum > 1 AND MachRun = 2 Then (((((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100)))/2
END as splitLaborHoursTT,
--COMBINED Labor using Hours w/ pctEff & Split
--With setup
CASE
WHEN CycleUnit = 'S' AND TimeUnit = 'M' AND RowNum = 1 AND MachRun = 1 AND UnattendOp = 'N' Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)+(SetupTime/60))/(pctEff/100))*ActualPayRate
WHEN CycleUnit = 'S' AND TimeUnit = 'M' AND RowNum = 1 AND MachRun = 2 AND UnattendOp = 'N' Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)/2+(SetupTime/60))/(pctEff/100))*ActualPayRate
WHEN CycleUnit = 'S' AND TimeUnit = 'H' AND RowNum = 1 AND MachRun = 1 AND UnattendOp = 'N' Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)+(SetupTime))/(pctEff/100))*ActualPayRate
WHEN CycleUnit = 'S' AND TimeUnit = 'H' AND RowNum = 1 AND MachRun = 2 AND UnattendOp = 'N'Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)/2+(SetupTime))/(pctEff/100))*ActualPayRate
WHEN CycleUnit = 'H' AND TimeUnit = 'H' AND RowNum = 1 AND MachRun = 1 AND UnattendOp = 'N' Then ((((CycleTime)*(PiecesFinished+PiecesScrapped))/(pctEff/100)) + SetupTime)*ActualPayRate
WHEN CycleUnit = 'H' AND TimeUnit = 'H' AND RowNum = 1 AND MachRun = 2 AND UnattendOp = 'N' Then (((((CycleTime)*(PiecesFinished+PiecesScrapped))/(pctEff/100)))/2 + SetupTime)*ActualPayRate
WHEN TimeUnit = 'H' AND RowNum = 1 AND MachRun = 1 AND UnattendOp = 'N' Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/60)/(pctEff/100)) + SetupTime)*ActualPayRate
WHEN TimeUnit = 'H' AND RowNum = 1 AND MachRun = 2 AND UnattendOp = 'N' Then (((((CycleTime)*(PiecesFinished+PiecesScrapped)/60)/(pctEff/100)))/2 + SetupTime)*ActualPayRate
WHEN CycleUnit = 'H' AND RowNum = 1 AND MachRun = 1 AND UnattendOp = 'N' Then ((((CycleTime)*(PiecesFinished+PiecesScrapped))/(pctEff/100)) + (SetupTime/60))*ActualPayRate
WHEN CycleUnit = 'H' AND RowNum = 1 AND MachRun = 2 AND UnattendOp = 'N' Then ((((CycleTime)*(PiecesFinished+PiecesScrapped))/(pctEff/100))/2 + (SetupTime/60))*ActualPayRate
WHEN RowNum = 1 AND MachRun = 1 AND UnattendOp = 'N' Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/60)+(SetupTime/60))/(pctEff/100))*ActualPayRate
WHEN RowNum = 1 AND MachRun = 2 AND UnattendOp = 'N' Then (((((CycleTime)*(PiecesFinished+PiecesScrapped)/60)/2+(SetupTime/60))/(pctEff/100)))*ActualPayRate
WHEN TimeUnit = 'M' AND RowNum = 1 AND UnattendOp = 'Y' Then ((((SetupTime/60))/(pctEff/100)))*ActualPayRate
WHEN TimeUnit = 'H' AND RowNum = 1 AND UnattendOp = 'Y' Then ((((SetupTime))/(pctEff/100)))*ActualPayRate
--Without setup
WHEN CycleUnit = 'S' AND TimeUnit = 'M' AND RowNum > 1 AND MachRun = 1 AND UnattendOp = 'N' Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600))/(pctEff/100))*ActualPayRate
WHEN CycleUnit = 'S' AND TimeUnit = 'M' AND RowNum > 1 AND MachRun = 2 AND UnattendOp = 'N' Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)/2)/(pctEff/100))*ActualPayRate
WHEN CycleUnit = 'S' AND TimeUnit = 'H' AND RowNum > 1 AND MachRun = 1 AND UnattendOp = 'N' Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600))/(pctEff/100))*ActualPayRate
WHEN CycleUnit = 'S' AND TimeUnit = 'H' AND RowNum > 1 AND MachRun = 2 AND UnattendOp = 'N' Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)/2)/(pctEff/100))*ActualPayRate
WHEN CycleUnit = 'H' AND TimeUnit = 'H' And RowNum > 1 AND MachRun = 1 AND UnattendOp = 'N' Then (((CycleTime)*(PiecesFinished+PiecesScrapped)))/(pctEff/100)*ActualPayRate
WHEN CycleUnit = 'H' AND TimeUnit = 'H' And RowNum > 1 AND MachRun = 2 AND UnattendOp = 'N' Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)))/(pctEff/100))/2*ActualPayRate
WHEN TimeUnit = 'H' AND RowNum > 1 AND MachRun = 1 AND UnattendOp = 'N' Then (((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100)*ActualPayRate
WHEN TimeUnit = 'H' AND RowNum > 1 AND MachRun = 2 AND UnattendOp = 'N' Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100))/2*ActualPayRate
WHEN CycleUnit = 'H' AND RowNum > 1 AND MachRun = 1 AND UnattendOp = 'N' Then (((CycleTime)*(PiecesFinished+PiecesScrapped)))/(pctEff/100)*ActualPayRate
WHEN CycleUnit = 'H' AND RowNum > 1 AND MachRun = 2 AND UnattendOp = 'N' Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)))/(pctEff/100))/2*ActualPayRate
WHEN CycleUnit = 'M' AND TimeUnit = 'M' AND RowNum > 1 AND MachRun = 1 AND UnattendOp = 'N' Then ((((CycleTime)*(PiecesFinished+PiecesScrapped))/60)/(pctEff/100))*ActualPayRate
WHEN CycleUnit = 'M' AND TimeUnit = 'M' AND RowNum > 1 AND MachRun = 2 AND UnattendOp = 'N' Then (((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100)/2*ActualPayRate
WHEN RowNum > 1 AND MachRun = 1 AND UnattendOp = 'N' Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100))*ActualPayRate
WHEN RowNum > 1 AND MachRun = 2 AND UnattendOp = 'N' Then (((((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100)))/2*ActualPayRate
WHEN TimeUnit = 'M' AND RowNum > 1 AND UnattendOp = 'Y' Then 0
WHEN TimeUnit = 'H' AND RowNum > 1 AND UnattendOp = 'Y' Then 0
END as estLaborCostTT,
--COMBINED Burden w/ pctEff
--With setup
CASE
WHEN CycleUnit = 'S' AND TimeUnit = 'M' AND RowNum = 1 AND MachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)+(SetupTime/60))/(pctEff/100))*BurdenRate
WHEN CycleUnit = 'S' AND TimeUnit = 'M' AND RowNum = 1 AND MachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)+(SetupTime/60))/(pctEff/100))*BurdenRate
WHEN CycleUnit = 'S' AND TimeUnit = 'H' AND RowNum = 1 AND MachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)+(SetupTime))/(pctEff/100))*BurdenRate
WHEN CycleUnit = 'S' AND TimeUnit = 'H' AND RowNum = 1 AND MachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600)+(SetupTime))/(pctEff/100))*BurdenRate
WHEN CycleUnit = 'H' AND TimeUnit = 'H' AND RowNum = 1 AND MachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped))/(pctEff/100)) + SetupTime)*BurdenRate
WHEN CycleUnit = 'H' AND TimeUnit = 'H' AND RowNum = 1 AND MachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped))/(pctEff/100)) + SetupTime)*BurdenRate
WHEN TimeUnit = 'H' AND RowNum = 1 AND MachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/60)/(pctEff/100)) + SetupTime)*BurdenRate
WHEN TimeUnit = 'H' AND RowNum = 1 AND MachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/60)/(pctEff/100)) + SetupTime)*BurdenRate
WHEN CycleUnit = 'H' AND RowNum = 1 AND MachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped))/(pctEff/100)) + (SetupTime/60))*BurdenRate
WHEN RowNum = 1 AND MachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/60)+(SetupTime/60))/(pctEff/100))*BurdenRate
WHEN RowNum = 1 AND MachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/60)+(SetupTime/60))/(pctEff/100))*BurdenRate
--Without setup
WHEN CycleUnit = 'S' AND TimeUnit = 'M' AND RowNum > 1 AND MachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600))/(pctEff/100))*BurdenRate
WHEN CycleUnit = 'S' AND TimeUnit = 'M' AND RowNum > 1 AND MachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600))/(pctEff/100))*BurdenRate
WHEN CycleUnit = 'S' AND TimeUnit = 'H' AND RowNum > 1 AND MachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600))/(pctEff/100))*BurdenRate
WHEN CycleUnit = 'S' AND TimeUnit = 'H' AND RowNum > 1 AND MachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/3600))/(pctEff/100))*BurdenRate
WHEN CycleUnit = 'H' AND TimeUnit = 'H' And RowNum > 1 AND MachRun = 1 Then (((CycleTime)*(PiecesFinished+PiecesScrapped)))/(pctEff/100)*BurdenRate
WHEN CycleUnit = 'H' AND TimeUnit = 'H' And RowNum > 1 AND MachRun = 2 Then (((CycleTime)*(PiecesFinished+PiecesScrapped)))/(pctEff/100)*BurdenRate
WHEN TimeUnit = 'H' AND RowNum > 1 AND MachRun = 1 Then (((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100)*BurdenRate
WHEN TimeUnit = 'H' AND RowNum > 1 AND MachRun = 2 Then (((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100)*BurdenRate
WHEN CycleUnit = 'H' AND RowNum > 1 AND MachRun = 1 Then (((CycleTime)*(PiecesFinished+PiecesScrapped)))/(pctEff/100)*BurdenRate
WHEN CycleUnit = 'H' AND RowNum > 1 AND MachRun = 2 Then (((CycleTime)*(PiecesFinished+PiecesScrapped)))/(pctEff/100)*BurdenRate
WHEN CycleUnit = 'M' AND TimeUnit = 'M' AND RowNum > 1 AND MachRun = 2 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped))/60)/(pctEff/100))*BurdenRate
WHEN CycleUnit = 'M' AND TimeUnit = 'M' AND RowNum > 1 AND MachRun = 1 Then (((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100)*BurdenRate
WHEN RowNum > 1 AND MachRun = 1 Then ((((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100))*BurdenRate
WHEN RowNum > 1 AND MachRun = 2 Then (((((CycleTime)*(PiecesFinished+PiecesScrapped)/60))/(pctEff/100)))*BurdenRate
END as estBurdenCostTT
FROM cte
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment