Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2009
    Posts
    58

    Unanswered: Help with date range query..

    Hi Guys,

    I have a table which contains start date and end date. Given a date range, it should give me the date range expect the date range present in the table
    For an example,

    If the table is as shown below.
    Code:
    Start_date           End_date
    3-jan-2010            7-jan-2010
    13-jan-2010           19-jan-2010
    Given the start date as 1-jan-2010 and end date as 25-jan-2010. Then the output should be as

    Code:
    Start_date           End_date
    1-jan-2010            2-jan-2010
    8-jan-2010           12-jan-2010
    20-jan-2010          25-jan-2010
    I was able to create all the date from start to end date. But unable to get the desired output. The code i used is

    Code:
    with temp(date) as 
    (select '1-jan-2010' as date 
    from dual
    union all
    select date+1 days from temp
    where date <='25-jan-2010')
    select * from temp
    Hope i made myself clear.

    Thanks for your help in advance.

    Magesh.

  2. #2
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    delete by poster
    Dick Brenholtz, Ami in Deutschland

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is an example considering the case where given start date or end date was overlapped with existing date range.
    (I thought that this query would be shorter by using LAG/LEAD OLAP specifications.)

    assumption:
    No overlaps between existing ranges.

    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    /**************************************************
    ********** Start of test data            **********
    **************************************************/
     given_range(start_date , end_date) AS (
    SELECT DATE( TO_DATE(start_date , 'dd-mon-yyyy') )
         , DATE( TO_DATE(  end_date , 'dd-mon-yyyy') )
      FROM (VALUES
              ( '3-jan-2010' ,  '7-jan-2010')
            , ('13-jan-2010' , '19-jan-2010')
            , ( '2-feb-2010' ,  '5-feb-2010')
           ) s(start_date , end_date)
    )
    , start_end(start_date , end_date) AS (
    SELECT DATE( TO_DATE(start_date , 'dd-mon-yyyy') )
         , DATE( TO_DATE(  end_date , 'dd-mon-yyyy') )
      FROM (VALUES
              ('1-jan-2010' , '25-jan-2010')
            --  ('5-jan-2010' , '25-jan-2010') /* start_date was overlapped with existing range ( '3-jan-2010' ,  '7-jan-2010') */
            --  ('1-jan-2010' , '15-jan-2010') /*   end_date was overlapped with existing range ('13-jan-2010' , '19-jan-2010') */
            --  ('1-jan-2010' , '10-feb-2010')
           ) s(start_date , end_date)
    )
    /**************************************************
    **********   End of test data            **********
    **************************************************/
    SELECT end_date   + 1 DAY AS start_date
         , start_date - 1 DAY AS end_date
     FROM  (SELECT start_date
                 , ROW_NUMBER() OVER(ORDER BY start_date) AS rn
              FROM
                   (SELECT gr.start_date
                      FROM given_range gr
                         , start_end   se
                     WHERE gr.start_date
                           BETWEEN se.start_date AND se.end_date
                    UNION ALL
                    SELECT se.end_date + 1 DAY
                      FROM start_end   se
                     WHERE NOT EXISTS
                           (SELECT 0
                              FROM given_range gr
                             WHERE se.end_date
                                   BETWEEN gr.start_date AND gr.end_date
                           )
                   ) s(start_date)
           ) s
     INNER JOIN
           (SELECT end_date
                 , ROW_NUMBER() OVER(ORDER BY end_date) AS rn
              FROM
                   (SELECT gr.end_date
                      FROM given_range gr
                         , start_end   se
                     WHERE gr.end_date
                           BETWEEN se.start_date AND se.end_date
                    UNION ALL
                    SELECT se.start_date - 1 DAY
                      FROM start_end   se
                     WHERE NOT EXISTS
                           (SELECT 0
                              FROM given_range gr
                             WHERE se.start_date
                                   BETWEEN gr.start_date AND gr.end_date
                           )
                   ) e(end_date)
           ) e
       ON  e.rn = s.rn
    ;
    ------------------------------------------------------------------------------
    
    START_DATE END_DATE  
    ---------- ----------
    2010-01-01 2010-01-02
    2010-01-08 2010-01-12
    2010-01-20 2010-01-25
    
      3 record(s) selected.
    Last edited by tonkuma; 07-13-10 at 08:47. Reason: Add an assumption. Change UNION to UNION ALL.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another example using LEAD OLAP specification.

    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    /**************************************************
    ********** Start of test data            **********
    **************************************************/
     given_range(start_date , end_date) AS (
    SELECT DATE( TO_DATE(start_date , 'dd-mon-yyyy') )
         , DATE( TO_DATE(  end_date , 'dd-mon-yyyy') )
      FROM (VALUES
              ( '3-jan-2010' ,  '7-jan-2010')
            , ('13-jan-2010' , '19-jan-2010')
            , ( '2-feb-2010' ,  '5-feb-2010')
           ) s(start_date , end_date)
    )
    , start_end(start_date , end_date) AS (
    SELECT DATE( TO_DATE(start_date , 'dd-mon-yyyy') )
         , DATE( TO_DATE(  end_date , 'dd-mon-yyyy') )
      FROM (VALUES
              ('1-jan-2010' , '25-jan-2010')
            --  ('5-jan-2010' , '25-jan-2010') /* start_date was overlapped with existing range ( '3-jan-2010' ,  '7-jan-2010') */
            --  ('1-jan-2010' , '15-jan-2010') /*   end_date was overlapped with existing range ('13-jan-2010' , '19-jan-2010') */
            --  ('1-jan-2010' , '10-feb-2010')
           ) s(start_date , end_date)
    )
    /**************************************************
    **********   End of test data            **********
    **************************************************/
    SELECT start_date
         , end_date
      FROM (SELECT end_date + 1 DAY AS start_date
                 , LEAD(start_date)
                     OVER(ORDER BY start_date) - 1 DAY AS end_date
              FROM (SELECT gr.start_date
                         , gr.  end_date
                      FROM given_range gr
                         , start_end   se
                     WHERE gr.start_date BETWEEN se.start_date AND se.end_date
                       OR  gr.  end_date BETWEEN se.start_date AND se.end_date
                    UNION ALL
                    SELECT DATE('0001-01-02')
                         , start_date - 1 DAY
                      FROM start_end   se
                     WHERE NOT EXISTS
                           (SELECT 0
                              FROM given_range gr
                             WHERE se.start_date
                                   BETWEEN gr.start_date AND gr.end_date
                           )
                    UNION ALL
                    SELECT   end_date + 1 DAY
                         , DATE('9999-12-30')
                      FROM start_end   se
                     WHERE NOT EXISTS
                           (SELECT 0
                              FROM given_range gr
                             WHERE se.end_date
                                   BETWEEN gr.start_date AND gr.end_date
                           )
                   ) s(start_date , end_date)
           )
     WHERE end_date IS NOT NULL
     ORDER BY
           start_date
    ;
    ------------------------------------------------------------------------------
    
    START_DATE END_DATE  
    ---------- ----------
    2010-01-01 2010-01-02
    2010-01-08 2010-01-12
    2010-01-20 2010-01-25
    
      3 record(s) selected.

  5. #5
    Join Date
    Jul 2009
    Posts
    58
    Thanks tonkuma.... can you please explain me the logic you have done.. thanks once again

  6. #6
    Join Date
    Jul 2009
    Posts
    58
    Tonkuma, i found one problem with the query.. If we have a overlapping range in the table (given_range of yours), then the result is coming wrongly.
    For an example, if the given_range table contains
    3-jan-2010 5-jan-2010
    4-jan-2010 15-jan-2010
    Then it should consider as
    3-jan-2010 15-jan-2010
    as both of them overlaps.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Yes.
    I had added an assumption on my first post in this thread.
    assumption:
    No overlaps between existing ranges.
    If we have a overlapping range in the table, we should first remove the overlapping.

    Here is an example(I change the table name to range_table):
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    /**************************************************
    ********** Start of test data            **********
    **************************************************/
     range_table(start_date , end_date) AS (
    SELECT DATE( TO_DATE(start_date , 'dd-mon-yyyy') )
         , DATE( TO_DATE(  end_date , 'dd-mon-yyyy') )
      FROM (VALUES
              ( '3-jan-2010' ,  '5-jan-2010')
            , ( '4-jan-2010' , '15-jan-2010')
            , ( '2-feb-2010' ,  '5-feb-2010')
            , ( '5-mar-2010' , '10-mar-2010')
            , ( '8-mar-2010' , '15-mar-2010')
            , ('14-mar-2010' , '20-mar-2010')
           ) s(start_date , end_date)
    )
    /**************************************************
    **********   End of test data            **********
    **************************************************/
    , range_no_overlap(start_date , end_date) AS (
    SELECT start_date , end_date
     FROM  (SELECT start_date
                 , ROW_NUMBER() OVER(ORDER BY start_date) rn_s
              FROM range_table q
             WHERE NOT EXISTS
                   (SELECT 0
                      FROM range_table r
                     WHERE q.start_date > r.start_date
                       AND q.start_date < r.end_date
                   )
           ) s
     INNER JOIN
           (SELECT end_date
                 , ROW_NUMBER() OVER(ORDER BY end_date) rn_e
              FROM range_table q
             WHERE NOT EXISTS
                   (SELECT 0
                      FROM range_table r
                     WHERE q.end_date > r.start_date
                       AND q.end_date < r.end_date
                   )
           ) e
       ON  rn_e = rn_s
    )
    SELECT * FROM range_no_overlap
    ;
    ------------------------------------------------------------------------------
    
    START_DATE END_DATE  
    ---------- ----------
    2010-01-03 2010-01-15
    2010-02-02 2010-02-05
    2010-03-05 2010-03-20
    
      3 record(s) selected.
    Last edited by tonkuma; 07-14-10 at 06:40.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is an example to be considered overlapping ranges in the range table.

    Example:
    Changed the table names and sample data.
    Result multiple periods at once.
    Code:
    WITH
    /**************************************************
    ********** Start of test data            **********
    **************************************************/
     range_table(start_date , end_date) AS (
    SELECT DATE( TO_DATE(start_date , 'dd-mon-yyyy') )
         , DATE( TO_DATE(  end_date , 'dd-mon-yyyy') )
      FROM (VALUES
              ( '3-jan-2010' ,  '5-jan-2010')
            , ( '4-jan-2010' , '15-jan-2010')
            , ( '2-feb-2010' ,  '5-feb-2010')
            , ( '5-mar-2010' , '10-mar-2010')
            , ( '8-mar-2010' , '15-mar-2010')
            , ('14-mar-2010' , '20-mar-2010')
           ) s(start_date , end_date)
    )
    , period(start_date , end_date) AS (
    SELECT DATE( TO_DATE(start_date , 'dd-mon-yyyy') )
         , DATE( TO_DATE(  end_date , 'dd-mon-yyyy') )
      FROM (VALUES
              ('1-jan-2010' , '31-mar-2010')
            , ('5-jan-2010' , '31-mar-2010') /* start_date was overlapped with ( '4-jan-2010' , '15-jan-2010') */
            , ('1-jan-2010' , '12-mar-2010') /*   end_date was overlapped with ( '8-mar-2010' , '15-mar-2010') */
           ) s(start_date , end_date)
    )
    /**************************************************
    **********   End of test data            **********
    **************************************************/
    SELECT pd.start_date AS period_start
         , pd.  end_date AS period_end
         , rs.end_date + 1 DAY AS start_date
         , COALESCE(
              (SELECT MIN(rt.start_date) - 1 DAY
                 FROM range_table rt
                WHERE rt.start_date
                      BETWEEN rs.end_date AND pd.end_date
                  AND NOT EXISTS -- Remove overlapping ranges
                      (SELECT 0
                         FROM range_table rt_e
                        WHERE rt.start_date > rt_e.start_date
                          AND rt.start_date < rt_e.end_date
                      )
              )
            , pd.end_date
           )  AS end_date
     FROM  period  pd
     CROSS JOIN
           LATERAL
           (SELECT rt.end_date
              FROM range_table rt
             WHERE rt.end_date
                   BETWEEN pd.start_date AND pd.end_date
               AND NOT EXISTS -- Remove overlapping ranges
                   (SELECT 0
                      FROM range_table rt_e
                        WHERE rt.end_date > rt_e.start_date
                          AND rt.end_date < rt_e.end_date
                   )
            UNION ALL
            SELECT pd.start_date - 1 DAY
              FROM sysibm.sysdummy1
             WHERE NOT EXISTS
                   (SELECT 0
                      FROM range_table rt
                     WHERE pd.start_date
                           BETWEEN rt.start_date AND rt.end_date
                   )
           ) rs(end_date)
     ORDER BY
           period_start
         , period_end
         , start_date
    ;
    ------------------------------------------------------------------------------
    
    PERIOD_START PERIOD_END START_DATE END_DATE  
    ------------ ---------- ---------- ----------
    2010-01-01   2010-03-12 2010-01-01 2010-01-02
    2010-01-01   2010-03-12 2010-01-16 2010-02-01
    2010-01-01   2010-03-12 2010-02-06 2010-03-04
    2010-01-01   2010-03-31 2010-01-01 2010-01-02
    2010-01-01   2010-03-31 2010-01-16 2010-02-01
    2010-01-01   2010-03-31 2010-02-06 2010-03-04
    2010-01-01   2010-03-31 2010-03-21 2010-03-31
    2010-01-05   2010-03-31 2010-01-16 2010-02-01
    2010-01-05   2010-03-31 2010-02-06 2010-03-04
    2010-01-05   2010-03-31 2010-03-21 2010-03-31
    
      10 record(s) selected.
    Relationships of sample data:
    Code:
    Jan                             Feb                          Mar                             Apr
    0        1         2         3  0        1         2         0        1         2         3  0
    1234567890123456789012345678901 1234567890123456789012345678 1234567890123456789012345678901 123456789
    ------------------------------- ---------------------------- ------------------------------- ---------
      <->           ( '3-jan-2010' ,  '5-jan-2010')
       <----------> ( '4-jan-2010' , '15-jan-2010')
                                     <-->     ( '2-feb-2010' ,  '5-feb-2010')
                                                                     <---->           ( '5-mar-2010' , '10-mar-2010')
                                                                        <------>      ( '8-mar-2010' , '15-mar-2010')
                                                                              <-----> ('14-mar-2010' , '20-mar-2010')
    
    <------------------------------ ---------------------------- ------------------------------>   period (1)
        <-------------------------- ---------------------------- ------------------------------>   period (2)
    <------------------------------ ---------------------------- ----------->                      period (3)
    ------------------------------- ---------------------------- ------------------------------- ---------
    0        1         2         3  0        1         2         0        1         2         3  0
    1234567890123456789012345678901 1234567890123456789012345678 1234567890123456789012345678901 123456789
    Jan                             Feb                          Mar                             Apr

    This is a formatted output by modifying the sample query:
    Code:
    PERIOD_START PERIOD_END START_DATE END_DATE  
    ------------ ---------- ---------- ----------
    2010-01-01   2010-03-12 2010-01-01 2010-01-02
    2010-01-01   2010-03-12 2010-01-16 2010-02-01
    2010-01-01   2010-03-12 2010-02-06 2010-03-04
    ----------   ---------- ---------- ----------
    2010-01-01   2010-03-31 2010-01-01 2010-01-02
    2010-01-01   2010-03-31 2010-01-16 2010-02-01
    2010-01-01   2010-03-31 2010-02-06 2010-03-04
    2010-01-01   2010-03-31 2010-03-21 2010-03-31
    ----------   ---------- ---------- ----------
    2010-01-05   2010-03-31 2010-01-16 2010-02-01
    2010-01-05   2010-03-31 2010-02-06 2010-03-04
    2010-01-05   2010-03-31 2010-03-21 2010-03-31
    ----------   ---------- ---------- ----------
    
      13 record(s) selected.

    Modified query in the example:
    Code:
    SELECT SUBSTR( CHAR(pd.start_date) || '----------' , 1 + GROUPING(rs.end_date) * 10 , 10 ) AS period_start
         , SUBSTR( CHAR(pd.  end_date) || '----------' , 1 + GROUPING(rs.end_date) * 10 , 10 ) AS period_end
         , COALESCE( CHAR(rs.end_date + 1 DAY) , '----------' ) AS start_date
         , COALESCE(
              (SELECT CHAR( MIN(rt.start_date) - 1 DAY )
                 FROM range_table rt
                WHERE rt.start_date
                      BETWEEN rs.end_date AND pd.end_date
                  AND NOT EXISTS -- Remove overlapping ranges
                      (SELECT 0
                         FROM range_table rt_e
                        WHERE rt.start_date > rt_e.start_date
                          AND rt.start_date < rt_e.end_date
                      )
              )
            , SUBSTR( CHAR(pd.end_date) || '----------' , 1 + GROUPING(rs.end_date) * 10 , 10 )
           )  AS end_date
      FROM   period  pd
    ...
    ...
     GROUP BY                                                             -- Added.
           GROUPING SETS( (pd.start_date , pd.end_date)                   -- Added.
                        , (pd.start_date , pd.end_date , rs.end_date) )   -- Added
     ORDER BY
           pd.start_date
         , pd.  end_date
         , rs.  end_date
    ;

Posting Permissions

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