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