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
