>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
>
>