A variety of if’s and Greatest in conjunction to mySQL math works!
SELECT (
(
GREATEST( IF( (
SELECT 10 AS discount
FROM `paypal_payment_info_sample`
WHERE `datecreation` = DATE_SUB( NOW( ) , INTERVAL 4
MONTH )
AND `buyer_email` = '$client_email'
AND `paymentstatus` = 'Completed'
LIMIT 1 ) , 10, 0 ) , IF( (
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 `buyer_email` = '$client_email'
AND `paymentstatus` = 'Completed'
LIMIT 1 ) , 5, 0
)
)
) + ( IF( (
SELECT 10 AS discount
FROM `subscriber_details`
WHERE `email` = '$client_email'
LIMIT 1
), 10, 0 ) )
) AS discount_percentage
The Verse of the Day
“Encouragement from God’s Word”
http://www.TheVerseOfTheDay.info
From: Amit Tandon
Sent: Monday, August 22, 2011 5:45 AM
To: Ron Piggott
Cc: php-db@lists.php.net
Subject: Re: [PHP-DB] SELECT online store discount %
Ron
Have u thought of CASE (in SELECT). 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.pigg...@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
