Re: SELECT online store discount %

["Ron Piggott" <ron.piggott@xxxxxxxxxxxxxxxxxx>]

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@xxxxxxxxxxxxx 
Subject: Re:  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@xxxxxxxxxxxxxxxxxx> 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