PHP DB - MySQL Query Help

Author

MySQL Query Help

Mark Dyer

2006-03-23, 7:58 am

Hello, I'm unsure how to write the follow query, Please can someone assist.

I am writing in short hand the basis of the two queries I would like to
combine. The object is to select all the products that have not meet the min
sale requirements so I can send myself a reminder email.

The first table products contains the product information and how ofter the
stores must purchase and at what period ie, W

ly or Monthly by product.

First Query gives me all the products that must check to see if they have
meet the min sales.

Select store_id,product_id, min_level, reoirder_period from products db
where monthly_order_required > 0

Second Query is then run to test to see if they have meet the min sales.

Php: If reorder period = w

ly then backdate = 7 days elseif period =
monthly then backdate = 30

Select sum(sale_product_qty) as sale_period_total from sales db where store
= store_id and product = product_id and date <= now and date > backdate and
sale_period_total > min_level

Result: sum of sales in the last w

or month period for that product from
that store. If no result then sale level ok. If result then the difference
from min_level is what is require for the store to make asap.

Php: if sale_period_total < min_level then email low order email.

Any assistance to combine to the queries instead of hundreds of individual
would be grateful.

Regards
Mark Dyer
NZ

João Cândido de Souza Neto

2006-03-23, 7:58 am

Mark Dyer wrote:

>
> Hello, I'm unsure how to write the follow query, Please can someone
> assist.
>
> I am writing in short hand the basis of the two queries I would like to
> combine. The object is to select all the products that have not meet the
> min sale requirements so I can send myself a reminder email.
>
> The first table products contains the product information and how ofter
> the stores must purchase and at what period ie, W

ly or Monthly by
> product.
>
> First Query gives me all the products that must check to see if they have
> meet the min sales.
>
> Select store_id,product_id, min_level, reoirder_period from products db
> where monthly_order_required > 0
>
>
> Second Query is then run to test to see if they have meet the min sales.
>
> Php: If reorder period = w

ly then backdate = 7 days elseif period =
> monthly then backdate = 30
>
> Select sum(sale_product_qty) as sale_period_total from sales db where
> store = store_id and product = product_id and date <= now and date >
> backdate and sale_period_total > min_level
>
>
> Result: sum of sales in the last w

or month period for that product
> from that store. If no result then sale level ok. If result then the
> difference from min_level is what is require for the store to make asap.
>
> Php: if sale_period_total < min_level then email low order email.
>
>
> Any assistance to combine to the queries instead of hundreds of individual
> would be grateful.
>
> Regards
> Mark Dyer
> NZ

Please, could you putting your real code here to us?

--
---------------------------------------------------
João Cândido de Souza Neto
Web Developer

Sponsored Links