This discussion is archived
1 2 3 Previous Next 35 Replies Latest reply: Jun 1, 2012 12:47 AM by Nicosa RSS

Finding gaps in dates

940047 Newbie
Currently Being Moderated
We are trying to capture where the date gap when the client was NOT active.

The conditions for active client are: records that has CLIENT_PGM_STAT_ID = 963 and CLIENT_PGM_ROLE_ID = 2676... otherwise to be removed.
The remaining rows to be combined if they have contiguous dates. Client can have many programs (699, 673...). So, my output result should be sorted as follows:
Client, PGM, Begin Dates.

Edited by: 937044 on May 31, 2012 2:31 PM
  • 1. Re: Finding gaps in dates
    Hoek Guru
    Currently Being Moderated
    Regarding gaps in dates, search this forum and/or http://asktom.oracle.com since you're not the first person with that question.
    For example:
    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:529176000346581356


    Your 'sample data' is nothing but a bunch of output, I do not have the time to turn them into CREATE TABLE and INSERT INTO statements.
    If you want us to help you, then help us first by posting a testcase we can run on our databases, instead of a bunch of output.
    Please take a look at:
    {message:id=9360002}
    Especially: *7) Sample Data*
  • 2. Re: Finding gaps in dates
    940047 Newbie
    Currently Being Moderated
    Thanks. I added my SQL which resulting syntex error and insert stmt to insert test data.
  • 3. Re: Finding gaps in dates
    Paulie Pro
    Currently Being Moderated
    >


    Hi,

    Thanks. I added my SQL which resulting syntex error and insert stmt to insert test data.
    You didn't test this! You're inserting strings into a date column which will fail.

    Kindly put the data in the correct format - i.e. TO_DATE('Date_String', 'Date_Format').

    Help us to help you.

    HTH,

    Paul...
  • 4. Re: Finding gaps in dates
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    As Paul said, you need to put DATEs into the DATE columns. All of your INSERT statements fail on my system because of that. Use TO_DATE to make a DATE from a VARCHAR2, such as '01/Jan/2010'.

    Explain how you get the results you want from that data.
    Is this really the output you want?
    CLIENT_SERIAL_ID     PGM_ID     BEGIN_DT     END_DT           CLIENT_PGM_STAT_ID     CLIENT_PGM_ROLE_ID
    2167               669     1/1/2010     11/30/2010    963               2676
    2167               669     1/1/2010     12/31/9999    963               2676
    Will begin_dt always be the same on all rows of output, regardless of where the gaps are?
    Why is end_dt on the first row in 2010, when there wasn't a gap until 2011?

    The solution you cited: {message:id=10314918} may work for you. Just add a WHERE clause to remove the gaps:
    WHERE   client_pgm_role_id  = 2676   -- or maybe ... != 2689
    Edited by: Frank Kulash on May 29, 2012 5:25 PM
  • 5. Re: Finding gaps in dates
    Hoek Guru
    Currently Being Moderated
    Thanks for posting a testcase, that sheds more light on the issue you have.
    Keep in mind though: as Paul already mentioned there are some errors in your statements.
    I am having a problem with the syntex if I add the additional columns.
    What error are you getting?
    Are you aware that you omitted the DATATYPE for columns CLIENT_PGM_STAT_ID and CLIENT_PGM_ROLE_ID?

    You say:
    The conditions are: we only look for records that has CLIENT_PGM_STAT_ID = 963 and CLIENT_PGM_ROLE_ID = 2676
    That sounds like a predicate/a WHERE clause.
    But that won't work if then after that you say:
    The expected result for client 2167, pgm 669 should be ...
    We need to filter out the records that have CLIENT_PGM_ROLE_ID = 2689 as well, since it indicates a gap.
    Also: Why is 12/31/9999 to be considered as a gap?
    Can you be more specific regarding the columns you want to filter on?
    Are you planning to use input parameters to filter on a specific set of columns?
  • 6. Re: Finding gaps in dates
    940047 Newbie
    Currently Being Moderated
    Thanks. I updated my insert statements and added more details.
  • 7. Re: Finding gaps in dates
    940047 Newbie
    Currently Being Moderated
    Thanks.

    client 2167 was active without gaps from jan 01 2010 to nov 30 2011. Then he became not active (2689) till dec 31, 2011.
    then active again till present.
  • 8. Re: Finding gaps in dates
    940047 Newbie
    Currently Being Moderated
    The answer should be:

    CLIENT_SERIAL_ID PGM_ID BEGIN_DT END_DT CLIENT_PGM_STAT_ID CLIENT_PGM_ROLE_ID
    2167 669 1/1/2010 11/30/2011 963 2676
    2167 669 1/1/2012 12/31/9999 963 2676
  • 9. Re: Finding gaps in dates
    940047 Newbie
    Currently Being Moderated
    Thanks.

    This is the sql I can drive from the above thread. I am having a problem with the syntex if I add the additional columns.
    (Lookup Error ORA-01841: (full) year must be between -4713 and +9999, and not be 0 )


    WITH     got_grp          
    AS
    (
         SELECT CLIENT_SERIAL_ID, CLIENT_PGM_STAT_ID, CLIENT_PGM_ROLE_ID, PGM_ID
         , EFFECTIVE_DT
         ,     EXPIRY_DT
         ,     EXPIRY_DT - SUM (EXPIRY_DT + 1 - EFFECTIVE_DT)
         OVER (ORDER BY CLIENT_SERIAL_ID, PGM_ID, EFFECTIVE_DT)     AS grp
         FROM FACT_CLIENT_PGM
    )
    SELECT MIN (EFFECTIVE_DT) AS grp_EFFECTIVE_DT, MAX (EXPIRY_DT)     AS grp_EXPIRY_DT
    FROM     got_grp
    where CLIENT_PGM_STAT_ID = 963 and CLIENT_PGM_ROLE_ID = 2676
    GROUP BY CLIENT_SERIAL_ID, PGM_ID,grp
    ORDER BY CLIENT_SERIAL_ID, PGM_ID,grp_EFFECTIVE_DT
    ;
  • 10. Re: Finding gaps in dates
    Hoek Guru
    Currently Being Moderated
    Sorry to be such a PITA, but: your latest query doesn't even work since all of a sudden you changed the column names.
    I cannot make quesses regarding your issue, only you can provide the correct inputs. You really need to keep both threads you posted completely separated from each other.
    Please put some effort in providing a working testcase...try to make a habit out of testing your own scripts.

    It's past midnight over here, I'll be happy to take another look tomorrow after some sleep ( and a coffee after that ).
    Usually your problem will already have been answered, by the way ;)
    ORA-01841
    Yup, got that one myself as well, but changing NLS_DATE_LANGUAGE and NLS_DATE_FORMAT fixed that for me.
  • 11. Re: Finding gaps in dates
    Paulie Pro
    Currently Being Moderated
    937044 wrote:
    Thanks. I updated my insert statements and added more details.
    Can you PLEASE read the FAQ and the post by BluShadow (at the top of the thread list)
    and learn how to ask a question on these forums.


    Following Hoek's post, you should repost your TESTED CREATE TABLE and INSERT INTO
    statements USING the {noformat}
    ....
    {noformat} tags.


    I've been trying to understand what's going on and it's not easy.


    The easier you make things for people trying to help you, the more and the better the
    answers you'll receive.


    Paul...
  • 12. Re: Finding gaps in dates
    940047 Newbie
    Currently Being Moderated
    What did you change for date format? I can see the error because of date format error but unclear on correcting it. thx
  • 13. Re: Finding gaps in dates
    Hoek Guru
    Currently Being Moderated
    I used:
    alter session set nls_date_format = 'dd-mon-yyyy';
    alter session set nls_date_language = 'american';
  • 14. Re: Finding gaps in dates
    ShankarViji Pro
    Currently Being Moderated
    Hi,

    Sorry Friend, I am Confused with your Input and Output Now too.

    But, You can Use the FIRST_VALUE Analytical Functions to achive the Difference Gap in Dates.

    Try Checking this.
    SELECT *
      FROM (SELECT   CLIENT_SERIAL_ID, PGM_ID, BEGIN_DT, END_DT,
                     CLIENT_PGM_STAT_ID, CLIENT_PGM_ROLE_ID,
                       BEGIN_DT
                     - FIRST_VALUE (BEGIN_DT) OVER (PARTITION BY CLIENT_PGM_STAT_ID)
                                                                          DAY_GAP
                FROM TEST_CLIENT_PGM
               WHERE CLIENT_PGM_STAT_ID = 963 AND CLIENT_PGM_ROLE_ID = 2676
            ORDER BY CLIENT_SERIAL_ID, PGM_ID, BEGIN_DT);
    Thanks,
    Shankar
1 2 3 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points