February 26, 2015 at 11:23 am
Hi
I am writing a query to return some production data.
Basically i need to insert either 1 or 2 rows into a Table variable based on a decision as to does the production part make 1 or 2 items ( The Raw data does not allow for this it comes from a look up in my database)
I can retrieve all the source data i need easily but when i come to insert it into the table variable i need to insert 1 record if its a single part or 2 records if its a twin part
i know could use a cursor but im sure there has to be an easier way !
Below is the code i have at the moment
declare @startdate as datetime
declare @enddate as datetime
declare @Line as Integer
DECLARE @count INT
set @startdate = '2015-01-01'
set @enddate = '2015-01-31'
set @Line = 3
DECLARE @OFBDATA TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[ShiftStartdate] [date] NULL,
[ProdShiftcolor] [varchar](15) NULL,
[Line] [smallint] NULL,
[MasterLabel] [varchar](25) NULL,
[DieNo] [smallint] NULL,
[Goodparts] [smallint] NULL,
[ScrapParts] [smallint] NULL)
Insert Into @OFBDATA
(ShiftStartdate,
ProdShiftcolor,
Line,
DieNo,
Goodparts,
ScrapParts)
How to make the If then else section work
select IF ToolList.TwinPart = 0 Then
scrapdata.Rundate,ScrapData.ProdShiftcolor,ScrapData.Line,ScrapData.DieNo,scrapdata.[Good parts F ],ScrapData.[Scrap F]
Else If ToolList.TwinPart = 1 Then
scrapdata.Rundate,ScrapData.ProdShiftcolor,ScrapData.Line,ScrapData.DieNo,scrapdata.[Good parts F ],ScrapData.[Scrap F]
union
scrapdata.Rundate,ScrapData.ProdShiftcolor,ScrapData.Line,ScrapData.DieNo,scrapdata.[Good parts R ],ScrapData.[Scrap R]
End If
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
WHERE (ProdRecord.ProdShiftID IN
(SELECT ID
FROM DailyProdShifts AS DailyProdShifts_1
WHERE (ShiftStartdate >= @startdate) AND (ShiftStartdate <= @enddate))) AND (ProdRecord.Line = @Line) ) as ScrapData
select * From @OFBDATA
February 26, 2015 at 1:55 pm
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.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply