3 Replies Latest reply: Apr 3, 2013 3:37 PM by Frank Kulash RSS

    MISSING DATE

    1000638
      I have a list of dates
      select dt from liste Where dt> dt = 01/01/2010 and <= 01/04/2013;

      result

      01/01/2010
      03/01/2010
      04/01/2010
      .........


      how to make a sql to detect the missing dates

      example is missing on 02/01/2010
        • 1. Re: MISSING DATE
          €$ħ₪
          Like this ?
          WITH t
               AS (SELECT DATE '2010-01-01' A FROM DUAL
                   UNION ALL
                   SELECT DATE '2010-01-04' B FROM DUAL),
               T1
               AS (SELECT MIN (A) A,
                          EXTRACT (
                             DAY FROM   MAX (TO_TIMESTAMP (A, 'DD-MM-YYYY HH:MI:SS'))
                                      - MIN (TO_TIMESTAMP (A, 'DD-MM-YYYY HH:MI:SS')))
                             LV
                     FROM T A)
              SELECT A + LEVEL - 1
                FROM T1
          CONNECT BY LEVEL <= LV + 1
          1/1/2010
          1/2/2010
          1/3/2010
          1/4/2010
          • 2. Re: MISSING DATE
            Frank Kulash
            Hi,

            Here's one way:
            WITH     got_prev_dt     AS
            (
                 SELECT     dt
                 ,     LAG (dt) OVER (ORDER BY dt)     AS prev_dt
                 FROM     table_x
            )
            SELECT       prev_dt + 1     AS first_missing
            ,       dt - 1         AS last_missing
            FROM       got_prev_dt
            WHERE       dt >     prev_dt + 1
            ORDER BY  dt
            ;
            • 3. Re: MISSING DATE
              Frank Kulash
              Hi,
              Ekh wrote:
              Like this ?
              WITH t
              AS (SELECT DATE '2010-01-01' A FROM DUAL
              UNION ALL
              SELECT DATE '2010-01-04' B FROM DUAL),
              T1
              AS (SELECT MIN (A) A,
              EXTRACT (
              DAY FROM   MAX (TO_TIMESTAMP (A, 'DD-MM-YYYY HH:MI:SS'))
              - MIN (TO_TIMESTAMP (A, 'DD-MM-YYYY HH:MI:SS')))
              LV
              FROM T A)
              SELECT A + LEVEL - 1
              FROM T1
              CONNECT BY LEVEL <= LV + 1
              1/1/2010
              1/2/2010
              1/3/2010
              1/4/2010
              That seems a little more complicated than necessary. For example, A is a DATE, but you're using it as the first argument to TO_TIMESTAMP, where a string is requried, so it's doing an implicit conversion to a VARCHAR2, and then converting that to a TIMESTAMP.

              Also, that generates all the dates. OP asked for the missing dates, so we have to do an outer join or MINUS or something, like this:
              WITH     extrema          AS
              (
                   SELECT     MIN (dt)     AS first_dt
                   ,     MAX (dt)     AS last_dt
                   FROM     table_x
              )
              SELECT     first_date + LEVEL - 1     AS a_dt
              FROM     extrema
              CONNECT BY     LEVEL     <= last_dt + 1 - first_dt
                  MINUS
              SELECT     dt
              FROM     table_x
                  --
              ORDER BY  a_dt
              ;