Hi,
Here is the table in question.
drop table `portfolio1`;
CREATE TABLE `portfolio1` (
`ccode` varchar(99) default NULL,
`symbol` varchar(99) default NULL,
`transaction_date` date default NULL,
`rate` decimal(11,2) default NULL,
`quantity` int(11) default NULL,
`transaction` enum('buy','sell') default NULL
) TYPE=MyISAM;
INSERT INTO `portfolio1` VALUES ( 'soak', 'A', '2002-09-02', '345.92',
'350', 'buy');
INSERT INTO `portfolio1` VALUES ( 'soak', 'D', '2003-03-25', '39.90',
'1000', 'buy');
INSERT INTO `portfolio1` VALUES ( 'soak', 'A', '2002-12-05', '500.00',
'100', 'sell');
INSERT INTO `portfolio1` VALUES ( 'soak', 'A', '2003-12-05', '900.00',
'100', 'sell');
INSERT INTO `portfolio1` VALUES ( 'soak', 'D', '2004-12-05', '67.00',
'200', 'sell');
INSERT INTO `portfolio1` VALUES ( 'soak', 'D', '2004-12-09', '87.00',
'500', 'sell');
query I know...
SELECT
sub1.ccode,
sub1.symbol,
SUM(sub1.quantity_buy) buy,
SUM(sub1.quantity_sell) sell,
(SUM(sub1.quantity_buy) - SUM(sub1.quantity_sell)) stock,
MAX(sub1.transaction_date) transaction_date
FROM
(
SELECT ccode, symbol, quantity quantity_buy, 0 quantity_sell, transaction_date
FROM `portfolio1` p
WHERE transaction = 'buy'
union all
SELECT ccode, symbol, 0, quantity quantity_sell, ''
FROM `portfolio1` p
WHERE transaction = 'sell'
) sub1
GROUP BY
sub1.ccode, sub1.symbol;
The results are as per my expectations.
ccode symbol buy sell stock transaction_date
soak A 300 275 25 2003-09-02
soak D 900 400 500 2003-03-25
But I do also want to know the corresponding quantity bought on that
particular transaction_date.
ccode |symbol | buy | sell | stock | transaction_date | bought
soak | A | 300 | 275 | 25 | 2003-09-02 | 150
soak | D | 900 | 400 | 500 | 2003-03-25 | 300
and if it is possible, the second last entry if the stock > bought like this...
ccode |symbol | buy | sell | stock | transaction_date | bought
soak | A | 300 | 275 | 25 | 2003-09-02 | 150
soak | D | 900 | 400 | 500 | 2003-03-25 | 300
soak | D | 900 | 400 | 500 | 2002-02-25 | 100
soak | D | 900 | 400 | 500 | 2001-03-25 | 100
Note: even if 500 quantity was bought on 2001-03-25 I want to display only 100
in the bought column above because 400 units have already been sold off.
Thanks
Shantanu Oak
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
