Google
  Web www.spinics.net

Re: SELECT online store discount %

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]


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@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
>

[PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Plagiarism Notes]     [Postgresql]     [Yosemite News]

Powered by Linux