Advanced Conditional Select

elite · August 2nd, 2008, 12:26 PM

I have the following table:

Transactions
============
id
date
transaction_type
product_id
stock_id
value

Typical Data for table Transactions
===================================
id | date | transaction_type | product_id | stock_id | value
1 | 2008-07-20 20:00:00 | S | 1 | 1 | 15.00
2 | 2008-07-31 20:00:00 | D | 1 | 1 | 15.00
3 | 2008-08-01 12:30:00 | R | 1 | 1 | 15.00
4 | 2008-08-01 19:00:00 | S | 1 | 1 | 15.00
5 | 2008-08-01 20:00:00 | S | 1 | 2 | 15.00
6 | 2008-08-01 21:00:00 | D | 2 | 1 | 20.00

The transaction_types are as follows:
S is an item being sold
D is an item being dispatched or sold and dispatched in the sale transaction
R is an item being returned (refunded)

Now, what I want to do is create a report that will sum the sales + despatched - returned value for each product, but only summing the latest value

For example, from the above data I would want the following result:

product_id | income
1 | 30
2 | 20

I had thought of doing the following:

Code:

"SELECT product_id, (SUM(IF(S.transaction_type = 'S', S.value, 0)) + SUM(IF(S.transaction_type = 'D', S.value, 0)) - SUM(IF(S.transaction_type = 'R', S.value, 0))) AS income FROM transactions GROUP BY product_id"

This works fine, except where a product has first been sold than dispatched in two separate transactions where it will double count...

Does anyone have any ideas as to how I would only sum the most recent value for each stock without having to resort to multiple queries?

I have no control over the database structure

Any help is gratefully appreciated

I should add I'm using mysql 5

Old Pedant · August 2nd, 2008, 01:35 PM

Can you clarify the rules a bit more???

When you say "latest value", do you mean "latest value, per productID, per stockID"???

Or what?

elite · August 2nd, 2008, 02:46 PM

Thanks for the reply - Sorry for not being clear enough

I'll try to explain a little better:

products_id has many stock_id

For each product I want to sum the value of sold and/or dispatched stock minus any returns (refunds) - simple enough you would think

However there are two workflows to making a purchase in the existing database:

1. Stock item is reserved, paid for and dispatched in the same transaction (a single transaction is added to the transaction table with the product_id, stock_id, value paid and the transaction type 'D')

or

2. This is a two part process:
a. Stock item is reserved and paid for (a transaction is added to the transaction table with the product_id, stock_id, value paid and the transaction type 'S')
b. Some time later the stock item is dispatched (a transaction is added to the transaction table with the product_id, stock_id, value paid and the transaction type 'D' - the transaction created in step 'a' is not updated/deleted)

So, if I sum all the transactions that have a transaction_type of 'S' OR 'D' I get the those transactions created using workflow 2 accounted for twice

What I guess what I would like to do is:

If DISTINCT stock_id's MOST RECENT transaction_type is 'S' OR 'D' then add this to the total (I guess this would allow me to ignore any returns)

Is this possible in one query or am I going to have to have a query for each product?

Hope this is a little clearer (I think I'm starting to confuse myself!)

Old Pedant · August 3rd, 2008, 08:09 PM

Well, I'm still not sure I understand it, but...

I *THINK* this is what you are after:

Code:

SELECT product_id, SUM(value) AS income 
FROM transactions AS T,
     ( SELECT product_id, stock_id, MAX(id) AS maxid
       FROM transactions
       WHERE transaction_type IN ('S','D')
       GROUP BY product_id, stock_id ) AS M
WHERE T.id = M.maxid
GROUP BY product_id
ORDER BY product_id

Now, if you are using Access, the poor little baby may complain that this is too complex for it.

If so, you just have to do it in two steps.

First, create a STORED QUERY which consists of just that inner SELECT:

Code:

       SELECT product_id, stock_id, MAX(id) AS maxid
       FROM transactions
       WHERE transaction_type IN ('S','D')
       GROUP BY product_id, stock_id ) AS M

Store that with some easy to remember name. Say, "MaxIdByProductAndStock".

Then just use *that* in the main query:

Code:

SELECT product_id, SUM(value) AS income 
FROM transactions AS T, MaxIdByProductAndStock AS M
WHERE T.id = M.maxid
GROUP BY product_id
ORDER BY product_id

Is that what you are after???