Tricky Select ...

[WurzelGummidge]

Don't quite know how to make thisa simple but here goes..

I have 2 tables, an order table 'orders' and stock table 'stock'
(fields used are: stock.product,stock.status, orders.product, orders.customer,orders.order_date)

I am trying to UPDATE a flag ('status')in the stock table depending on orders of products (in STOCK table) with the following criteria:


IF the 'distinct count' of customers ordering a product over the last 3 months >=2
AND
qty of product sold over last 3 months >=3 THEN 'stock.status' = 'BTO'

this needs to work for every stock line in the stock table

Is this do-able ?

 

[roccorocks]

Please post a sample of your data.

Where does 'qty of product sold' come from (is it with the count as well?)?

Remember when you use distinct count, the count returned will be dependent on the columns selected and the information in those columns.

Rocco

 

[WurzelGummidge]

Hi Rocco,
Sorry, yes - qty product sold, customer are in same table(stock).

data like:
stock table
===========
code status
A1 SP
A2 SP
A3 BTO
A4 SP
A5 YT
.
.

order table
===========
code customer ord_no date qty
A1 WALLY 103 2003-01-01 6
A1 SMITH 123 2003-03-03 1
A1 JONES 478 2003-08-12 2
A1 SMITH 658 2003-08-08 3
A1 SMITH 781 2003-09-09 2
A2 JONES 893 2003-10-10 5
A2 JONES 891 2003-10-12 1

SO.. what i need to do is UPDATE the stock.status field to 'BTO' WHERE:
distinct count of the customers for each product sold in the last 3 months >=2
AND
qty_sold (total) of that product line >=3 (in the last 3 months)
SO.. above example
should return - (for A1 product) 2 (distinct) customers have bought it (in last 3 months) AND total qty sold (in last 3 months) = 7 - Therefore, stock.status should be UPDATED to 'BTO'
Product A2 should NOT be updated, as although the qty sold was ok, there was only 1 distinct customer buying.

 

[maswien]

It should be:

update stock set status = 'BTO' from stock s where
(select count(distinct customer) from orders o
where s.product = o.product
and datediff(mm,order_date,getdate()) <=3 ) >=2 and
(select count(*) from orders o where s.product = o.product
and datediff(mm,order_date,getdate()) <=3 ) >=3

 

[maswien]

If the qty is already there, then SQL should be:

update stock set status = 'BTO' from stock s where
(select count(distinct customer) from orders o
where s.code = o.code
and datediff(mm,order_date,getdate()) <=3 ) >=2 and
(select sum(qty) from orders o where s.code = o.code
and datediff(mm,order_date,getdate()) <=3 ) >=3