May 18, 2004 at 1:33 am
hi ,
I would like to change the following query into an update query where categ would be the field to update:
SELECT 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
any ideas ?
thanks
May 18, 2004 at 2:49 am
Hi Paul,
Try this...
UPDATE DBO.SALES F
SET F.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
It is only a trail.
Lucky
May 18, 2004 at 5:23 am
only a trail but a happy trail !
thanks for your help
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply