Stored Procedure unable to print using sum with case statement

[mahalirajesh]

Hi Friend

The commented text getting error, i unable to print the quiery.
PLs help me how to use SUM with Case statement.

pls check the commented code.,

--drop PROCEDURE dbo.CCR_KPI

--CREATE PROCEDURE dbo.CCR_KPI

--@PrDate Varchar(10),
--@EpFilter Varchar(3),
--@Level Varchar(4)

--As
Declare @PrDate Varchar(10)
Declare @EpFilter Varchar(3)
Declare @Level Varchar(4)
set @PrDate='05/05/2008'
Set @EpFilter=''
set @Level=''

Declare @PreDate as varchar(10)
Declare @PreMonth as varchar(10)
Declare @PreYear as varchar(10)
Declare @PreDayName as varchar(10)
Declare @PreNoDays as  varchar(10)
Declare @PreMonthSt as  varchar(10)
Declare @PreMonthEnd as  varchar(10)

Declare @sDissID as Varchar(10)
Declare @sCollID as Varchar(10)
Declare @strSQL as varchar(8000)

Declare @1weekName as varchar(10)
Declare @2weekName as varchar(10)
Declare @3weekName as varchar(10)
Declare @4weekName as varchar(10)
Declare @5weekName as varchar(10)
Declare @6weekName as varchar(10)

Declare @1weekSt as varchar(10)
Declare @2weekSt as varchar(10)
Declare @3weekSt as varchar(10)
Declare @4weekSt as varchar(10)
Declare @5weekSt as varchar(10)
Declare @6weekSt as varchar(10)

Declare @1weekEnd as varchar(10)
Declare @2weekEnd as varchar(10)
Declare @3weekEnd as varchar(10)
Declare @4weekEnd as varchar(10)
Declare @5weekEnd as varchar(10)
Declare @6weekEnd as varchar(10)
DECLARE @sQry AS VARCHAR(8000)
DECLARE @strBatchPath AS VARCHAR(50)

SET @PreMonth  = Month(@PreMonth)
SET @PreYear  = Year(@PreMonth) 
SET @PreDate  = @PreMonth + '/01/' +Year(@PreMonth) 

SET @PreNoDays =DAY(DATEADD (m, 1, DATEADD (d, 1 - DAY(@PreDate), @PreDate)) - 1)
SET @PreDayName =  DATEPART(dw, @PreYear) 

SET @PreMonthSt  = @PreMonth + '/01/' +Year(@PreMonth) 

SET @PreMonthEnd  = @PreMonth + '/'+ @PreNoDays +'/' +Year(@PreMonth) 

SET @strBatchPath ='[npAPR-Filter]'

		Set @1weekName ='1'
		Set @1weekSt= @PreMonth + '/01/' + @PreYear
		Set @1weekEnd= ''
		Set @2weekName ='2-8'
		Set @2weekSt= @PreMonth + '/02/' + @PreYear
		Set @2weekEnd= @PreMonth + '/08/' + @PreYear
		Set @3weekName ='9-15'
		Set @3weekSt= @PreMonth + '/09/' + @PreYear
		Set @3weekEnd= @PreMonth + '/15/' + @PreYear
		Set @4weekName ='16-22'
		Set @4weekSt= @PreMonth + '/16/' + @PreYear
		Set @4weekEnd= @PreMonth + '/22/' + @PreYear
		if @PreNoDays >= 29		
			begin
				Set @5weekName ='23-29'
				Set @5weekSt= @PreMonth + '/01/' + @PreYear
				Set @5weekEnd= @PreMonth + '/29/' + @PreYear
			
			end
			if @PreNoDays > 29		
			begin
				Set @6weekName ='30-'+@PreNoDays
				Set @6weekSt= @PreMonth + '/30/' + @PreYear
				Set @6weekEnd= @PreMonth + '/'+ @PreNoDays +'/' + @PreYear
			end
		else
			begin
				Set @5weekName ='23-'+@PreNoDays
				Set @5weekSt= @PreMonth + '/23/' + @PreYear
				Set @5weekEnd= @PreMonth + '/'+ @PreNoDays +'/' + @PreYear			
			end		


	Set @stRSQl = " SELECT batch AS Batch, dbm_Collector as Collector, Count(dbm_mCard) AS Files, "
	

--	if @1weekEnd is not null
--		Begin
--			Set @stRSQl = @stRSQl + "	Sum(case when [date] between " + @1weekSt + " And " + @1weekEnd + " then [amount] else 0 end) AS [1st-week " + @1weekName + "], "
--		end
--	else
--		Begin
--			Set @stRSQl = @stRSQl + "	Sum(case when [date] >= " + @1weekSt + " then [amount] else 0 end) AS [1st-week "  + @1weekName + "], "
--		end
--	Set @stRSQl = @stRSQl + "	Sum(case when [date] between "  + @2weekSt +  " And "  + @2weekEnd +  " then [amount] else 0 end) AS [2nd-Week "  + @2weekName +  "], "
--	Set @stRSQl = @stRSQl  + "Sum(case when [date] between "  + @3weekSt +  " And "  + @3weekEnd +  " then [amount] else 0 end) AS [3rd-Week "  + @3weekName +  "], "
--	Set @stRSQl = @stRSQl + "	Sum(case when [date] between "  + @4weekSt +  " And "  + @4weekEnd +  " then [amount] else 0 end) AS [4th-Week "  + @4weekName +  "], "
--	Set @stRSQl = @stRSQl + "	Sum(case when [date] between "  + @5weekSt +  " And "  + @5weekEnd +  " then [amount] else 0 end) AS [5th-Week "  + @5weekName +  "], "
--	Set @stRSQl = @stRSQl + "	Sum(["  +  @strBatchPath  +  ".amount) AS [Total PayAmt] "
	
	Set @stRSQl = @stRSQl + "	FROM " + @strBatchPath 
	Set @stRSQl = @stRSQl + "	WHERE (((" + @strBatchPath + ".ep)= '"+ "yes"+ "')) "
	--Set @stRSQl = @stRSQl + "	AND ([date] between '"+ @PreMonthSt +"' And '"+ @PreMonthEnd +"')"
	if @sDissID is not null
		Begin
			Set @stRSQl = @stRSQl + "	AND " + @strBatchPath + ".batch='"+ @sDissID+"'"
		end
	if @sCollID is not null
		Begin
			Set @stRSQl = @stRSQl + "	AND " + @strBatchPath + ".dbm_Collector='"+ @sCollID+"'"
		end
	Set @stRSQl = @stRSQl + "	GROUP BY " + @strBatchPath + ".batch, " + @strBatchPath + ".dbm_Collector "
	Set @stRSQl = @stRSQl + "	ORDER BY " + @strBatchPath + ".batch,  "
	Set @stRSQl = @stRSQl + "	Sum(" + @strBatchPath + ".amount) DESC "

-- (@stRSQl)
print (@stRSQl)	

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

[Hack]

Split from an old thread into its own thread.

[Slope]

First, I don't think SQL likes double-quotes. Next, you probably need the date in single quotes after the string is evaluated.

Set @stRSQl = @stRSQl + ' Sum(case when [date] between ''' + @2weekSt + ''' And ''' + @2weekEnd + ''' then [amount] else 0 end) AS [2nd-Week ' + @2weekName + '], '