Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help with CASE statement syntax 1

Status
Not open for further replies.
LMGroup

LMGroup

MIS
Apr 10, 2006
85
CA
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!
 
Sort by date Sort by votes
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.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Andrzejek
  • Locked
  • Question
Case sensitive request 1
Replies
3
Views
123
Chris Miller
Chris Miller
grnzbra
  • Locked
  • Question
Create Function Within IF statement. 1
Replies
4
Views
488
mikrom
mikrom
f0rg3tfu1
  • Locked
  • Question
PLSQL Package Help
Replies
0
Views
212
f0rg3tfu1
f0rg3tfu1
peac3
  • Locked
  • Question
split one row into multiple rows... help pls 2
Replies
12
Views
535
Chris Miller
Chris Miller
gussy81
  • Locked
  • Question
Decimal Datatype with negative value
Replies
5
Views
646
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top