quote:

---

Originally posted by cf46sr

Hello,

Below is a procedure that simply totals sales and credits for a certain product catagory for the current month. When the procedure is run , I get 3 results, Totalsales, Totalcredits and Total(Totalsales - Totalcredits). All I want is the final total. How do I 'suppress' the Totalsales & Totalcredits.


Feel free to suggest improvements in general, if you wish

Thank you




GO
IF OBJECT_ID('dbo.usp_MonthlySales') IS NOT NULL
DROP PROC dbo.usp_MonthlySales

GO
Create Procedure usp_MonthlySales
@AcctID varchar(10),
@Category varchar(10)

As

Declare @Month varchar(2)
Declare @Year varchar(4)
Declare @YearMonth varchar(6)
Declare @TotalSales Int
Declare @TotalCredits Int
Declare @Total Int

Set @Year = LTrim(STR(Year(GETDATE())))
Set @Month = Ltrim(Month(GETDATE()))
If Len(@Month)= 1
Begin
Set @YearMonth = @Year + '0' + @Month
End
Else
Begin
Set @YearMonth = @Year + @Month
End


If @Category = 'PAB'

Begin
Set @Totalsales = (SELECT SUM(OEINVD.TBASE1)
FROM OEINVD INNER JOIN
OEINVH ON OEINVD.INVUNIQ = OEINVH.INVUNIQ
where Left(OEINVH.INVDATE,6) = @YearMonth
and (OEINVD.CATEGORY = 'C100' or OEINVD.CATEGORY = 'C500' )
and OEINVH.CUSTOMER = @AcctID)


Set @TotalCredits = (SELECT SUM(OECRDD.TBASE1)
FROM OECRDD INNER JOIN
OECRDH ON OECRDD.CRDUNIQ = OECRDH.CRDUNIQ
where Left(OECRDH.INVDATE,6) = @YearMonth
and (OECRDD.CATEGORY = 'C100' or OECRDD.CATEGORY = 'C200' )
and OECRDH.CUSTOMER = @AcctID)


Select ISNULL(@TotalSales,0)
Select ISNULL(@TotalCredits,0)

Select ISNULL(@TotalSales,0) - ISNULL(@TotalCredits,0) As 'Total'

End



Else If @Category = 'COM'
Begin
Set @TotalSales = (SELECT SUM(OEINVD.TBASE1)
FROM OEINVD INNER JOIN
OEINVH ON OEINVD.INVUNIQ = OEINVH.INVUNIQ
where Left(OEINVH.INVDATE,6) = @YearMonth
and (OEINVD.CATEGORY = 'C400' )
and OEINVH.CUSTOMER = @AcctID)


Set @TotalCredits = (SELECT SUM(OECRDD.TBASE1)
FROM OECRDD INNER JOIN
OECRDH ON OECRDD.CRDUNIQ = OECRDH.CRDUNIQ
where Left(OECRDH.INVDATE,6) = @YearMonth
and (OECRDD.CATEGORY = 'C400' )
and OECRDH.CUSTOMER = @AcctID)


Select ISNULL(@TotalSales,0)
Select ISNULL(@TotalCredits,0)

Select ISNULL(@TotalSales,0) - ISNULL(@TotalCredits,0) As 'Total'


End



Else If @Category = 'SLE'
Begin
Set @TotalSales = (SELECT SUM(OEINVD.TBASE1)
FROM OEINVD INNER JOIN
OEINVH ON OEINVD.INVUNIQ = OEINVH.INVUNIQ
where Left(OEINVH.INVDATE,6) = @YearMonth
and (OEINVD.CATEGORY = 'C500' )
and OEINVH.CUSTOMER = @AcctID)


Set @TotalCredits = (SELECT SUM(OECRDD.TBASE1)
FROM OECRDD INNER JOIN
OECRDH ON OECRDD.CRDUNIQ = OECRDH.CRDUNIQ
where Left(OECRDH.INVDATE,6) = @YearMonth
and (OECRDD.CATEGORY = 'C500' )
and OECRDH.CUSTOMER = @AcctID)


Select ISNULL(@TotalSales,0)
Select ISNULL(@TotalCredits,0)

Select ISNULL(@TotalSales,0) - ISNULL(@TotalCredits,0) As 'Total'


End




Go

---

GO
IF OBJECT_ID('dbo.usp_MonthlySales') IS NOT NULL
	DROP PROC dbo.usp_MonthlySales

GO
Create Procedure usp_MonthlySales
(
	@AcctID varchar(10),
	@Category varchar(10)
)
As
Begin 
	Declare @YearMonth varchar(6)
	Declare @TotalSales Int
	Declare @TotalCredits Int
	Declare @Total Int

	-- Initialize all the variables for the months 

	Select 
	           @Year = Case When 
				Len(A.Mnths) = 1 Then A.Yrs + '0' + A.Mnths
			       Else 
				A.Yrs + A.Mnths
			 End 
	From 
	(
		Select LTrim(STR(Year(GETDATE()))) As Yrs ,Ltrim(Month(GETDATE())) As Mnths 
	) A 	
	
	
	--Lets Calculate Total Sales.. 

	Select @TotalSales = Sum (
				   Case When 
					       (OEINVD.CATEGORY = 'C100' or OEINVD.CATEGORY = 'C500' )  And  (@Category = 'PAB') 
					Then 
						OEINVD.TBASE1
					When 
					        (OEINVD.CATEGORY = 'C400' ) And (@Category = 'COM')
					Then 
						OEINVD.TBASE1
					When 
					         (OEINVD.CATEGORY = 'C500' ) And (@Category = 'SLE')
					Then 
						OEINVD.TBASE1
					 Else
						0 
				   End 
				) 
	FROM  OEINVD INNER JOIN
	           OEINVH ON OEINVD.INVUNIQ = OEINVH.INVUNIQ
	where
	             Left(OEINVH.INVDATE,6) = @YearMonth And OEINVH.CUSTOMER = @AcctID

	--Now Total Credits 

	Select @TotalCredits  = Sum (
				       Case When
						 (OECRDD.CATEGORY = 'C100' or OECRDD.CATEGORY = 'C200' ) And (@Category = 'PAB')
					  Then 
						OECRDD.TBASE1
					  When 
						 (OECRDD.CATEGORY = 'C400' ) And  (@Category = 'COM')	
					  Then 
						OECRDD.TBASE1
					  When  
						(OECRDD.CATEGORY = 'C500' ) And  (@Category = 'SLE')
					  Then 
						OECRDD.TBASE1
					  Else 
						0 
				        End 
				     ) 
	From  OECRDD INNER JOIN
	          OECRDH ON OECRDD.CRDUNIQ = OECRDH.CRDUNIQ
	Where 
	         Left(OECRDH.INVDATE,6) = @YearMonth And  OECRDH.CUSTOMER = @AcctID
	
	Select COALESCE (@TotalSales,0) - COALESCE(@TotalCredits,0) As 'Total'
	
	
End 

Go