itzumar
Posts: 88
|
| Posted: 05/16/2013, 4:30 AM |
|
i have following mysql table
date , amount disbursed , previous month disbursed
31-04-2013 , 1000 , 0
31-05-2013 , 500 , 1000
how to write querry for that?
_________________
Umar |
 |
 |
scarvello
Posts: 64
|
| Posted: 05/16/2013, 7:33 AM |
|
First create a table like this:
CREATE TABLE `mytable` (
`id` int(10) unsigned NOT NULL,
`field_date` datetime DEFAULT NULL,
`field_amount` decimal(11,2) DEFAULT NULL,
`previous` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `previous` (`previous`),
CONSTRAINT `FK_previous` FOREIGN KEY (`previous`) REFERENCES `mytable` (`id`)
)
The PREVIOUS field reference the ID field of the same table:
Look at:
CONSTRAINT `FK_previous` FOREIGN KEY (`previous`) REFERENCES `mytable` (`id`)
Then insert some data:
INSERT INTO `mytable` (`id`, `field_date`, `field_amount`, `previous`) VALUES
(3, '2012-10-10', 1000, NULL),
(4, '2012-10-11', 500, 3);
This mean that row ID 4 as a previous row with ID 3. (While row ID with 3 has non previous)
Note:
The number sequence(3,4, ecc) is not important.
The concept is that a generic row can reference another row of the same table.
For example if row ID 456 with amount =10 reference row id 34 (with amount =20). This mean that row id 456 has amount =10 and previous amount =20
So you can now create a union query (or a view) of table MYTABLE with table MYTABLE and join the two tables with the clause previous=id
Here is the DDL query code (view)
CREATE `myview` AS
select
`mytable`.`id` AS `id`,
`mytable`.`field_date` AS `field_date`,
`mytable`.`field_amount` AS `field_amount`,
`previous`.`field_amount` AS `field_previous_amount`
from
(`mytable` left join `mytable` `previous` on((`mytable`.`previous` = `previous`.`id`)));
To obtain the data just execute:
select * from myview
Results:
--------------------------------------------------
3 2012-10-10 , 1000 , null
4 2012-10-11 , 500 , 1000
--------------------------------------------------
You can use this view as datasource for CCS Grid.
|
|
|
|
itzumar
Posts: 88
|
| Posted: 05/16/2013, 8:15 AM |
|
lot of thanks brother
_________________
Umar |
|
|
|
|
