|
I'm trying to modify existing code supplied by the software company to change a canned stored procedure in SQL Server 2005. I'm using Mgmt Studio. I need to modify the code so that any records that contain the word 'Helper' total the charges and hours separately from the other records. To do this, I added 2 new fields. I'm having difficulty with the CASE statement in the SELECT statement near the bottom. I need to change the other CASE statement below it too, but I thought it would be a good idea to get the first one working before I tackled it. I'm including all the code for the original version and the modifications I've made (I've removed chunks of the code that will not be needed in the modified version). Original: CODE/****** Object: StoredProcedure [dbo].[csp_labRepDept] Script Date: 05/23/2012 09:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--/* ***** Source Level:1.16 *****/
--$Revision: 1.16 $
/****** Object: Stored Procedure dbo.labRepDept
-----------------------------------------------------------------------
Returns data for Labor Report - Department Detail and Summary
-----------------------------------------------------------------------
5/23/12 - GTK generated copy of standard SP code for Lowe-Martin
*/
CREATE proc [dbo].[csp_labRepDept]
@ReportType integer,
@SelLine varchar(6000),
@ShiftSort integer,
@YTDStart datetime,
@QTDStart datetime,
@PeriodStart datetime,
@PeriodEnd datetime,
@RepDateSel integer
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #ProdData
(LabCCN int, LabShift int, Hrs decimal (9,2), Cost decimal(15,2), Qty decimal(15,2), IsYTD int, IsQTD int, IsCur int)
CREATE TABLE #ProdSum (LabCCN int, LCCDesc varchar(40) COLLATE database_default, DeptNum int,
CCNum int, Standard decimal(9,2), AIC decimal(15,2), LabShift int,
TotHrs decimal(15,2), TotCost decimal(15,2), TotQty decimal(15,2), IsYTD int, IsQTD int, IsCur int)
declare @EmpDet tinyint, @EmpSum tinyint, @DeptDet tinyint, @DeptSum tinyint
SET @EmpDet = 1
SET @EmpSum = 2
SET @DeptDet = 3
SET @DeptSum = 4
declare @DeptBrk int
SELECT @DeptBrk = dbo.zlaGetNParm(10160)
IF @DeptBrk = 0 SET @DeptBrk = 10000 --Don't allow a zero value
declare @SQLLine varchar(7000)
declare @BeginDate datetime
declare @ZeroDate as datetime Set @ZeroDate = '1900-01-01'
declare @NtFnd as char(17) Set @NtFnd = '<<< Not Found >>>'
declare @ReWorkCC as int Set @ReWorkCC = 70
declare @NonChargeCC as int Set @NonChargeCC = 71
if @RepDateSel = 3 -- YTD
SET @BeginDate = @YTDStart
else if @RepDateSel = 2 --QTD
SET @BeginDate = @QTDStart
else if @RepDateSel = 1 --Cur
SET @BeginDate = @PeriodStart
else -- all - get the first
begin
SET @BeginDate = @YTDStart
IF @QTDStart < @BeginDate and @QTDStart <> @ZeroDate SET @BeginDate = @QTDStart
IF @PeriodStart < @BeginDate or @BeginDate = @ZeroDate SET @BeginDate = @PeriodStart
end
-- Set them to inoperative dates, if not used
if @QTDStart = @ZeroDate
SET @QTDStart = @PeriodStart
if @YTDStart = @ZeroDate
SET @YTDStart = @QTDStart
--Pick up Department names
declare @DeptTable table (DeptN int, DeptName varchar(40) COLLATE database_default)
insert @DeptTable
SELECT LCCN, LCCDescription FROM dbo.LaborCostCntr WHERE (LCCN/@DeptBrk)* @DeptBrk = LCCN
--Pick up Cost Center names
declare @CCTable table (CCN int, CCName varchar(40) COLLATE database_default, CCStd decimal(15,2), CCRate decimal(9,2))
insert @CCTable
SELECT LCCN, LCCDescription, Standard, AICCostRate FROM dbo.LaborCostCntr WHERE (LCCN/100)* 100 = LCCN
if @ReportType = @DeptSum or @ReportType = @DeptDet
begin
--Collect Labor
SET @SQLLine = 'INSERT INTO #ProdData SELECT '
SET @SQLLine = @SQLLine + ' CASE WHEN Prefix in (2,5,8) THEN floor(LCCN/100)*100 + ' + cast(@ReworkCC as char(2)) + ' ELSE LCCN END,'
if @ShiftSort = 1
SET @SQLLine = @SQLLine + 'Shift,'
else
SET @SQLLine = @SQLLine + '0,'
SET @SQLLine = @SQLLine + 'Hours, AICCost, LaborQuantity, '
SET @SQLLine = @SQLLine + 'CASE WHEN UpdateDate >= ''' + cast(@YTDStart as varchar(20)) + ''' and UpdateDate <= ''' + cast(@PeriodEnd as varchar(20)) + ''' THEN 1 ELSE 0 END,'
SET @SQLLine = @SQLLine + 'CASE WHEN UpdateDate >=''' + cast(@QTDStart as varchar(20)) + ''' and UpdateDate <= ''' + cast(@PeriodEnd as varchar(20)) + ''' THEN 1 ELSE 0 END,'
SET @SQLLine = @SQLLine + 'CASE WHEN UpdateDate >=''' + cast(@PeriodStart as varchar(20)) + ''' and UpdateDate <=''' + cast(@PeriodEnd as varchar(20)) + ''' THEN 1 ELSE 0 END'
SET @SQLLine = @SQLLine + ' FROM dbo.JobLabor '
SET @SQLLine = @SQLLine + ' WHERE UpdateDate >=''' + cast(@BeginDate as varchar(20)) + ''' AND UpdateDate <=''' + cast(@PeriodEnd as varchar(20)) + ''''
SET @SQLLine = @SQLLine + ' AND LCCN > 2 '
if len(@SelLine) > 0
SET @SQLLine = @SQLLine + ' AND ' + @SelLine
EXEC (@SQLLine)
-- Get DMI data from Machine Cards
SET @SQLLine = 'INSERT INTO #ProdData SELECT '
SET @SQLLine = @SQLLine + ' CASE WHEN Prefix in (2,5,8) THEN floor(MainEquipN)*100 + ' + cast(@ReworkCC as char(2))
SET @SQLLine = @SQLLine + ' WHEN OperationCodes.NonCh1Chg0 = 1 THEN floor(MainEquipN)*100 + ' + cast(@NonChargeCC as char(2))
SET @SQLLine = @SQLLine + ' ELSE MainEquipN*100 END,'
if @ShiftSort = 1
SET @SQLLine = @SQLLine + 'Shift,'
else
SET @SQLLine = @SQLLine + '0,'
SET @SQLLine = @SQLLine + 'ElapsedHours, AICCost, NetCount, '
SET @SQLLine = @SQLLine + 'CASE WHEN UpdateDate >= ''' + cast(@YTDStart as varchar(20)) + ''' and UpdateDate <= ''' + cast(@PeriodEnd as varchar(20)) + ''' THEN 1 ELSE 0 END,'
SET @SQLLine = @SQLLine + 'CASE WHEN UpdateDate >=''' + cast(@QTDStart as varchar(20)) + ''' and UpdateDate <= ''' + cast(@PeriodEnd as varchar(20)) + ''' THEN 1 ELSE 0 END,'
SET @SQLLine = @SQLLine + 'CASE WHEN UpdateDate >=''' + cast(@PeriodStart as varchar(20)) + ''' and UpdateDate <=''' + cast(@PeriodEnd as varchar(20)) + ''' THEN 1 ELSE 0 END'
SET @SQLLine = @SQLLine + ' FROM dbo.MachineCards JOIN Equipment ON ConfigurationN = EquipNumber '
SET @SQLLine = @SQLLine + ' JOIN dbo.OperationCodes ON MachineCards.OperationCode = OperationCodes.OperationCode'
SET @SQLLine = @SQLLine + ' WHERE (ElapsedHours > 0 OR NetCount > 0) AND UpdateDate >=''' + cast(@BeginDate as varchar(20)) + ''' AND UpdateDate <=''' + cast(@PeriodEnd as varchar(20)) + ''''
if len(@SelLine) > 0
begin
SET @SelLine = Replace(@SelLine, 'LCCN','MainEquipN*100')
SET @SelLine = Replace(@SelLine, 'EmployeeN','Employee')
SET @SQLLine = @SQLLine + ' AND ' + @SelLine
end
EXECUTE ( @SQLLine)
--Now return the report data
--Summarize data by LCC, etc.
INSERT #ProdSum
SELECT LabCCN, isnull(LCCDescription, CASE LabCCN %100 WHEN @ReworkCC THEN 'Rework'
WHEN @NonChargeCC THEN 'Non Chargeable' ELSE @NtFnd END) LCCDesc,
dbo.lccDept(LabCCN, @DeptBrk), dbo.lccCostCntr(LabCCN), isnull(Standard,0), isnull(AICCostRate,0), LabShift,
isnull(Sum(Hrs),0) TotHrs, isnull(Sum(Cost),0) TotCost, isnull(Sum(Qty),0) TotQty, IsYTD, IsQTD, IsCur
FROM #ProdData LEFT OUTER JOIN dbo.LaborCostCntr ON #ProdData.LabCCN = LaborCostCntr.LCCN
GROUP BY LabShift, LabCCN, LCCDescription, Standard, AICCostRate, IsYTD, IsQTD, IsCur
if @ReportType = @DeptDet
begin
--Now send out data with names attached
SELECT LabCCN, LCCDesc, DeptNum, isnull(DeptName, @NtFnd) DeptName,
CCNum, isnull(CCName, @NtFnd) CCName,
isnull(Standard,0) Standard, LabShift,
isnull(Sum(TotHrs * IsCur),0) TotHrs,
isnull(Sum(TotQty * IsCur),0) TotQty,
isnull(Sum(TotHrs * IsQTD),0) QTotHrs,
isnull(Sum(TotQty * IsQTD),0) QTotQty,
isnull(Sum(TotHrs * IsYTD),0) YTotHrs,
isnull(Sum(TotQty * IsYTD),0) YTotQty
FROM #ProdSum LEFT OUTER JOIN @CCTable ON CCNum = CCN
LEFT OUTER JOIN @DeptTable ON DeptN = DeptNum
GROUP BY LabCCN, LCCDesc, DeptNum, DeptName, CCNum, CCName, Standard, LabShift
end
else
SELECT LabShift, DeptNum, isnull(DeptName, @NtFnd) DeptName, CCNum,
isnull(CCName, @NtFnd) CCName, isnull(CCStd,0) CCStd, isnull(CCRate, 0) CCRate,
Sum(TotHrs * (CASE WHEN (LabCCN % 100) >= @ReWorkCC THEN 0 ELSE 1 END)* IsCur) ChgHrs,
Sum(TotHrs * IsCur) TotHrs,
Sum(TotCost * (CASE WHEN (LabCCN % 100) >= @ReWorkCC THEN 0 ELSE 1 END)* IsCur) Chg,
Sum(TotHrs * (CASE WHEN (isnull(Standard,0) = 0) THEN 0 ELSE 1 END)* IsCur) HrsWStd,
Sum(dbo.StdLabHrs(TotQty, isnull(Standard,0))* IsCur) StdLabHrs,
Sum(TotHrs * (CASE WHEN (LabCCN % 100) >= @ReWorkCC THEN 0 ELSE 1 END)* IsQTD) QChgHrs,
Sum(TotHrs * IsQTD) QTotHrs,
Sum(TotCost * (CASE WHEN (LabCCN % 100) >= @ReWorkCC THEN 0 ELSE 1 END)* IsQTD) QChg,
Sum(TotHrs * (CASE WHEN (isnull(Standard,0) = 0) THEN 0 ELSE 1 END)* IsQTD) QHrsWStd,
Sum(dbo.StdLabHrs(TotQty, isnull(Standard,0))* IsQTD) QStdLabHrs,
Sum(TotHrs * (CASE WHEN (LabCCN % 100) >= @ReWorkCC THEN 0 ELSE 1 END)* IsYTD) YChgHrs,
Sum(TotHrs ) YTotHrs,
Sum(TotCost * (CASE WHEN (LabCCN % 100) >= @ReWorkCC THEN 0 ELSE 1 END)* IsYTD) YChg,
Sum(TotHrs * (CASE WHEN (isnull(Standard,0) = 0) THEN 0 ELSE 1 END)* IsYTD) YHrsWStd,
Sum(dbo.StdLabHrs(TotQty, isnull(Standard,0))* IsYTD) YStdLabHrs
FROM #ProdSum LEFT OUTER JOIN @CCTable ON CCNum = CCN
LEFT OUTER JOIN @DeptTable ON DeptN = DeptNum
GROUP BY LabShift, DeptNum, DeptName, CCNum, CCName, CCStd, CCRate
order by labshift, deptnum, ccnum
end
END
GO Modified verion: CODE/****** Object: StoredProcedure [dbo].[csp_labRepDept] Script Date: 05/23/2012 09:06:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--/* ***** Source Level:1.16 *****/
--$Revision: 1.16 $
/****** Object: Stored Procedure dbo.labRepDept
-----------------------------------------------------------------------
Returns data for Labor Report - Department Detail and Summary
-----------------------------------------------------------------------
5/23/12 - GTK generated copy of standard SP code for Lowe-Martin
5/24/12 - LLM modified the code to remove Helper LCCs from the Cost Centres and total them separately
*/
CREATE proc [dbo].[csp_LMlabRepDept]
@ReportType integer,
@SelLine varchar(6000),
@ShiftSort integer,
@YTDStart datetime,
@QTDStart datetime,
@PeriodStart datetime,
@PeriodEnd datetime,
@RepDateSel integer
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #ProdData
(LabCCN int, LabShift int, Hrs decimal (9,2), Cost decimal(15,2), Qty decimal(15,2), IsYTD int, IsQTD int, IsCur int)
CREATE TABLE #ProdSum (LabCCN int, LCCDesc varchar(40) COLLATE database_default, DeptNum int,
CCNum int, Standard decimal(9,2), AIC decimal(15,2), LabShift int,
TotHrs decimal(15,2), TotCost decimal(15,2), TotQty decimal(15,2), IsYTD int, IsQTD int, IsCur int, HlpHrs decimal(15,2), HlpCost decimal(15,2))
declare @EmpDet tinyint, @EmpSum tinyint, @DeptDet tinyint, @DeptSum tinyint
SET @EmpDet = 1
SET @EmpSum = 2
SET @DeptDet = 3
SET @DeptSum = 4
declare @DeptBrk int
SELECT @DeptBrk = dbo.zlaGetNParm(10160)
IF @DeptBrk = 0 SET @DeptBrk = 10000 --Don't allow a zero value
declare @SQLLine varchar(7000)
declare @BeginDate datetime
declare @ZeroDate as datetime Set @ZeroDate = '1900-01-01'
declare @NtFnd as char(17) Set @NtFnd = '<<< Not Found >>>'
declare @ReWorkCC as int Set @ReWorkCC = 70
declare @NonChargeCC as int Set @NonChargeCC = 71
SET @RepDateSel = 1 --Cur
SET @BeginDate = @PeriodStart
-- Set them to inoperative dates, if not used
if @QTDStart = @ZeroDate
SET @QTDStart = @PeriodStart
if @YTDStart = @ZeroDate
SET @YTDStart = @QTDStart
--Pick up Department names
declare @DeptTable table (DeptN int, DeptName varchar(40) COLLATE database_default)
insert @DeptTable
SELECT LCCN, LCCDescription FROM dbo.LaborCostCntr WHERE (LCCN/@DeptBrk)* @DeptBrk = LCCN
--Pick up Cost Center names
declare @CCTable table (CCN int, CCName varchar(40) COLLATE database_default, CCStd decimal(15,2), CCRate decimal(9,2))
insert @CCTable
SELECT LCCN, LCCDescription, Standard, AICCostRate FROM dbo.LaborCostCntr WHERE (LCCN/100)* 100 = LCCN
if @ReportType = @DeptSum or @ReportType = @DeptDet
begin
--Collect Labor
SET @SQLLine = 'INSERT INTO #ProdData SELECT '
SET @SQLLine = @SQLLine + ' CASE WHEN Prefix in (2,5,8) THEN floor(LCCN/100)*100 + ' + cast(@ReworkCC as char(2)) + ' ELSE LCCN END,'
if @ShiftSort = 1
SET @SQLLine = @SQLLine + 'Shift,'
else
SET @SQLLine = @SQLLine + '0,'
SET @SQLLine = @SQLLine + 'Hours, AICCost, LaborQuantity, '
SET @SQLLine = @SQLLine + 'CASE WHEN UpdateDate >= ''' + cast(@YTDStart as varchar(20)) + ''' and UpdateDate <= ''' + cast(@PeriodEnd as varchar(20)) + ''' THEN 1 ELSE 0 END,'
SET @SQLLine = @SQLLine + 'CASE WHEN UpdateDate >=''' + cast(@QTDStart as varchar(20)) + ''' and UpdateDate <= ''' + cast(@PeriodEnd as varchar(20)) + ''' THEN 1 ELSE 0 END,'
SET @SQLLine = @SQLLine + 'CASE WHEN UpdateDate >=''' + cast(@PeriodStart as varchar(20)) + ''' and UpdateDate <=''' + cast(@PeriodEnd as varchar(20)) + ''' THEN 1 ELSE 0 END'
SET @SQLLine = @SQLLine + ' FROM dbo.JobLabor '
SET @SQLLine = @SQLLine + ' WHERE UpdateDate >=''' + cast(@BeginDate as varchar(20)) + ''' AND UpdateDate <=''' + cast(@PeriodEnd as varchar(20)) + ''''
SET @SQLLine = @SQLLine + ' AND LCCN > 2 '
if len(@SelLine) > 0
SET @SQLLine = @SQLLine + ' AND ' + @SelLine
EXEC (@SQLLine)
--Now return the report data
--Summarize data by LCC, etc.
INSERT #ProdSum
SELECT LabCCN, isnull(LCCDescription, CASE LabCCN %100 WHEN @ReworkCC THEN 'Rework'
WHEN @NonChargeCC THEN 'Non Chargeable' ELSE @NtFnd END) LCCDesc,
dbo.lccDept(LabCCN, @DeptBrk), dbo.lccCostCntr(LabCCN), isnull(Standard,0), isnull(AICCostRate,0), LabShift,
isnull(Sum(Hrs),0) TotHrs, isnull(Sum(Cost),0) TotCost, isnull(Sum(Qty),0) TotQty, IsYTD, IsQTD, IsCur, isnull(Sum(Hrs),0) HlpHrs, isnull(Sum(Cost),0) HlpCost
FROM #ProdData LEFT OUTER JOIN dbo.LaborCostCntr ON #ProdData.LabCCN = LaborCostCntr.LCCN
GROUP BY LabShift, LabCCN, LCCDescription, Standard, AICCostRate, IsYTD, IsQTD, IsCur
SELECT LabShift, DeptNum, isnull(DeptName, @NtFnd) DeptName, CCNum,
isnull(CCName, @NtFnd) CCName, isnull(CCStd,0) CCStd, isnull(CCRate, 0) CCRate,
CASE
WHEN CCName not like '%Helper%'
THEN
Sum(TotHrs * (CASE WHEN (LabCCN % 100) >= @ReWorkCC THEN 0 ELSE 1 END)* IsCur) ChgHrs
ELSE
Sum(HlpCost * (CASE WHEN (LabCCN % 100) >= @ReWorkCC THEN 0 ELSE 1 END)* IsCur) HelpChg,
END
Sum(TotHrs * IsCur) TotHrs,
Sum(TotCost * (CASE WHEN (LabCCN % 100) >= @ReWorkCC THEN 0 ELSE 1 END)* IsCur) Chg,
Sum(TotHrs * (CASE WHEN (isnull(Standard,0) = 0) THEN 0 ELSE 1 END)* IsCur) HrsWStd,
Sum(dbo.StdLabHrs(TotQty, isnull(Standard,0))* IsCur) StdLabHrs,
Sum(TotHrs * (CASE WHEN (LabCCN % 100) >= @ReWorkCC THEN 0 ELSE 1 END)* IsQTD) QChgHrs,
Sum(TotHrs * IsQTD) QTotHrs,
Sum(TotCost * (CASE WHEN (LabCCN % 100) >= @ReWorkCC THEN 0 ELSE 1 END)* IsQTD) QChg,
Sum(TotHrs * (CASE WHEN (isnull(Standard,0) = 0) THEN 0 ELSE 1 END)* IsQTD) QHrsWStd,
Sum(dbo.StdLabHrs(TotQty, isnull(Standard,0))* IsQTD) QStdLabHrs,
Sum(TotHrs * (CASE WHEN (LabCCN % 100) >= @ReWorkCC THEN 0 ELSE 1 END)* IsYTD) YChgHrs,
Sum(TotHrs ) YTotHrs,
Sum(TotCost * (CASE WHEN (LabCCN % 100) >= @ReWorkCC THEN 0 ELSE 1 END)* IsYTD) YChg,
Sum(TotHrs * (CASE WHEN (isnull(Standard,0) = 0) THEN 0 ELSE 1 END)* IsYTD) YHrsWStd,
Sum(dbo.StdLabHrs(TotQty, isnull(Standard,0))* IsYTD) YStdLabHrs
FROM #ProdSum LEFT OUTER JOIN @CCTable ON CCNum = CCN
LEFT OUTER JOIN @DeptTable ON DeptN = DeptNum
GROUP BY LabShift, DeptNum, DeptName, CCNum, CCName, CCStd, CCRate
order by labshift, deptnum, ccnum
end
END
GO Thanks in advance! |
|
You can't use one CASE to create two fields CODE...
Sum(CASE WHEN CCName not like '%Helper%'
THEN TotHrs * (CASE WHEN (LabCCN % 100) >= @ReWorkCC THEN 0 ELSE 1 END)* IsCur
ELSE 0 END) AS ChgHrs,
Sum(CASE WHEN CCName LIKE '%Helper%'
THEN HlpCost * (CASE WHEN (LabCCN % 100) >= @ReWorkCC THEN 0 ELSE 1 END)* IsCur
ELSE 0 END) AS HelpChg,
... Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008. |
|
Thank you so much! That fixed it :D |
|
|
 |
Talk To Other Members
Read More Threads Like This One