SasDev
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes this is for an AD-HOC query. Your solution worked perfectly! Thanks BrandonGalderisi!
-- Traci
-- Traci
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.
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
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
ASKER
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