Insert Into @OFBDATA
(ShiftStartdate,
ProdShiftcolor,
Line,
DieNo,
Goodparts,
ScrapParts)
Select
scrapdata.Rundate,ScrapData.ProdShiftcolor,ScrapData.Line,ScrapData.DieNo,
case when which_row = 'F' then scrapdata.[Good parts F ] else scrapdata.[Good parts R ] END,
case when which_row = 'F' then ScrapData.[Scrap F] else ScrapData.[Scrap R] END
From
(SELECT DailyProdShifts.ShiftStartdate AS Rundate,
ProdRecord.ProdShiftID,
ProdRecord.RunNo,
ProdRecord.DieNo,
DailyProdShifts.
ProdShiftcolor,
ProdShiftColors.ShiftName,
ProdRecord.LotSizeRear AS [Good Parts R or 1],
ProdRecord.ScrapRear AS [Bad Parts R or 1],
ProdRecord.LotSizeFront AS [Good parts F ],
ProdRecord.ScrapFront AS [Scrap F],
ProdRecord.Line,
ToolList.TwinPart
FROM prodRecord INNER JOIN
DailyProdShifts ON ProdRecord.ProdShiftID = DailyProdShifts.ID INNER JOIN
ProdShiftColors ON DailyProdShifts.ProdShiftcolor = ProdShiftColors.ID inner JOIN
ToolList ON ProdRecord.DieNo = ToolList.DieNo
CROSS APPLY (
SELECT 'F' AS which_row
UNION ALL
SELECT 'R'
WHERE ToolList.TwinPart = 1
) AS ca1
WHERE (ProdRecord.ProdShiftID IN
(SELECT ID
FROM DailyProdShifts AS DailyProdShifts_1
WHERE (ShiftStartdate >= @startdate) AND (ShiftStartdate <= @enddate))) AND (ProdRecord.Line = @Line) ) as ScrapData
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.