Thanks Naomi
What I ended up with was
WITH Maximumspend (MaxTotal, member_ref) AS
(select max(total) as [MaxTotal],
member_ref
from (
select sum(amount) as Total,
member_ref,
description
from payments
where description like '%Test%'
group by member_ref, description
) X
group by member_ref
)
select Tier = case when Maxtotal <= 10 then 'Tier1'
when Maxtotal between 10 and 20 then 'Tier2'
when Maxtotal > 20 then 'Tier3' END ,
member_ref into tempdb.dbo.temptiers from Maximumspend>Using query I provided it will be:
>
>
>;with Sums AS (select sum(amount) as Total, ;
> member_ref, ;
> description ;
> from payments ;
> where description like '%Test%' ;
> group by member_ref, description ;
> ),
>Numbered as (select *, row_number() over (partition by member_ref ORDER BY Total DESC) as Rn from Sums)
>
>select CASE WHEN Total<10 then 'Tier1' WHEN Total >=10 and Total < 20 then 'Tier2' ELSE 'Tier3' END as Tier,
>member_ref, description, Total from Numbered where Rn = 1
>
>
>If you prefer to use Gregory's query you will need to use 2 derived tables.
>
>
>>Hi
>>
>>going with gregorys sql query
>>
>>
>>select max(total), ;
>> member_ref ;
>> from ( ;
>> select sum(amount) as Total, ;
>> member_ref, ;
>> description ;
>> from payments ;
>> where description like '%Test%' ;
>> group by member_ref, description ;
>> ) X ;
>> group by 2
>>
>>
>>
>>How could I alter that SQL to return
>>
>> 'Tier1' if the max total is up to 10
>>
>>'Tier2' max total 10 to 20
>>
>>and 'Tier3' over 20
>>
>>Thanks
>>
>>Nick
