1 2 3 Previous Next 35 Replies Latest reply on Jun 1, 2012 7:47 AM by Nicosa-Oracle

    Finding gaps in dates

    940047
      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
          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
            Thanks. I added my SQL which resulting syntex error and insert stmt to insert test data.
            • 3. Re: Finding gaps in dates
              Paulie
              >


              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
                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
                  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
                    Thanks. I updated my insert statements and added more details.
                    • 7. Re: Finding gaps in dates
                      940047
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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