I have three tables.
meter_id | bay_number | trans_date_time | amount
----------+------------+----------------------------+--------
1078 | 5 | 2013-06-03 09:59:32+10 | 5.00
1078 | 7 | 2013-06-03 09:12:01+10 | 5.00
1078 | 6 | 2013-06-03 08:33:11+10 | 5.00
1078 | 8 | 2013-05-27 09:48:26+10 | 5.00
1078 | 5 | 2013-05-27 09:41:42+10 | 5.00
1078 | 4 | 2013-05-27 09:41:08+10 | 5.00
1078 | 7 | 2013-05-20 17:14:15+10 | 2.00
1078 | 2 | 2013-05-20 16:19:10+10 | 2.50
Table: service
meter_id | notified_at | finished_at
----------+------------------------+------------------------
1078 | 2013-05-30 15:02:27+10 | 2013-05-30 15:32:20+10
1078 | 2013-05-30 12:32:43+10 | 2013-05-30 14:50:14+10
1078 | 2013-05-30 08:31:27+10 | 2013-05-30 10:25:56+10
1078 | 2013-05-29 07:36:31+10 | 2013-05-29 11:35:47+10
Table: relocated_meter
relocation_date | meter_id | bay_number | source_meter_id | source_bay_number
-----------------+----------+------------+-----------------+-------------------
2013-04-24 | 1078 | 1 | 1078 | 1
2013-04-24 | 1078 | 2 | 1078 | 2
2013-04-24 | 1078 | 3 | 1078 | 3
2013-04-24 | 1078 | 4 | 1078 | 4
2013-04-24 | 1078 | 5 | 1078 | 5
2013-04-24 | 1078 | 6 | 1078 | 6
2013-04-24 | 1078 | 7 | 1078 | 7
2013-04-24 | 1078 | 8 | 1067 | 5
2013-04-24 | 1078 | 9 | 1067 | 6
A quick sum(amount) where service.notified_at and service.finished_at gives me the amount received during service.
To get the average amount between service.notified_at and service.finished_at for the last 52 weeks, I just run a case statement similar to the following:
SUM (CASE WHEN meter_id IN (1078)
AND trans_date_time BETWEEN 'notified_at' AND 'finished_at'
THEN amount ELSE 0
END) AS 'week_no'
I then sum the amount for each 'week_no'/52 to get the average yearly amount.
Now, a few meters have inherited some bays and with the introduction on my third table called relocated_meter, I'd like to get the weekly amount PLUS income for another meter (1067 bay 5 and 1067 bay 6) because they're now part of meter 1078 bay 8 and 9, so average income should include the other bays too.
I hope my question makes sense. Please advise if you like further details.