I did not write this stored procedure but have been asked to edit the code and display new data. The column name is H.BilletUOM. It only contains only three values. B71,B72, and B73. I was able to select the column in the stored procedure easily so the data is available for the report but the next step is where I am having difficulty. If the value is B71 then I want to display 28ft. B72 display 34ft. B73 display 40ft. Can someone please take a look at this code and try to help me figure this out. I highlighted the H.BilletUOM in red so you can quickly see where I am selecting this column. Any assistance is greatly appreciated. I am new to SQL and have not started classes yet. Thanks again.
CREATE PROCEDURE SP_Melt_HeatReport_HeatList
@.HeatNo varchar(50) = NULL,
@.Date varchar(50) = NULL
AS
UPDATE WebPageData
SET Hits = Hits + 1
WHERE SPName = 'SP_Melt_HeatReport_HeatList'
DECLARE @.Site varchar(100) SET @.Site = dbo.UDF_SystemParameter('Site')
DECLARE @.SiteName varchar(100) SET @.SiteName = dbo.UDF_SystemParameter('SiteName')
DECLARE @.MinHeatNo int SET @.MinHeatNo = CSCMelting.dbo.UDF_SystemParameter('MinClevelandHeat')
DECLARE @.MaxHeatNo int SET @.MaxHeatNo = CSCMelting.dbo.UDF_SystemParameter('MaxClevelandHeat')
DECLARE @.HeatsPerPage int SET @.HeatsPerPage = 40
IF @.Date IS NOT NULL
BEGIN
SELECT @.MinHeatNo = MIN(HeatNo)
FROM CSCMelting.dbo.MS_HeatLog
WHERE RptDate = @.Date AND HeatNo BETWEEN @.MinHeatNo AND @.MaxHeatNo
SELECT @.MaxHeatNo = MAX(HeatNo)
FROM CSCMelting.dbo.MS_HeatLog
WHERE RptDate = @.Date AND HeatNo BETWEEN @.MinHeatNo AND @.MaxHeatNo
SELECT @.HeatNo = @.MinHeatNo
SELECT @.HeatsPerPage = (@.MaxHeatNo - @.MinHeatNo) / 10 + 1
END
IF PATINDEX('%,%', @.HeatNo) > 0
SET @.HeatNo = SUBSTRING(@.HeatNo ,PATINDEX('%,%', @.HeatNo) + 1, 100)
DECLARE @.ColorRed varchar(7) SET @.ColorRed = '#FF0000'
DECLARE @.ColorRedDark varchar(7) SET @.ColorRedDark = '#AA0000'
DECLARE @.ColorBlue varchar(7) SET @.ColorBlue = '#0000FF'
DECLARE @.ColorGreen varchar(7) SET @.ColorGreen = '#008800'
DECLARE @.ColorYellow varchar(7) SET @.ColorYellow = '#999900'
DECLARE @.ColorWhite varchar(7) SET @.ColorWhite = '#FFFFFF'
DECLARE @.ColorBlack varchar(7) SET @.ColorBlack = '#000000'
DECLARE @.ColorGray varchar(7) SET @.ColorGray = '#eeeeee'
IF @.HeatNo IS NULL OR @.HeatNo = '' OR ISNUMERIC(@.HeatNo) = 0 OR @.HeatNo = '0'
SELECT @.HeatNo = MAX(HeatNo) - (@.HeatsPerPage * 10) + 10
FROM CSCMelting.dbo.MS_HeatLog
WHERE HeatNo BETWEEN @.MinHeatNo AND @.MaxHeatNo
SELECT Tag = 1, Parent = NULL,
[Title!1!Title1] = 'Charter Steel ' + @.SiteName + ' Melting',
[Title!1!Title2] = 'LEVEL II HEAT REPORTS'
FOR XML EXPLICIT
SELECT Tag = 1, Parent = NULL,
[PageVars!1!Site] = @.Site,
[PageVars!1!WebPageID] = 20500,
[PageVars!1!HeatNo] = @.HeatNo,
[PageVars!1!HeatNoNext] = @.HeatNo + (@.HeatsPerPage * 10) ,
[PageVars!1!HeatNoPrev] = @.HeatNo - (@.HeatsPerPage * 10) ,
[PageVars!1!HeatNoLast] = 0,
[PageVars!1!HomeLink] = 'http://mesweb/xml.aspx?Site=' + @.Site + '&WebPageID=20500'
FOR XML EXPLICIT
SELECT HeatNo,
Job = JOB_NUMBER,
RowColor = CASE WHEN (Heatno /10) % 2 = 0 THEN @.ColorGray ELSE @.ColorWhite END,
Grade = CONVERT(varchar(20), GRADE + ' ' + CS_CMDESC),
Status = CASE Status WHEN 4 THEN
CASE WHEN ChemCert = 1 THEN 'Certified'
WHEN EXISTS(SELECT * FROM CSCMelting.dbo.MS_BilletInventory WHERE HeatNo = H.HeatNo AND Location = '99') THEN 'Inspection'
WHEN EXISTS (SELECT * FROM CSCMelting.dbo.MS_FceHeatLog WHERE PourbackHeatNo = H.HeatNo) THEN 'CCM'
ELSE 'Inspected'
END
WHEN 3 THEN 'CCM'
WHEN 2 THEN 'LRF'
WHEN 1 THEN 'EAF'
ELSE ''
END, --+ CASE WHEN EXISTS (SELECT * FROM CSCMelting.dbo.MS_FceHeatLog WHERE PourbackHeatNo = H.HeatNo) THEN ' (PB)' ELSE '' END,
H.NonConforming,
PourBackTons = (SELECT MIN(PourBackAmt) / 2000 FROM CSCMelting.dbo.MS_FceHeatLog WHERE PourbackHeatNo = H.HeatNo),
Location = (SELECT MAX(Location) FROM CSCMelting.dbo.MS_BilletInventory WHERE HeatNo = H.HeatNo),
CntCounted = H.BilletsMade1 + H.BilletsMade2 + H.BilletsMade3 + H.BilletsMade4,
ScrapCount = (SELECT ScrapCount FROM CSCMelting.dbo.VW_Prod_ScrapBilletsByHeat Sc WHERE Sc.Heatno = H.HeatNo),
CntInv = (SELECT COUNT(*) FROM CSCMelting.dbo.MS_BilletInventory WHERE HeatNo = H.HeatNo),
CntRolled = (SELECT SUM(Quantity) FROM CSCRolling.dbo._Lot WHERE Heat = H.HeatNo),
CntSched = (SELECT SUM(IC_QUANTITY) FROM CSCRolling.dbo._Lot_Scheduled WHERE IC_LOT_NUMBER = H.HeatNo),
CCM.SEQ_COUNTER,
SeqHeat = (SELECT COUNT(*) FROM CSCMelting.dbo.REP_CCM CCM2 WHERE CCM2.SEQ_COUNTER = CCM.SEQ_COUNTER AND CCM2.REPORT_COUNTER <= R.REPORT_COUNTER), --CCM.SEQ_HEAT_COUNTER,
SeqTotal = (SELECT COUNT(*) FROM CSCMelting.dbo.REP_CCM CCM2 WHERE CCM2.SEQ_COUNTER = CCM.SEQ_COUNTER),
RptDate = CONVERT(varchar(10), RptDate, 120),
EndTapTime = H.EndTapTime,
H.BilletUOM,
ConsumptionsLink = 'http://mesweb/xml.aspx?Site=' + @.Site + '&WebPageID=20501&HeatNo=' + CONVERT(varchar(20), HeatNo),
FCERepCnt = ISNULL((SELECT MAX(REPORT_COUNTER) FROM CSCMelting.dbo.REPORTS WHERE Heat_ID = H.HeatNo AND Area_ID = 400),0),
FCERepCntLink = 'http://mesweb/xml.aspx?Site=' + @.Site + '&WebPageID=20100&HeatNo=' + CONVERT(varchar(20), HeatNo),
-- VADRepCnt = ISNULL((SELECT MAX(REPORT_COUNTER) FROM CSCMelting.dbo.REPORTS WHERE Heat_ID = H.HeatNo AND Area_ID = 600),0),
-- VADRepCntLink = 'http://mesweb/xml.aspx?Site=' + @.Site + '&WebPageID=20200&HeatNo=' + CONVERT(varchar(20), HeatNo),
LRFRepCnt = ISNULL((SELECT MAX(REPORT_COUNTER) FROM CSCMelting.dbo.REPORTS WHERE Heat_ID = H.HeatNo AND Area_ID = 600),0),
LRFRepCntLink = 'http://mesweb/xml.aspx?Site=' + @.Site + '&WebPageID=20200&HeatNo=' + CONVERT(varchar(20), HeatNo),
VODRepCnt = ISNULL((SELECT MAX(REPORT_COUNTER) FROM CSCMelting.dbo.REPORTS WHERE Heat_ID = H.HeatNo AND Area_ID = 800),0),
VODRepCntLink = 'http://mesweb/xml.aspx?Site=' + @.Site + '&WebPageID=20200&HeatNo=' + CONVERT(varchar(20), HeatNo),
CCMRepCnt = ISNULL((SELECT MAX(REPORT_COUNTER) FROM CSCMelting.dbo.REPORTS WHERE Heat_ID = H.HeatNo AND Area_ID = 1100),0),
CCMRepCntLink = 'http://mesweb/xml.aspx?Site=' + @.Site + '&WebPageID=20300&HeatNo=' + CONVERT(varchar(20), HeatNo),
RunoutOrderLink = 'http://mesweb/xml.aspx?Site=' + @.Site + '&WebPageID=20301&HeatNo=' + CONVERT(varchar(20), HeatNo)
INTO #Heats
FROM CSCMelting.dbo.MS_HeatLog H
LEFT JOIN CSCMelting.dbo.REPORTS R ON R.HEAT_ID = H.HeatNo AND R.AREA_ID = 1100
LEFT JOIN CSCMelting.dbo.REP_CCM CCM ON CCM.REPORT_COUNTER = R.REPORT_COUNTER
WHERE HeatNo >= @.HeatNo AND HeatNo < @.HeatNo + (@.HeatsPerPage * 10) AND HeatNo % 10 = 0
SELECT *,
EndTapTimeStr = CONVERT(varchar(25), EndTapTime, 100),
MissChemStr = CASE WHEN NonConforming = 1 THEN 'Y' ELSE '' END,
PourBackTonsStr = CASE WHEN PourBackTons = 0 THEN '' ELSE CONVERT(varchar(10), PourBackTons) + 'tn' END,
CntCountedStr = NULLIF(CntCounted, 0),
ScrapCountStr = NULLIF(ScrapCount, 0),
CntInvStr = NULLIF(CntInv, 0),
CntRolledStr = NULLIF(CntRolled, 0),
CntSchedStr = NULLIF(CntSched, 0),
HeatSeqStr = '' + char(CONVERT(int, SEQ_COUNTER) % 26 + 65) + ' - ' + CASE WHEN SeqTotal <= 1 THEN 'single' ELSE CONVERT(varchar(2), SeqHeat) + ' of ' + CONVERT(varchar(2), SeqTotal) END,
StatusColor = CASE
WHEN PourBackTons > 0 THEN @.ColorBlack
WHEN Status = 'EAF' THEN @.ColorBlue
WHEN Status = 'LRF' THEN @.ColorGreen
WHEN Status = 'CCM' THEN @.ColorYellow
WHEN Status = 'Inspection' THEN @.ColorRedDark
WHEN Status = 'Inspected' THEN @.ColorRed
WHEN Status = 'Certified' THEN @.ColorBlack
ELSE ''
END
FROM #Heats Heats
ORDER BY HeatNo
FOR XML AUTO
print @.HeatNo + (@.HeatsPerPage * 10) + 10
DROP TABLE #Heats
GO
http://msdn2.microsoft.com/en-us/library/ms181765.aspx|||
I came up with this code and it still is still giving me syntax errors. Could you please take a look and see what I am doing wrong? Thank you very much for any help.
H.BilletUOM = CASE H.BilletUOM WHEN B71 THEN '28ft.'
WHEN B72 THEN '34ft.'
WHEN B73 THEN '40ft.'
ELSE ''
END,
WHEN 'B71' THEN...
WHEN 'B72' THEN...
etc..
No comments:
Post a Comment