subquery, case statement help
-
Hi
I want to calculate a field values is based on a CASE statement.
It will evaluate an expression which is the following :
SALES divided by the SUM of SALES for one PERIOD for that REP
example :
PRODUCT PERIOD REP VALUES
A JANUARY SMITH 16$
B JANUARY SMITH 32$
C JANUARY SMITH 5$
A FEBRUARY SMITH 80$
...
The Sum per REP, per PERIOD would be 16+32+5 = 53$
The market share is 16/53 for the first record
32/53 for the secod record
5/53 for the third record
So for product A in January SMITH would be in category 1 because marketshare is < 0.40
I was thinking of using a temporary calculated field called marketshare and use it in the case statement to add the category based on the marketshare.
CAT1 < 10%
CAT2 < 20%
CAT3 <60%
CAT4 < =100%
-
Something like this should do the trick.
SELECT
PRODUCT,
PERIOD,
REP,
CASE
WHEN (CAST(n.[VALUES] as Numeric(10,3))/CAST(s.TOT_VALUE as Numeric(10,3)))*100 < 10 THEN 'Cat1'
WHEN (CAST(n.[VALUES] as Numeric(10,3))/CAST(s.TOT_VALUE as Numeric(10,3)))*100 < 20 THEN 'Cat2'
WHEN (CAST(n.[VALUES] as Numeric(10,3))/CAST(s.TOT_VALUE as Numeric(10,3)))*100 < 60 THEN 'Cat3'
ELSE 'Cat4'
END
FROM
tblName N
INNER JOIN
(
SELECT
PERIOD,
REP,
SUM([VALUES]) TOT_VALUE
FROM
tblName
GROUP BY
PERIOD,
REP
 
SON
S.PERIOD = N.PERIOD
S.REP = N.REP
-
thanks for your suggestion :
I receive an error : view definition includes no output columns or includes no items in the from clause
do you know what it means ?
thanks
-
i finally figured it out :
CREATE PROCEDURE myproc AS
SELECT F.PERIOD, F.PROD, F.REP, DERIVEDTBL.TOTAL_VALUES, MKTSHARE = case when derivedtbl.total_values > 0 then F.VALUES / DERIVEDTBL.TOTAL_VALUES else 0 end,
categ = CASE WHEN DERIVEDTBL.TOTAL_VALUES = 0 THEN 'cat0' WHEN F.VALUES / DERIVEDTBL.TOTAL_VALUES < 0.1 THEN
'cat1' WHEN F.VALUES / DERIVEDTBL.TOTAL_VALUES < 0.2 THEN 'cat2' WHEN F.VALUES / DERIVEDTBL.TOTAL_VALUES < 0.4 THEN 'cat3'
WHEN F.VALUES / DERIVEDTBL.TOTAL_VALUES < 0.6 THEN 'cat4' ELSE 'cat5' END
FROM dbo.sales F INNER JOIN
(SELECT DERIVEDTBL.PERIOD, DERIVEDTBL.REP, SUM(VALUES) AS TOTAL_VALUES
FROM dbo.sales DERIVEDTBL
GROUP BY PERIOD, REP) DERIVEDTBL ON F.PERIOD = DERIVEDTBL.PERIOD AND F.REP = DERIVEDTBL.REP
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply