Jun 15, 2000
I can not get this code to work, I dey try to convert a non relational database, catcde na the name of the field in the non conversional database and i have a table in the relational database that called DuesCategory, that it's DuesCategoryname has the following fields,
Retired
On Leave
No Dues
Agency Fee Payer
Student
Full Dues
3/4 Dues
1/2 Dues
1/4 Dues
1/8 Dues
the fields in the non relational are not d same that is why i am trying to map it using the case statements, and since i will be converting alot of databases i what it to be based on which peremeter they put in.
select @DuesCategoryID=DuesCategoryID from DuesCategory where DuesCategoryName=@catcde
WHILE (@@FETCH_STATUS <>-1)
BEGIN
select @DuesCategoryID= case @catcde when '@DuesCategoryName1' then 'Per Diem'
when '@DuesCategoryName2' then 'Retired.'
when '@DuesCategoryName3' then 'On Leave.'
when '@DuesCategoryName4' then 'No Dues.'
when '@DuesCategoryName5' then 'Agency Fee Payer.'
when '@DuesCategoryName6' then 'Student.'
when '@DuesCategoryName7' then 'Full Dues.'
when '@DuesCategoryName8' then '3/4 Dues.'
when '@DuesCategoryName9' then '1/2 Dues.'
when '@DuesCategoryName10' then '1/4 Dues.'
when '@DuesCategoryName11' then '1/8 Dues.'
when ' ' then null
end
This is all the code
CREATE Procedure _RunIndividual_5 @tablename varchar(100) as
Exec ('declare cur_individual cursor for
SELECT last, first, mi, address1, address2, city, state, zip,
bdate, sex, soc, mstat, depnum, hometel, status, sefdte, catcde,
cefdate, poscd, poedte, rfdlt, duespthru, paydeduc, classcode,
location, deptcode, tenuresub, ocertsub, bassalary, salarystep,
salarycol, startdate, initdate, seniordate, worktel, votecope, votedate,
voteded, polparty, polactiv, precinct, condst, sendst, asmdst, rschd,
loccode, extra1, extra2, extra3, extra4, extra5, extra6, extra7,
extra8, extra9, extra10, access, pctype, os, pclocation, internet,
email, show, county, localname, localnum, bargin, offtitle2, offtitle,
regvoter, reshigh, reselem, resunit, rescollege, employer, empnum, workfax,
offtitle3, work_phone, const_code, class_titl, faxnumber, constit
FROM Conversion.dbo.'+ @tablename+' order by last,first')
----Declare variables
declare @last nvarchar (50) ,
@first nvarchar (50) ,
@mi nvarchar (50) ,
@address1 nvarchar (50) ,
@address2 nvarchar (50) ,
@city nvarchar (50) ,
@state nvarchar (50) ,
@zip nvarchar (255) ,
@bdate nvarchar (255) ,
@sex nvarchar (255) ,
@soc nvarchar (255) ,
@mstat nvarchar (255) ,
@depnum float ,
@hometel nvarchar (255) ,
@status nvarchar (255) ,
@sefdte nvarchar (255) ,
@catcde nvarchar (255) ,
@cefdate nvarchar (255) ,
@poscd nvarchar (50) ,
@poedte nvarchar (255) ,
@rfdlt nvarchar (255) ,
@duespthru nvarchar (255) ,
@paydeduc nvarchar (255) ,
@classcode nvarchar (50) ,
@location nvarchar (255) ,
@deptcode nvarchar (255) ,
@tenuresub nvarchar (255) ,
@ocertsub nvarchar (255) ,
@bassalary float ,
@salarystep float ,
@salarycol nvarchar (255) ,
@startdate nvarchar (255) ,
@initdate nvarchar (255) ,
@seniordate nvarchar (255) ,
@worktel nvarchar (255) ,
@votecope float ,
@votedate nvarchar (255) ,
@voteded nvarchar (255) ,
@polparty nvarchar (255) ,
@polactiv nvarchar (255) ,
@precinct nvarchar (255) ,
@condst nvarchar (255) ,
@sendst nvarchar (255) ,
@asmdst nvarchar (255) ,
@rschd nvarchar (255) ,
@loccode int ,
@extra1 nvarchar (255) ,
@extra2 nvarchar (255) ,
@extra3 nvarchar (255) ,
@extra4 nvarchar (255) ,
@extra5 nvarchar (255) ,
@extra6 nvarchar (255) ,
@extra7 nvarchar (255) ,
@extra8 nvarchar (255) ,
@extra9 nvarchar (255) ,
@extra10 nvarchar (255) ,
@access bit ,
@pctype nvarchar (255) ,
@os nvarchar (255) ,
@pclocation nvarchar (255) ,
@internet nvarchar (255) ,
@email nvarchar (255) ,
@show bit ,
@county nvarchar (255) ,
@localname nvarchar (255) ,
@localnum nvarchar (255) ,
@bargin nvarchar (255) ,
@offtitle2 nvarchar (255) ,
@offtitle nvarchar (255) ,
@regvoter nvarchar (255) ,
@reshigh nvarchar (255) ,
@reselem nvarchar (255) ,
@resunit nvarchar (255) ,
@rescollege nvarchar (255) ,
@employer nvarchar (255) ,
@empnum nvarchar (255) ,
@workfax nvarchar (255) ,
@offtitle3 nvarchar (255) ,
@work_phone nvarchar (255) ,
@const_code nvarchar (255) ,
@class_titl nvarchar (255) ,
@faxnumber nvarchar (255) ,
@constit nvarchar (255) ,
@DuesCategoryName nvarchar (255) ,
@DuesCategoryName1 nvarchar (255) ,
@DuesCategoryName2 nvarchar (255) ,
@DuesCategoryName3 nvarchar(255) ,
@DuesCategoryName4 nvarchar(255) ,
@DuesCategoryName5 nvarchar(255) ,
@DuesCategoryName6 nvarchar(255) ,
@DuesCategoryName7 nvarchar(255) ,
@DuesCategoryName8 nvarchar(255) ,
@DuesCategoryName9 nvarchar(255) ,
@DuesCategoryName10 nvarchar(255) ,
@DuesCategoryName11 nvarchar(255) ,
--- other variables
@prefix varchar(5),@prefixid uniqueidentifier,@worksiteid uniqueidentifier,
@chapterid uniqueidentifier,@PaymentMethodName varchar(50),@PaymentMethodID uniqueidentifier,
@DuesCategoryID uniqueidentifier,@DuesCategoryLocalID uniqueidentifier,
@DeactivateReasonID uniqueidentifier,@DeactivateReasonLocalID uniqueidentifier,
@JobClassID uniqueidentifier,@LocalJobClassID uniqueidentifier,
@MaritalStatusID uniqueidentifier,@MemberStatusID uniqueidentifier,
@SubjectID uniqueidentifier,@PoliticalPartyID uniqueidentifier,
@EmployerID uniqueidentifier, @LocalUnionID uniqueidentifier,@LocalUnionNbr char(5),
@addressID uniqueidentifier,@StateTerritoryId uniqueidentifier,
@CountryId uniqueidentifier,@PoliticallyActiveTF bit,@IndividualId uniqueidentifier
begin
open cur_individual
fetch from cur_individual into @last,@first, @mi,@address1,@address2,@city,@state,
@zip,@bdate,@sex,@soc,@mstat,@depnum,@hometel , @status , @sefdte ,
@catcde , @cefdate , @poscd , @poedte ,
@rfdlt , @duespthru , @paydeduc ,
@classcode ,@location ,@deptcode ,
@tenuresub ,@ocertsub ,@bassalary ,@salarystep ,
@salarycol ,@startdate ,@initdate ,
@seniordate ,@worktel ,@votecope ,@votedate ,
@voteded ,@polparty ,@polactiv ,
@precinct ,@condst ,@sendst ,
@asmdst ,@rschd ,@loccode ,@extra1 ,
@extra2 ,@extra3 ,@extra4 ,@extra5 ,
@extra6 ,@extra7 ,@extra8 ,@extra9 ,
@extra10 ,@access ,@pctype ,@os ,
@pclocation ,@internet ,@email ,
@show ,@county ,@localname ,@localnum ,
@bargin ,@offtitle2 ,@offtitle ,
@regvoter ,@reshigh ,@reselem ,
@resunit ,@rescollege ,@employer ,
@empnum ,@workfax ,@offtitle3 ,
@work_phone ,@const_code ,@class_titl ,
@faxnumber ,@constit
WHILE (@@FETCH_STATUS <>-1)
BEGIN
select @prefix= case @sex when 'Male' then 'Mr.'
when 'Female' then 'Ms.'
when ' ' then null
end
select @prefixid=prefixid from prefix where prefixname=@prefix
select @worksiteid=worksiteid,@EmployerID=EmployerID from worksite where worksitenumber=@loccode
select @chapterid=chapterid from chapter where localunionid in (select localunionid from localunion)
select @PaymentMethodName= case @paydeduc when 'YES' then 'Payroll Deduction'
end
Select @PaymentMethodID=PaymentMethodID from PaymentMethod where PaymentMethodName=@PaymentMethodName
select @DuesCategoryID=DuesCategoryID from DuesCategory where DuesCategoryName=@catcde
WHILE (@@FETCH_STATUS <>-1)
BEGIN
select @DuesCategoryID= case @catcde when '@DuesCategoryName1' then 'Per Diem'
when '@DuesCategoryName2' then 'Retired.'
when '@DuesCategoryName3' then 'On Leave.'
when '@DuesCategoryName4' then 'No Dues.'
when '@DuesCategoryName5' then 'Agency Fee Payer.'
when '@DuesCategoryName6' then 'Student.'
when '@DuesCategoryName7' then 'Full Dues.'
when '@DuesCategoryName8' then '3/4 Dues.'
when '@DuesCategoryName9' then '1/2 Dues.'
when '@DuesCategoryName10' then '1/4 Dues.'
when '@DuesCategoryName11' then '1/8 Dues.'
when ' ' then null
end
select @DeactivateReasonID=DeactivateReasonID,@Deactivate ReasonLocalID= DeactivateReasonLocalID
from DeactivateReasonLocal
where DeactivateReasonLocalName=@rfdlt
select @MaritalStatusID=MaritalStatusID from MaritalStatus where MaritalStatusName=@mstat
select @MemberStatusID=MemberStatusID from MemberStatus where MemberStatusName=@Status
select @SubjectID=SubjectID from Subject where SubjectName=@tenuresub
select @PoliticalPartyID=PoliticalPartyID from PoliticalParty where PoliticalPartyName=@polparty
select @LocalUnionID=LocalUnionID,@LocalUnionNbr=LocalUni onNbr from Localunion
select @StateTerritoryId=StateTerritoryId,@CountryId=Coun tryId from Address where StateCode=@State
Insert into Address values (newid(),@Address1,@Address2,@City,null,@State,@zi p,
null,null,'USA',@StateTerritoryId,@CountryId,NULL, 0,@county,NULL,0,default,default,
default,default,default,@LocalUnionNbr,@LocalUnion Id)
Select @AddressId=AddressId from Address where uid=@@Identity
select @LocalJobClassId=LocalJobClassId from LocalJobClass where LocalJobClassCode=@classcode
---- Inserting Individual rows
insert into individual values (
newid(), @soc, null, @last,@First, @mi,null,CONVERT(DATETIME,@bdate),@PrefixId,@sex,
null, 1, @MaritalStatusId,null,null,0,null,null,null, null,@WorksiteId,null,
@JobClassId,@LocalJobClassId, CONVERT(DATETIME,@STARTDATE), null, null, CONVERT(DATETIME,@seniordate), null, @location,@deptcode,
null,null, 0, @condst, @regvoter, null, @sendst, @rschd,@asmdst, @PolActiv,@PoliticalPartyId,
@precinct,null, null, 0, @SubjectId, null,default, 0,null, @Hometel,null, @Worktel,null,
@MemberStatusId, null,null, @Email,null,@ChapterId,@DuesCategoryId,
null, @AddressId, CONVERT(DATETIME,@CEFDATE),0 , NULL,@PaymentMethodId,@DUESPTHRU,
NULL,CONVERT(DATETIME,@InitDate), @DuesCategoryLocalId,null,@DeactivateReasonId,
null, null, @LocalUnionNbr, null, null, @LocalUnionId, null, null, 0, null,
null, @WorkFax, null, null, null, null, @DeactivateReasonLocalId, null,
null, null, null, null,null,null,null,null,null,null,null,null,null, null,
null,null,null,null, null,null,null,null,null,null,default,default,defa ult,default,
default,@EmployerId, null, null, null,null,null,null,null,null,default,default,
default,default,default,default,default,default)
select @IndividualId=IndividualId from Individual where LastName=@Last and FirstName=@first and
MiddleName=@mi and ssn=@soc and dob=@bdate and Gender=@sex
insert into COPE values (newid(),null,@IndividualId,null,@votecope,convert (datetime,@votedate),
default,default,default,default,default,@LocalUnio nNbr,@LocalUnionId)
Select @prefixId=null
fetch from cur_individual into @last,@first, @mi,@address1,@address2,@city,@state,
@zip,@bdate,@sex,@soc,@mstat,@depnum,@hometel , @status , @sefdte ,
@catcde , @cefdate , @poscd , @poedte ,
@rfdlt , @duespthru , @paydeduc ,
@classcode ,@location ,@deptcode ,
@tenuresub ,@ocertsub ,@bassalary ,@salarystep ,
@salarycol ,@startdate ,@initdate ,
@seniordate ,@worktel ,@votecope ,@votedate ,
@voteded ,@polparty ,@polactiv ,
@precinct ,@condst ,@sendst ,
@asmdst ,@rschd ,@loccode ,@extra1 ,
@extra2 ,@extra3 ,@extra4 ,@extra5 ,
@extra6 ,@extra7 ,@extra8 ,@extra9 ,
@extra10 ,@access ,@pctype ,@os ,
@pclocation ,@internet ,@email ,
@show ,@county ,@localname ,@localnum ,
@bargin ,@offtitle2 ,@offtitle ,
@regvoter ,@reshigh ,@reselem ,
@resunit ,@rescollege ,@employer ,
@empnum ,@workfax ,@offtitle3 ,
@work_phone ,@const_code ,@class_titl ,
@faxnumber ,@constit
END
CLOSE CUR_individual
DEALLOCATE CUR_individual
END
update individual set homeaddressid=addressid where addressid is not null -- set default address as home
Thanks
View 2 Replies
View Related
Nov 13, 2006
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
View 3 Replies
View Related
