|
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! Best Regards, Steve ************************************************* Steven Lavrenz, MS, EIT Doctoral Research Fellow, Ph.D. Candidate Purdue University |
Transportation Engineering Hampton Hall of Civil Engineering, Room 1122 550 Stadium Mall Drive 765-775-6423 |
