Re: SELECT online store discount %

Ron Have u thought of CASE (in SELECT)<http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html>.. Remebber their is some syntactical difference in "CASE" for SELECT and "CASE" in procedures ============ regds amit "The difference between fiction and reality? Fiction has to make sense." On Sun, Aug 21, 2011 at 12:55 AM, Ron Piggott < ron.piggott@actsministries.org> wrote: > > I am trying to write a database query that determine the customer loyalty > discount for an online store. I am wondering if there is a way of doing > this as 1 query, instead of multiple and using PHP to do the math? > > - I want to offer a 10% discount if the person is a subscriber > > SELECT 10 AS discount FROM subscriber_details WHERE > email = > '$client_email' LIMIT 1 > > - I also want to offer a customer loyalty discount: > > 10% if this is a purchase within 4 months of the previous purchase, > > SELECT 10 AS discount FROM paypal_payment_info_sample WHERE > datecreation >= DATE_SUB( NOW( ) , INTERVAL 4 MONTH ) AND > paymentstatus > = 'Completed' LIMIT 1 > > - OR 5% if the most recent previous purchase is between 4 months and 1 year > ago. > > SELECT 5 AS discount FROM paypal_payment_info_sample WHERE > datecreation > BETWEEN DATE_SUB( NOW( ) , INTERVAL 1 YEAR ) AND DATE_SUB( NOW( ) , INTERVAL > 4 MONTH ) AND paymentstatus = 'Completed' LIMIT 1 > > The discounts possibilities would be: > - 20% (a subscriber with a purchase within the past 4 months) > - 15% (a subscriber with a purchase between 4 months and a year ago) > - 10% (for being a subscriber) > - 10% (for a purchase made within the past 4 months) > - 5% (for a purchase made between 4 months and a year ago) > > Is there a way to do this all within the context of 1 query? > > Ron > > The Verse of the Day > “Encouragement from God’s Word” > http://www.TheVerseOfTheDay.info >