Results 1 to 4 of 4
  1. #1
    Join Date
    May 2006
    Posts
    4

    Red face Unanswered: Can see it, can't put it to sql,...

    I have an odd thing to do in sql, it's got to count how many times it finds a gap between dates in the database.

    Imagine a table with two fields, FromDate ToDate with in that order these values:
    01/01/01 - 02/01/01
    02/15/01 - 03/01/01
    02/20/01 - 04/01/01

    What I would like to do is see whether there are no overlaps in a give date range. So say I would want to see whether there are gaps between 01/01/01 and 06/01/01 it would return a count of two because between 02/01/01 and 02/15/01 there's a 2 week gap and there's a 2 months gap between 04/01/01 and 06/01/01.

    So I can see it but have no clue how to do it in sql,... can someone help? Also,.. without doing it in code, do you think it's possible to return the start and endate of the gaps instead?

    Cheers!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why did you post this in both the oracle and sql server forums? the solutions will be completely different
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2006
    Posts
    4
    Well, it has to run on both.
    Why did you ask me the same question on both forums, the answer is the same.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Oh well, this is an extremely UGLY PL/SQL solution which probably might be enhanced; without too much testing, a function which returns number of gaps looks like this:
    Code:
    CREATE OR REPLACE FUNCTION fun_gap (
       par_start_date   IN   DATE,
       par_end_date     IN   DATE
    )
       RETURN NUMBER
    IS
       TYPE dat_typ IS TABLE OF NUMBER
          INDEX BY BINARY_INTEGER;
       dat             dat_typ;
    
       CURSOR cur_d
       IS
          SELECT start_d, end_d
            FROM GAP
           WHERE start_d >= par_start_date
             AND end_d   <= par_end_date;
    
       l_cnt           NUMBER  := 0;    -- number of gaps
       l_seq_started   NUMBER  := 0;
    BEGIN
       -- insert Julian dates (numbers, actually) into an index-by table
       FOR i IN TO_CHAR (par_start_date, 'j') .. TO_CHAR (par_end_date, 'j')
       LOOP
          dat (i) := i;
       END LOOP;
    
       -- delete dates which are contained in the GAP table
       FOR cur_r IN cur_d
       LOOP
          FOR j IN TO_CHAR (cur_r.start_d, 'j') .. TO_CHAR (cur_r.end_d, 'j')
          LOOP
             BEGIN
                dat.DELETE (j);
             EXCEPTION
                WHEN NO_DATA_FOUND
                THEN
                   NULL;
             END;
          END LOOP;
       END LOOP;
    
       -- search for consecutive remaining dates and check whether there are
       -- gaps between them
       FOR k IN dat.FIRST .. dat.LAST
       LOOP
          BEGIN
             IF dat (k) - dat.PRIOR (k) = 1
             THEN
                l_seq_started := 1;
             ELSE
                l_seq_started := 0;
                l_cnt := l_cnt + 1;
             END IF;
          EXCEPTION
             WHEN NO_DATA_FOUND
             THEN
                NULL;
          END;
       END LOOP;
    
       -- adjust it a little bit more (there's no gap in between)
       IF l_seq_started = 1 AND l_cnt = 0
       THEN
          l_cnt := 1;
       END IF;
    
       RETURN (l_cnt);
    END;
    /
    Using your input data:
    Code:
    SQL> SELECT fun_gap ('01.01.2001', '01.06.2001') number_of_gaps FROM DUAL;
    
    NUMBER_OF_GAPS
    --------------
                 2
    
    SQL>
    I'm sure there are better solutions and can't wait to see them and learn something new.

Posting Permissions

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