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
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