April 16, 2010 at 9:02 pm
I have a table like this
CREATE TABLE AAA(K int, K1 int, MONEY int)
And i have a SQL like this
SELECT K, CASE WHEN SUM(MONEY)>999 THEN 999 ELSE SUM(MONEY) END FROM AAA GROUP BY K
It work with my solution but the command SUM(MONEY) calculate 2 time. How to rewrite my SQL to shorter?:-D
April 16, 2010 at 9:15 pm
How about this?
;WITH SUM_CTE(K, SUM_AMOUNT)
AS
(
SELECT
K, SUM(MONEY) SUM_AMOUNT
FROM
AAA
GROUP BY
K
)
SELECT
K,
CASE
WHEN SUM_AMOUNT > 999 THEN 999
ELSE SUM_AMOUNT
END SUM_AMOUNT
FROM SUM_CTE
By this way, the sum and the CASE are executed only once 🙂
Did it help you buddy??
Cheers!!
April 18, 2010 at 2:27 am
SELECT K,
ISNULL(NULLIF(CONVERT(BIT, SIGN(SUM(MONEY) - 999) - 1), 0) * SUM(MONEY), $999)
FROM AAA
GROUP BY K;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply