0

In english:
Retrieve current month's usage (using current date/month),
retrieve previous month's usage (using previous date/month),
subtract current - previous to get difference.

table is water_records
id, customer_id, water_records_date, water_records_usage
1, '1', '2010-02-01', '226600'
2, '1', '2010-01-01', '223000'
3, '4', '2010-01-05', '5600'
9, '4', '2010-02-19', '88000'
15, '5', '2010-02-15', '10060'
19, '5', '2009-12-02', '9000'
21, '9', '2010-02-19', '10500'
22, '9', '2009-12-02', '8900'

SELECT t1.water_records_usage
FROM water_records t1
INNER JOIN water_records t2
on t1.ID = t2.ID
WHERE month(t1.water_records_date) = month(DATE_ADD(now(), INTERVAL -1 month))
AND
WHERE month(t2.water_records_date) = month(DATE_ADD(now(), INTERVAL -2 month))

Which is not working.....

2
Contributors
3
Replies
4
Views
7 Years
Discussion Span
Last Post by wayne sd
0

id, customer_id, water_records_date, water_records_usage
1, '1', '2010-02-01', '226600'
2, '1', '2010-01-01', '223000'
3, '4', '2010-01-05', '5600'
9, '4', '2010-02-19', '88000'
15, '5', '2010-02-15', '10060'
19, '5', '2009-12-02', '9000'
21, '9', '2010-02-19', '10500'
22, '9', '2009-12-02', '8900'

Above is your input, Do specify how you want the ouput.

0

preferred output is:

customer_id | current | previous| total(current - previous)
1 | 226600 | 223000 | 3600
4 | 88000 | 5600 | 82400
5 | 10060 | 9000 | 1060
9 | 10500 | 8900 | 1600

0

Have gotten so close with this:

SELECT
wr.water_records_usage AS previous, DATE_FORMAT(wr.`water_records_date`, '%Y-%m') AS month,
wr2.water_records_usage AS current, DATE_FORMAT(wr2.`water_records_date`, '%Y-%m') AS month
FROM `water_records` wr, `water_records` wr2
WHERE
month(wr.water_records_date) = month(DATE_ADD(now(), INTERVAL -2 month)) AND
month(wr2.water_records_date) = month(DATE_ADD(now(), INTERVAL -1 month))
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.