DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Very Urgent - Query to split by date range

  1. #1
    Join Date
    May 2001
    Location
    Boston
    Posts
    24

    Exclamation

    Hi,

    I have a table with 3 columns

    Column Names: id Number
    start_date Date
    end_date Date
    Start_Date & End_Date will contain only dates of SUNDAY's.

    For Eg: ID = 1, Start_Date = 16-JUN-2002 , End_Date = 30-JUN-2002
    ID = 2, Start_Date = 04-AUG-2002 , End_Date = 25-AUG-2002

    Like this there will be more than one record in the table.

    My requirement is to split the above record by weeks.

    For the above example no. of weeks between 16-JUN-2002 & 30-JUN-2002 is 2 weeks and for 04-AUG-2002 & 24-AUG-2002 is 3 weeks.

    So my query should return 2 rows for record 1 and 3 rows for record 2, the records should be split with respect to SUNDAYS as below:

    Eg: ID Start_Date End_Date
    1 16-JUN-2002 23-JUN-2002
    1 23-JUN-2002 30-JUN-2002
    2 04-AUG-2002 11-AUG-2002
    2 11-AUG-2002 18-AUG-2002
    2 18-AUG-2002 25-AUG-2002
    This should be done with SELECT statement.

    I definitely believe you should have come across this from your experience.

    I appreciate your help in advance.

    Thanks.
    RaviSHankar.

  2. #2
    Join Date
    Jun 2002
    Location
    Denver
    Posts
    54
    Just a SELECT ? That's a brain teaser. Can you create temporary table or something ?

  3. #3
    Join Date
    Feb 2001
    Posts
    180
    I agree with shibha:
    If you create a (temp) table with all possible dates
    (from min(start) till max(end) ) only the sundays,
    you can join the tables like this:

    select o.id, d.start_dt, d.end_dt
    from org_table o
    , date_table d
    where d.start_dt >= o.start_dt
    and d.end_dt < o.end_dt

    Can be some mistakes in it, but it's the idea.

    Regards
    Ben de Boer

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    The following will work for the any interval in this decade (522 weeks, from 01-JAN-2000 until 31-DEC-2009).

    Code:
    SQL> select * from my_table;
    
            ID START_DAT END_DATE
    ---------- --------- ---------
             1 16-JUN-02 30-JUN-02
             2 04-AUG-02 25-AUG-02
    
    SQL> SELECT a.id, b.start_date, b.end_date
      2  FROM my_table a, 
      3    (SELECT (ROWNUM-1)*7+NEXT_DAY('01-JAN-2000','SUNDAY') start_date,
      4         ROWNUM*7+NEXT_DAY('01-JAN-2000','SUNDAY') end_date
      5     FROM all_objects
      6     WHERE ROWNUM <= 522) b
      7  WHERE b.start_date >= a.start_date
      8    AND b.start_date < a.end_date; 
    
            ID START_DAT END_DATE
    ---------- --------- ---------
             1 16-JUN-02 23-JUN-02
             1 23-JUN-02 30-JUN-02
             2 04-AUG-02 11-AUG-02
             2 11-AUG-02 18-AUG-02
             2 18-AUG-02 25-AUG-02
    
    SQL>
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

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


Click Here to Expand Forum to Full Width