Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Join Date
    Apr 2012
    Posts
    13

    Unanswered: Query to identify sequence of date range

    I'm working on a DB2 query to identify the date range sequence and to filter out any sub sets of date ranges.

    I really appreciate any help on this.

    This is how I have data in a view.

    ID Begin Date End Date
    140721 6/26/2010 12/31/2010 <--
    140721 7/31/2010 8/27/2010
    140721 9/25/2010 10/8/2010
    140721 10/9/2010 10/29/2010
    140721 11/27/2010 12/31/2010
    140721 1/1/2011 7/1/2011 <--
    140721 1/1/2011 1/28/2011
    140721 1/29/2011 2/25/2011
    140721 2/2/2011 2/10/2011
    140721 2/26/2011 3/18/2011
    140721 3/19/2011 4/1/2011
    140721 4/2/2011 4/29/2011
    140721 4/30/2011 5/13/2011
    140721 5/14/2011 5/27/2011
    140721 5/28/2011 7/1/2011
    140721 7/2/2011 12/30/2011 <--
    140721 7/2/2011 8/26/2011
    140721 8/6/2011 8/12/2011
    140721 8/27/2011 9/30/2011
    140721 10/1/2011 10/28/2011
    140721 1/28/2012 2/3/2012 <--

    I want the output of the query to show only those rows which I have marked with "<--" symbol.

    Can someone please provide me with any ideas on how to write appropriate query for this? Thanks!!

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by rookie24 View Post

    I want the output of the query to show only those rows which I have marked with "<--" symbol.
    And why did you mark those particular rows?

  3. #3
    Join Date
    Apr 2012
    Posts
    13
    Basically my output should be as below:

    ID Begin Date End Date
    140721 6/26/2010 12/31/2010
    140721 1/1/2011 7/1/2011
    140721 7/2/2011 12/30/2011
    140721 1/28/2012 2/3/2012

    Because only these rows have a date sequence ...

    Appreciate your response.
    Last edited by rookie24; 04-03-12 at 13:15.

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Select ID, Begin_Date, End_Date
    from your_tbl where date_seq = '<--' ?

    Last edited by Lenny77; 04-03-12 at 14:06.

  5. #5
    Join Date
    Apr 2012
    Posts
    13
    Quote Originally Posted by Lenny77 View Post
    Select ID, Begin_Date, End_Date
    from your_tbl where date_seq = '<--' ?

    Well, I don't think this will work because there are multiple IDs with each having multiple date ranges (some with subset of other). My goal is to filter out any overlapping date ranges or subsets of other date ranges and show only those rows which have consecutive date range.

    Hope this gives a clear picture of my requirement.

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Question

    Maybe this one is good to you ?

    Code:
    Select ID, Begin_Date, End_Date
    from 
    (
    select ID, Begin_Date, End_Date,
    Row_Number() over(order by year(Begin_Date) desc, 
        (days(End_Date) - days(Begin_Date)) desc) rows
    from your_tbl 
    Order by 
    year(Begin_Date) desc, 
         (days(End_Date) - days(Begin_Date)) desc
    fetch first 4 rows only
    ) a
    Lenny
    Last edited by Lenny77; 04-03-12 at 16:23.

  7. #7
    Join Date
    Apr 2012
    Posts
    13
    Can you please explain me what "year" is pointing to? also I couldn't find a function as dates() in db2 as you have used in the first sub-select.

    Sorry I couldn't understand it.

  8. #8
    Join Date
    Apr 2012
    Posts
    13
    This is how I am getting the o/p as: (i removed fetch first 4 rows only statement to explain in a better way)

    ID Begin Date End Date Rows
    140721 1/28/2012 2/3/2012 1
    140721 1/1/2011 7/1/2011 2
    140721 7/2/2011 12/30/2011 3
    140721 7/2/2011 8/26/2011 4
    140721 5/28/2011 7/1/2011 5
    140721 8/27/2011 9/30/2011 6
    140721 4/2/2011 4/29/2011 7
    140721 1/29/2011 2/25/2011 10
    140721 1/1/2011 1/28/2011 9
    140721 10/1/2011 10/28/2011 8
    140721 2/26/2011 3/18/2011 11
    140721 5/14/2011 5/27/2011 14
    140721 4/30/2011 5/13/2011 13
    140721 3/19/2011 4/1/2011 12
    140721 2/2/2011 2/10/2011 15
    140721 8/6/2011 8/12/2011 16
    140721 6/26/2010 12/31/2010 17
    140721 11/27/2010 12/31/2010 18
    140721 7/31/2010 8/27/2010 19
    140721 10/9/2010 10/29/2010 20
    140721 9/25/2010 10/8/2010 21

    The rows that I want to see as o/p are: Rows - 1,2,3 & 17 - because other date ranges are covered within these 4 rows.

    Any ideas please ?

  9. #9
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by rookie24 View Post
    Can you please explain me what "year" is pointing to? also I couldn't find a function as dates() in db2 as you have used in the first sub-select.

    Sorry I couldn't understand it.
    I fixed it. This is function Days of cause. Sorry.

  10. #10
    Join Date
    Apr 2012
    Posts
    13
    It didn't work the way that I want. First of all the query should not use any "Fetch first n rows only" because there can be more than 4 records for a ID who can have a consecutive date range.

    Below is a pictorial representation of my requirement.

    (see attachment)

    from attached file, the query should pick "1" & "5" only because they have a consecutive date range there by avoiding any subsets of it. (2,3,4 are subsets of 1)
    Attached Files Attached Files

  11. #11
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Why do you attach a text file to show something like that?
    If you enclose your line-drawing in [code] tags it will show up just fine.

  12. #12
    Join Date
    Apr 2012
    Posts
    13
    Thanks for the suggestion. There was some problem doing that so had to attach a file. But it is just to give a higher level picture.

  13. #13
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    see if this works ..

    Code:
    select a.id,a.begin_dt,a.end_dt from temp a,temp b
    where
    a.begin_dt<b.begin_dt and a.end_dt>b.end_dt
    and a.id=b.id
    should be a more efficient way of doing this though ...
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Two examples.

    If no index exists,
    Example 1 may use one join and Example 2 may use two sorts.

    Example 1:
    Code:
    SELECT *
     FROM  sample_data a
     WHERE NOT EXISTS
          (SELECT 0
            FROM  sample_data b
            WHERE b.id         =  a.id
              AND b.begin_date <= a.begin_date
              AND b.end_date   >= a.end_date
              AND
             (    b.begin_date <> a.begin_date
              OR  b.end_date   <> a.end_date
             )
          )
    ;

    Example 2:
    Code:
    SELECT id , begin_date , end_date
     FROM (SELECT s.*
                , COALESCE(
                     MIN(begin_date)
                        OVER( PARTITION BY id
                                  ORDER BY end_date DESC
                              ROWS BETWEEN UNBOUNDED PRECEDING
                                       AND 1         PRECEDING
                            )
                   , begin_date
                  ) AS min_begin_date
                , COALESCE(
                     MAX(end_date)
                        OVER( PARTITION BY id
                                  ORDER BY begin_date
                              ROWS BETWEEN UNBOUNDED PRECEDING
                                       AND 1         PRECEDING
                            )
                   , end_date
                  ) AS max_end_date
            FROM  sample_data s
          )
     WHERE end_date   >= max_end_date
       AND begin_date <= min_begin_date
    ;
    Last edited by tonkuma; 04-04-12 at 01:35. Reason: If no index was exists, ---> If no index exists,

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Example 3(may use one sort) must be better than Example 2(may use two sorts).

    Example 3:
    Removed min_begin_date
    and added/changed bold-red from Example 2.
    Code:
    SELECT id , begin_date , end_date
     FROM (SELECT s.*
                , COALESCE(
                     MAX(end_date)
                        OVER( PARTITION BY id
                                  ORDER BY begin_date
                                         , end_date   DESC
                              ROWS BETWEEN UNBOUNDED PRECEDING
                                       AND 1         PRECEDING
                            )
                   , end_date - 1 DAY
                  ) AS max_end_date
            FROM  sample_data s
          )
     WHERE end_date > max_end_date
    ;
    Last edited by tonkuma; 04-04-12 at 08:09.

Tags for this Thread

Posting Permissions

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