Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 2nd, 2008, 12:26 PM
Registered User
 
Join Date: Aug 2008
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Advanced Conditional Select

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 August 2nd, 2008, 01:35 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Can you clarify the rules a bit more???

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

Or what?
 
Old August 2nd, 2008, 02:46 PM
Registered User
 
Join Date: Aug 2008
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 August 3rd, 2008, 08:09 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional select previous records' value sjanderson XSLT 5 May 4th, 2007 03:28 AM
What is Advanced PHP raaj Beginning PHP 6 December 21st, 2006 12:51 PM
Conditional Select problem rodmcleay SQL Server 2000 3 September 7th, 2005 07:42 PM
advanced listobox! Please Help Varg_88 Javascript 4 September 29th, 2004 01:49 AM
advanced search Moharo Pro PHP 1 February 2nd, 2004 04:43 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.