Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2015
    Posts
    10

    Unanswered: How to populate missing dates in Postgres?

    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!

  2. #2
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    You don't need to populate anything.
    Set start_date_of_reporting to the start_date - 1.
    Set the index to object+channel+date
    read the table.
    If table.date <> start_date_of_reporting + 1
    display reporting missing after start_date_of_reporting until table.date-1
    start_of_reporting_date=table.date-1
    endif
    next record.
    Don't ask me how to code that in sql. You also need to reset the start of reporting date after each channel and object break.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •