Link to home
Start Free TrialLog in
Avatar of SasDev
SasDevFlag for United States of America

asked on

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



Avatar of SasDev
SasDev
Flag of United States of America image

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of BrandonGalderisi
BrandonGalderisi
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of SasDev

ASKER

Yes this is for an AD-HOC query. Your solution worked perfectly! Thanks BrandonGalderisi!
-- Traci
Avatar of SasDev

ASKER

Thanks again!
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.
Avatar of SasDev

ASKER

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