Re: Populating missing dates in postgresql data

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(dot)klaver(at)aklaver(dot)com