Search Postgresql Archives

Complex case statement

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi All,

I have three tables.

Table: transaction

 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.

Postgres version: Postgresql 9.2
OS: RHEL 5

Thanks!


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux