display values depending on a rule

hello,
i have a table with 3 fields
CustNr (int)
artikleNr (int)
pieces (int)
simple example - all customer have bueyed the article with the nr 11
101 11 8
102 11 3
101 11 4
102 11 20
103 11 3
104 11 15
104 11 25
i want to display a information in the following way
if customer has < 10 pieces display 0
if customer has 10 -20 display real value 1...20
if customer has more then 20 display 20
101 8 + 4 =12 display 12
102 3 + 20=23 display 20
103 3 display 0
104 15+25=40 display 20
thanksSomething like this?
SELECT CustNr,
'SomeColumn' = CASE WHEN SUM(pieces) < 10 THEN 0
WHEN SUM(pieces) BETWEEN 10 AND 20 THEN SUM(pieces)
WHEN SUM(pieces) > 20 THEN 20
ELSE NULL END
FROM YourTable
WHERE artikleNr = 11 /* I don't know if this is a param that would limit
the data returned, or if you want to group by this column as well (in
addition to CustNr) */
Keith Kratochvil
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:6A10DB32-0D1E-4C48-B657-C5C83A78DDF7@.microsoft.com...
> hello,
> i have a table with 3 fields
> CustNr (int)
> artikleNr (int)
> pieces (int)
> simple example - all customer have bueyed the article with the nr 11
> 101 11 8
> 102 11 3
> 101 11 4
> 102 11 20
> 103 11 3
> 104 11 15
> 104 11 25
> i want to display a information in the following way
> if customer has < 10 pieces display 0
> if customer has 10 -20 display real value 1...20
> if customer has more then 20 display 20
>
> 101 8 + 4 =12 display 12
> 102 3 + 20=23 display 20
> 103 3 display 0
> 104 15+25=40 display 20
> thanks|||thanks Keith, it works perfect.
best regards
"Keith Kratochvil" wrote:

> Something like this?
> SELECT CustNr,
> 'SomeColumn' = CASE WHEN SUM(pieces) < 10 THEN 0
> WHEN SUM(pieces) BETWEEN 10 AND 20 THEN SUM(pieces)
> WHEN SUM(pieces) > 20 THEN 20
> ELSE NULL END
> FROM YourTable
> WHERE artikleNr = 11 /* I don't know if this is a param that would limit
> the data returned, or if you want to group by this column as well (in
> addition to CustNr) */
> --
> Keith Kratochvil
>
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:6A10DB32-0D1E-4C48-B657-C5C83A78DDF7@.microsoft.com...
>
>