November 19, 2016 at 4:56 pm
i have following calculation in my sp. i need to know any short way to do it.
CREATE TABLE #Average_Price
(Brand_Id INT,
Measure_name VARCHAR(128),
Time_scale VARCHAR(50),
Measure_Value NUMERIC(38,11))
CREATE TABLE #PriceProtection
(Brand_Id INT,
Time_Scale VARCHAR(32),
Measure_Value NUMERIC(38,11))
CREATE TABLE #BaseDiscount
(Brand_Id INT,
Time_Scale VARCHAR(32),
Measure_Value NUMERIC(38,11))
CREATE TABLE #GrossTotal
(Brand_Id INT,
Time_Scale VARCHAR(32),
Measure_Value NUMERIC(38,11))
INSERT INTO #PriceProtection
SELECT PRODUCT_ID ,
YEAR_MONTH_NAME ,
SUM(MEASURE_VALUE)
FROM SUMMARY
WHERE MEASURE_NAME='PRICE PROTECTION'
AND @Measures = 'AVERAGE PRICE '
AND @Time_Scale = 'MONTH'
GROUP BY PRODUCT_ID,YEAR_MONTH_NAME
UNION
SELECT PRODUCT_ID ,
QUARTER_YEAR_NAME ,
SUM(MEASURE_VALUE)
FROM SUMMARY
WHERE MEASURE_NAME='PRICE PROTECTION'
AND @Measures = 'AVERAGE PRICE '
AND @Time_Scale = 'QUARTER'
GROUP BY PRODUCT_ID,QUARTER_YEAR_NAME
INSERT INTO BaseDiscount
SELECT PRODUCT_ID ,
YEAR_MONTH_NAME ,
SUM(MEASURE_VALUE)
FROM JASPER_REBATES_SUMMARY
WHERE MEASURE_NAME='BASE REBATE %'
AND @Measures = 'AVERAGE BASE '
AND @Time_Scale = 'MONTH'
GROUP BY PRODUCT_ID,YEAR_MONTH_NAME
UNION
SELECT PRODUCT_ID ,
QUARTER_YEAR_NAME ,
SUM(MEASURE_VALUE)
FROM SUMMARY
WHERE MEASURE_NAME='BASE REBATE %'
AND @Measures = 'AVERAGE BASE'
AND @Time_Scale = 'QUARTER'
GROUP BY PRODUCT_ID,QUARTER_YEAR_NAME
INSERT INTO #GrossTotal
SELECT PRODUCT_ID ,
YEAR_MONTH_NAME ,
SUM(MEASURE_VALUE)
FROM JASPER_REBATES_SUMMARY
WHERE MEASURE_NAME='GROSS REVENUE'
AND @Time_Scale = 'MONTH'
GROUP BY PRODUCT_ID,YEAR_MONTH_NAME
UNION
SELECT PRODUCT_ID ,
QUARTER_YEAR_NAME ,
SUM(MEASURE_VALUE)
FROM SUMMARY
WHERE MEASURE_NAME='GROSS REVENUE'
AND @Time_Scale = 'QUARTER'
GROUP BY PRODUCT_ID,QUARTER_YEAR_NAME
INSERT INTO #Average_Price
SELECT a.Brand_id,
'AVERAGE PRICE PROTECTION',
a.Time_scale,
a.Measure_Value/b.Measure_Value
FROM #PriceProtection a INNER JOIN #GrossTotal b
ON a.Brand_id = b.Brand_id AND a.Time_Scale = b.Time_Scale
UNION
SELECT a.Brand_id,
'AVERAGE BASE ',
a.Time_scale,
a.Measure_Value/b.Measure_Value
FROM #BaseDiscount a INNER JOIN #GrossTotal b
ON a.Brand_id = b.Brand_id AND a.Time_Scale = b.Time_Scale
any idea? thanks in advance
November 19, 2016 at 11:03 pm
Can you post the DDL (creat table) script for the SUMMARY and the JASPER_REBATES_SUMMARY tables, the full code of the stored procesure and the actual execution plan please?
😎
November 21, 2016 at 9:12 am
Thank you for trying to post DDL. But tables must have a key, by definition. You have mixed data and metadata in the same table; a measurement name and a timescale are metadata, not the value. And why the heck did you pick the Oracle default numeric (38, 11) for a column? You copying this from something else, aren't you? Also, we would never materialize summary data in SQL. This was one of the major steps of RDBMS over filesystems; a virtual table is just as real as a base table; we put the calculations in a view that is always correct. Every time it is accessed.
The next thing you missed is that we have a convention from MySQL of using "yyyy-mm-00" for the month within the year and "yyyy-00-00" for the year as a whole. This is up for in ISO standard because it matches the ISO 8601 convention.
Also, a brand usually has a name and not an identifier. Can we try again with better postings?
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
November 21, 2016 at 9:43 am
Eirikur Eiriksson (11/19/2016)
Can you post the DDL (creat table) script for the SUMMARY and the JASPER_REBATES_SUMMARY tables, the full code of the stored procesure and the actual execution plan please?😎
I'd be curious to the grain of these 2 tables as well. Given your WHERE clauses like:
WHERE MEASURE_NAME='PRICE PROTECTION'
AND @Measures = 'AVERAGE PRICE '
AND @Time_Scale = 'MONTH'
It looks like there is multiple grains within the same SUMMARY table, creating a Centipede fact table?
If you're using something like Analysis Services on top of these tables, it might be better to utilize hierarchies within that to summarize data:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply