Loyalty Program / Customer Information /Help with Query

[imosri]

Hi all,

I need some guidance in order to perform the following:

In a POS system, I have set a loyalty program to registered customers. At 10:00PM, a stored procedure updates discounts on registered customers based on purchases made in the last 60 days.

Here is the view:

SELECT M_cliente.MC_Id , M_Cliente.MC_Nombre AS MC_Nombre, SUM(M_Ticket.MT_Total) AS MT_Total,
case 
when SUM(M_Ticket.MT_Total) >=2000 and SUM(M_Ticket.MT_Total) <4000 then 10
when SUM(M_Ticket.MT_Total) >=4000 and SUM(M_Ticket.MT_Total) <6000 then 15
when SUM(M_Ticket.MT_Total) >=6000 and SUM(M_Ticket.MT_Total) <8000 then 20
when SUM(M_Ticket.MT_Total) >=8000 and SUM(M_Ticket.MT_Total) <10000 then 25
when SUM(M_Ticket.MT_Total) >=10000  then 30
ELSE 0 END AS MC_Descuento

FROM M_Cliente 
INNER JOIN
   M_Ticket ON M_Cliente.MC_Id = M_Ticket.MC_Id
WHERE (M_Cliente.MC_U_Ticket>0) 
  and (M_Ticket.MT_Status = 0) 
  AND (CONVERT(varchar, M_Ticket.MT_fecha, 101) >= CONVERT(varchar, GETDATE() - 60, 101))
GROUP BY M_Cliente.MC_Id, M_Cliente.MC_Nombre
HAVING
(M_Cliente.MC_Id NOT IN '1','14111001','14111002','11041003','11041345','11041014')) 
AND 
(M_Cliente.MC_Nombre <> 'Disponible')

The code works great, but customers are starting to make questions, for example:
I am at the 15% discount level, until when?

So, I would like to give the customers a report telling them something like:

Actual Discount Level: 15%
Your actual discount level is valid until: 12/20/2004


So the task here, taking as an example a customer with 15% discount, is the following:

sum customer's tickets until the sum hits the lower limit of the discount range (in this case 4,000), take the oldest ticket from that group, add 60 days.

Any ideas on how to perform this task?

Regards,