• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6254
  • Last Modified:

Calculate Total Sum in SQL Stored Procedure

I would like to have a new field that showes the total sum of netsales for all of the records specified in my criteria. Is there a simple way to do this?
Here is what I have so far:
ALTER PROCEDURE spSalesAnalysis
--Exec spSalesAnalysis 2559,2921,0
(
@BegDate as int,
@EndDate as int,
@SalesRep as smallint
)


AS

SELECT c.datenk as fiscaldate, c.fiscalweek, a.salesrepwk, b.salesrepname, b.salesregion, b.salesregionname as regionname,
d.customername as customer, a.salesdatewk, a.customerwk, a.invoice, a.invoiceline, a.itemwk, a.fiscaldatewk, a.unitofissue, a.quantityordered,
a.quantitysold, a.extendedprice, a.extendedpromotions, a.marketingallowances, a.deposits, a.salestax, a.freightservicefees,
a.telxonfees, a.othercharges, a.extendedretail, a.extendedcost, a.costallowances, a.shippingwarehouse, a.customcategorycode, a.customcategorystyle,
a.customcategorydesc, a.source, a.internalcost, a.buyercost, a.creditinvoiceflag, a.commission, a.itemtax,


case when a.salesDateWK between @BegDate and @EndDate then a.extendedprice - a.extendedpromotions + a.Marketingallowances +
a.deposits + a.salestax + a.freightservicefees + a.telxonfees + a.othercharges end as netsales

into #tempSales

from factcustsales a
INNER JOIN DimSalesRep b
     ON a.SalesRepWK = b.SalesRepWK
INNER JOIN DimCalendar c
     ON a.salesdatewk  = c.DateWK
INNER JOIN DimCustomer d
     ON a.CustomerWK = d.CustomerWK

WHERE  
a.salesrepwk = b.salesrepwk and
b.salesregion in ('4') and
d.warehousetransfer = ('No') and
a.creditinvoiceflag <> ('RB')and
a.salesdateWK between @BegDate and @EndDate

select  distinct fiscaldate, invoice, customer, salesrepwk, salesrepname, sum(netsales)as netsales
from #tempsales

group by Salesrepname, fiscaldate, customer, invoice, salesrepwk


order by invoice desc



0
SasDev
Asked:
SasDev
  • 4
  • 2
1 Solution
 
SasDevAuthor Commented:
Sample record set:
fiscaldate                 invoice     customer                         salesrepwk      salesrepname          netsales
10/25/2007 0:00      683915      SUNOCO 16 & GROESBECK      45      DAVID NEIRINCK      108.2
10/26/2007 0:00      683914      SUNOCO Z & L                     127      JOE HASTINGS       108.2
10/24/2007 0:00      683913      SUNOCO GARFIELD                      54            CONNIE ELLSTROM        101.2
10/25/2007 0:00      683912      SUNOCO-DHS ENT., INC.      74      LINDA HUTTON      118.2
10/24/2007 0:00      683911      SUNOCO BADR                     378                KAREN J. KLEPETSANIS      99.2
10/25/2007 0:00      683910      SUNOCO TRACTOR INVESTMENT  127      JOE HASTINGS      108.2
0
 
BrandonGalderisiCommented:
So you want the data you have above.... plus an additional display of netsales accumulated?  Is this an ADHOC stored procedure call where the data is viewed on screen or is it pulled as a recordset into some application?  

You should be able to do a compute sum(netsales)
select * from (select  distinct fiscaldate, invoice, customer, salesrepwk,    salesrepname, sum(netsales)as netsales
   from #tempsales  
   group by Salesrepname, fiscaldate, customer, invoice, salesrepwk) a
compute sum(netsales)

Open in new window

0
 
SasDevAuthor Commented:
Yes this is for an AD-HOC query. Your solution worked perfectly! Thanks BrandonGalderisi!
-- Traci
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
SasDevAuthor Commented:
Thanks again!
0
 
BrandonGalderisiCommented:
For clarification, the reason I asked if it was for an AD HOC query was because if it were being called from an application, you have to ensure that the data provider you were using allowed you to pull multiple recordsets from the connection at once.  Because the compute PROBABLY would have been returned in a separate recordset.
0
 
SasDevAuthor Commented:
I see...thanks for the heads up.
I was using this sp to compare against similar sales reports we have pulling from a different data source (i5). This will not be used in an application.

Thanks for helping us beginners --

-- Traci
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now