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
select shop,count(*) from table group by shop having count(*)<=1 ...will this do ?
In report studio, you can create a data item using a case statement.
case
when sum(points) > 100
then 100
else sum(points)
end