Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Stored Procedure Using RollUP

Status
Not open for further replies.

brxmas

Programmer
Feb 9, 2004
69
0
0
US
ODBC 2000
Crystal XI

My Stored Procedure is below. I would like to do a ROLLUP on each prod_code. Which is best ROLLUP or COMPUTE SUM to use for quick processing time. I've never used either. I would appreciate anyones help. Thanks in advance.

(
@year int
, @start_date_CY varchar(10)
, @end_date_CY varchar(10)
, @start_date_LY varchar(10)
, @end_date_LY varchar(10)
)

AS

SET NOCOUNT ON

SELECT
CASE
WHEN (GROUPING(prod_code) = 'MBLE') THEN 'Legl'
WHEN (GROUPING(prod_code) = 'MBMD') THEN 'Displ'
WHEN (GROUPING(prod_code) = 'MBEX') THEN 'Color'
END AS prod_code
, insertion_date = Convert(VarChar(10), mc_ad_insertion.insertion_date, 101)
, SUM(credit_amt) - SUM(debit_amt) AS QtySum

FROM mc_ad_insertion INNER JOIN mc_ad_cost
ON (mc_ad_insertion.bus_unit_id = mc_ad_cost.bus_unit_id)
AND (mc_ad_insertion.pub_id = mc_ad_cost.pub_id)
AND (mc_ad_insertion.acct_num = mc_ad_cost.acct_num)
AND (mc_ad_insertion.ad_num = mc_ad_cost.ad_num)
AND (mc_ad_insertion.insertion_num = mc_ad_cost.trans_id)

WHERE ((mc_ad_insertion.insertion_date between
@start_date_CY and @end_date_CY)
OR (mc_ad_insertion.insertion_date between
@start_date_LY AND @end_date_LY))

GROUP BY insertion_date, prod_code, credit_amt, debit_amt WITH ROLLUP

GO
-----------------------------------------------------------
output
CY_$ LY_$
LEGL 455 325
DISP 241 375
COLR 512 431

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top