Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

sqldatasource dal, wwwxxxenden, convert raid5 to raid 10 mdadm, apache force chunked, nrao wwwxxx, xxxxxdup, procmail change subject header, wwwXxx not20, Wwwxxx.doks sas, linux raid resync after reboot

Links

XODOX
Impressum

#1: SELECT online store discount %

Posted on 2011-08-20 21:25:33 by ron.piggott

------=_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--

Report this message

#2: Re: SELECT online store discount %

Posted on 2011-08-22 11:45:32 by Amit Tandon

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

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

Report this message

#3: Re: SELECT online store discount %

Posted on 2011-08-22 17:01:59 by ron.piggott

------=_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 =
<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 =
`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--

Report this message