Stored Procedure unable to print using sum with case statement


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Stored Procedure unable to print using sum with case statement

  1. #1
    Join Date
    May 2008
    Posts
    1

    Stored Procedure unable to print using sum with case statement

    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.,
    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

  2. #2
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Split from an old thread into its own thread.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  3. #3
    Join Date
    Feb 2008
    Posts
    162
    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 + '], '
    It's not what you don't know that gets you in trouble. It's what you know that just ain't so. -Mark Twain

Similar Threads

  1. Databind Dropdown Asp.net
    By geo039 in forum ASP.NET
    Replies: 3
    Last Post: 08-06-2007, 04:46 PM
  2. errors with some varibles
    By ELHEK in forum Java
    Replies: 1
    Last Post: 10-01-2006, 06:08 PM
  3. Replies: 3
    Last Post: 04-26-2005, 05:49 PM
  4. Stored Procedure SQL Server 2000
    By Michael in forum Database
    Replies: 2
    Last Post: 03-17-2005, 12:07 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center