SELECT online store discount %

------=_NextPart_000_0085_01CC5F4D.6713C090
Content-Type: text/plain;
charset="UTF-8"
Content-Transfer-Encoding: quoted-printable


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` =3D =
'$client_email' LIMIT 1

- I also want to offer a customer loyalty discount:=20

10% if this is a purchase within 4 months of the previous purchase,=20

SELECT 10 AS discount FROM `paypal_payment_info_sample` WHERE =
`datecreation` >=3D DATE_SUB( NOW( ) , INTERVAL 4 MONTH ) AND =
`paymentstatus` =3D '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` =3D =
'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
â€=9CEncouragement from Godâ€=99s Wordâ€=9D
http://www.TheVerseOfTheDay.info =20

------=_NextPart_000_0085_01CC5F4D.6713C090--

Re: SELECT online store discount %

--20cf303bff82eadafb04ab14f141
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

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` =3D
> '$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` >=3D DATE_SUB( NOW( ) , INTERVAL 4 MONTH ) AND `paymentsta=
tus`
> =3D 'Completed' LIMIT 1
>
> - OR 5% if the most recent previous purchase is between 4 months and 1 ye=
ar
> ago.
>
> SELECT 5 AS discount FROM `paypal_payment_info_sample` WHERE `datecreatio=
n`
> BETWEEN DATE_SUB( NOW( ) , INTERVAL 1 YEAR ) AND DATE_SUB( NOW( ) , INTER=
VAL
> 4 MONTH ) AND `paymentstatus` =3D '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
> â€=9CEncouragement from Godâ€=99s Wordâ€=9D
> http://www.TheVerseOfTheDay.info
>

--20cf303bff82eadafb04ab14f141--

Re: SELECT online store discount %

------=_NextPart_000_000F_01CC60BA.EA1D8B10
Content-Type: text/plain;
charset="utf-8"
Content-Transfer-Encoding: quoted-printable


A variety of ifâ€=99s and Greatest in conjunction to mySQL math =
works!

SELECT (
(
GREATEST( IF( (

SELECT 10 AS discount
FROM `paypal_payment_info_sample`
WHERE `datecreation` >=3D DATE_SUB( NOW( ) , INTERVAL 4
MONTH )
AND `buyer_email` =3D '$client_email'
AND `paymentstatus` =3D '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` =3D '$client_email'
AND `paymentstatus` =3D 'Completed'
LIMIT 1 ) , 5, 0
)
)
) + ( IF( (

SELECT 10 AS discount
FROM `subscriber_details`
WHERE `email` =3D '$client_email'
LIMIT 1
), 10, 0 ) )
) AS discount_percentage

The Verse of the Day
â€=9CEncouragement from Godâ€=99s Wordâ€=9D
http://www.TheVerseOfTheDay.info =20


From: Amit Tandon=20
Sent: Monday, August 22, 2011 5:45 AM
To: Ron Piggott=20
Cc: php-db@lists.php.net=20
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 =
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` =3D =
'$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` >=3D DATE_SUB( NOW( ) , INTERVAL 4 MONTH ) AND =
`paymentstatus` =3D '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` =3D =
'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
â€=9CEncouragement from Godâ€=99s Wordâ€=9D
http://www.TheVerseOfTheDay.info


------=_NextPart_000_000F_01CC60BA.EA1D8B10--