On 03/25/2015 05:25 PM, Lavrenz, Steven M wrote:
Alright everyone, this is a doozy of a problem. I am new to Postgres so
I appreciate patience/understanding. I have a database of hardware
objects, each of which has several different “channels”. Once per day,
these channels are supposed to check in with a central server,
generating an event log table (TABLE A) like the following:
object_id channel check-in date
****************************************
990 1 2014-12-01
990 1 2014-12-02
990 2 2014-12-01
990 2 2014-12-02
286 2 2014-12-01
286 2 2014-12-02
286 5 2014-12-01
286 5 2014-12-02
4507 1 2014-12-01
4507 1 2014-12-02
4507 2 2014-12-01
4507 2 2014-12-02
And so on. Occasionally, communications will break down to the hardware,
such that no reporting occurs. For example, let’s say that object 286
loses communications on 12/1/2014. Then the table might look like:
object_id channel check-in date
****************************************
990 1 2014-12-01
990 1 2014-12-02
990 2 2014-12-01
990 2 2014-12-02
286 2 2014-12-02
286 5 2014-12-02
4507 1 2014-12-01
4507 1 2014-12-02
4507 2 2014-12-01
4507 2 2014-12-02
Or let’s say that for some reason, just channel 2 loses reporting for a
day. Then we would have:
object_id channel check-in date
****************************************
990 1 2014-12-01
990 1 2014-12-02
990 2 2014-12-01
990 2 2014-12-02
286 2 2014-12-02
286 5 2014-12-01
286 5 2014-12-02
4507 1 2014-12-01
4507 1 2014-12-02
4507 2 2014-12-01
4507 2 2014-12-02
I have a second table (TABLE B) with all of the object_ids and channels
that are supposed to be reporting in each day. For cases where a certain
channel does not check in, I want to add a column that indicates the
comm failure. So, for the example where all channels on object 286 do
not check in, I would like to get is something like this:
object_id channel check-in
date comm failure
**********************************************************
990 1
2014-12-01 No
990 1
2014-12-02 No
990 2
2014-12-01 No
990 2
2014-12-02 No
286 2
2014-12-01 Yes
286 2
2014-12-02 No
286 5
2014-12-01 Yes
286 5
2014-12-02 No
4507 1
2014-12-01 No
4507 1
2014-12-02 No
4507 2
2014-12-01 No
4507 2
2014-12-02 No
I have been racking my mind for the better part of a day on how to do
this. The thing is that I can do a right join of TABLE B on TABLE A, and
this will populate the missing object ids and channels. However, this
only works for a single day, and it gives me something like:
object_id channel check-in
date comm failure
**********************************************************
990 1
2014-12-01 No
990 1
2014-12-02 No
990 2
2014-12-01 No
990 2
2014-12-02 No
286 2
Yes
286 2
2014-12-02 No
286 5
Yes
286 5
2014-12-02 No
4507 1
2014-12-01 No
4507 1
2014-12-02 No
4507 2
2014-12-01 No
4507 2
2014-12-02 No
I need to do a count of comm failures by day, so I need to populate the
check-in date field. Please help!
Without seeing the actual query this is just a suggestion. I would say
use CASE:
http://www.postgresql.org/docs/9.3/interactive/functions-conditional.html#FUNCTIONS-CASE
Where if the date was not available from table A use the one from table B.
Best Regards,
Steve
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
