Re: SELECT online store discount %
| From: | Ron Piggott | Date: | Mon, 22 Aug 2011 15:01:59 +0000 |
| Subject: | Re: SELECT online store discount % | ||
| References: | 1 2 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-47986@lists.php.net to get a copy of this message | ||
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.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