Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354

    Unanswered: Lost writing a query...

    Got a query I'm trying to write and not getting anywhere with it...

    I have an event table that has, for instance, holidays. The query I'm trying to write is one that has a day of the month and any "event" that will fall on that day. So, for July, I might get:

    PHP Code:
    Day   Event
    ===  ==========
    1
    2
    3
    4     Independence Day 
    (USA)
    5
    ... 
    In my events table, I have a begin date and an end date for events such as Thanksgiving holiday... which spans multiple days. So, I'm trying to write a query that would return, for November, something like this:

    PHP Code:
    Day   Event
    ===  =========
    ...
    21 
    22    Thanksgiving 
    (USA)
    23    Thanksgiving (USA)
    24 
    My EVENTS table has columns EVENT_NAME, BEGIN_DATE, END_DATE... any ideas how to write the query?
    JoeB
    save disk space, use smaller fonts

  2. #2
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    Figured it out... a bit ugly, but it works:
    PHP Code:
    SELECT   all_dom.theDay,
             
    event,
             
    begin_dt,
             
    end_dt
        FROM 
    (SELECT theDay,
                     
    event,
                     
    begin_dt,
                     
    end_dt
                FROM 
    (SELECT     LEVEL TheDay
                            FROM DUAL
                      CONNECT BY LEVEL 
    <= TO_CHAR (LAST_DAY (SYSDATE), 'DD')) dom,
                     
    EVENTS e
               WHERE dom
    .theDay BETWEEN TO_CHAR (e.begin_dt'DD') AND TO_CHAR (e.end_dt,
                                                                                
    'DD')
                 AND (   
    TO_CHAR (SYSDATE'MM') = TO_CHAR (e.begin_dt'MM')
                      OR 
    TO_CHAR (SYSDATE'MM') = TO_CHAR (e.end_dt'MM')
                     )) 
    ev,
             (
    SELECT     LEVEL TheDay
                    FROM DUAL
              CONNECT BY LEVEL 
    <= TO_CHAR (LAST_DAY (SYSDATE), 'DD')) all_dom
       WHERE all_dom
    .theDay ev.theDay(+)
    ORDER BY all_dom.theDay 
    JoeB
    save disk space, use smaller fonts

Posting Permissions

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