Remove a Duplicate value

[cognosun]

Hi Experts,

name   points   shop      date
d1             20             s1   1/1/2012
d1             80             s1   1/1/2012
d2             55             s2   1/1/2012
d2             45             s3   1/1/2012
d2             25             s3   1/1/2012

We have above table.

If a consumer ( d1) belongs to one shop(s1)  his points are 100 ( 20+80) which is correct.

But if a consumer belongs to 2 shops ( s2 and s3) you can observe his points are exceeding 100 ( 55+45+25)

The requirement is, points should not cross 100, for any consumer, in any month ( here it's Jan)

Can anyone help me in writing sql for it...and also is it possible to implement this change in Query studio, instead of Report studio.


Many Thanks

[cognosun]

select shop,count(*) from table group by shop having count(*)<=1 ...will this do ?

[tjohnson3050]

In report studio, you can create a data item using a case statement.

case
when sum(points) > 100
then 100
else sum(points)
end