Re: SQL: Listing the dates for a particular day of the week in a range

From: Matt Anderson <matta576_at_gmail.com>
Date: Tue, 16 Dec 2014 20:45:39 +0000
Message-ID: <CAD6kJfB1kOX=Kwwzv9v6SxZ6rLwRLMukk6ckS5FutZNbg06-VQ_at_mail.gmail.com>

I assumed the date range would only ever be one week just to give you a quick example of NEXT_DAY...

SQL> run test.sql
  1 WITH x

  2       AS ( (SELECT 'M' date_str,
  3                    TO_DATE ('12/14/2014', 'MM/DD?YYYY') start_date,
  4                    TO_DATE ('12/20/2014', 'MM/DD?YYYY') end_date
  5               FROM DUAL)
  6           UNION
  7           (SELECT 'T' date_str,
  8                   TO_DATE ('12/14/2014', 'MM/DD?YYYY') start_date,
  9                   TO_DATE ('12/20/2014', 'MM/DD?YYYY') end_date
 10              FROM DUAL)
 11           UNION
 12           (SELECT 'W' date_str,
 13                   TO_DATE ('12/14/2014', 'MM/DD?YYYY') start_date,
 14                   TO_DATE ('12/20/2014', 'MM/DD?YYYY') end_date
 15              FROM DUAL)
 16           UNION
 17           (SELECT 'TR' date_str,
 18                   TO_DATE ('12/14/2014', 'MM/DD?YYYY') start_date,
 19                   TO_DATE ('12/20/2014', 'MM/DD?YYYY') end_date
 20              FROM DUAL)
 21           UNION
 22           (SELECT 'MWF' date_str,
 23                   TO_DATE ('12/14/2014', 'MM/DD?YYYY') start_date,
 24                   TO_DATE ('12/20/2014', 'MM/DD?YYYY') end_date
 25              FROM DUAL)),
 26       y
 27       AS (SELECT 'M' DOW, 'MONDAY' DAY_OF_WEEK FROM DUAL
 28           UNION
 29           SELECT 'T' DOW, 'TUESDAY' DAY_OF_WEEK FROM DUAL
 30           UNION
 31           SELECT 'W' DOW, 'WEDNESDAY' DAY_OF_WEEK FROM DUAL
 32           UNION
 33           SELECT 'R' DOW, 'THURSDAY' DAY_OF_WEEK FROM DUAL
 34           UNION
 35           SELECT 'F' DOW, 'FRIDAY' DAY_OF_WEEK FROM DUAL)
 36  SELECT date_str,
 37         start_date,
 38         end_date,
 39         NEXT_DAY (start_date, y1.day_of_week) first_date,
 40         NEXT_DAY (start_date, y2.day_of_week) second_date,
 41         NEXT_DAY (start_date, y3.day_of_week) third_date
 42    FROM x,
 43         y y1,
 44         y y2,
 45         y y3
 46   WHERE     SUBSTR (x.date_str, 1, 1) = y1.dow (+)
 47         AND SUBSTR (x.date_str, 2, 1) = y2.dow (+)
 48*        AND SUBSTR (x.date_str, 3, 1) = y3.dow (+)

DAT START_DATE END_DATE FIRST_DATE SECOND_DATE THIRD_DATE

--- ----------- ----------- ----------- ----------- -----------
MWF 14-DEC-2014 20-DEC-2014 15-DEC-2014 17-DEC-2014 19-DEC-2014
M   14-DEC-2014 20-DEC-2014 15-DEC-2014
T   14-DEC-2014 20-DEC-2014 16-DEC-2014
W   14-DEC-2014 20-DEC-2014 17-DEC-2014
TR  14-DEC-2014 20-DEC-2014 16-DEC-2014 18-DEC-2014

On Tue Dec 16 2014 at 1:27:36 PM Ronan Merrick <merrickronan1_at_gmail.com> wrote:

> Hi Anthony,
>
> Do you mean if start date was say, 1st December and end date was say 31st
> December and days was M, you want to return the dates of all the Mondays in
> that set?
>
> Ronan
> On 16 Dec 2014 19:52, "Anthony Ballo" <aballo_at_unm.edu> wrote:
>
>> Hello,
>>
>> I'm working on a scheduling SQL problem. I have a rowset that returns
>> "days" with a "startdate" and "enddate".
>>
>> For example - "days" can have the following values:
>>
>> Days
>> ====
>> M
>> T
>> W
>> TR
>> MWF
>>
>> Where: M = Monday, TR = Tuesday & Thursday and MWF = Monday, Wednesday
>> and Friday.
>>
>> What is the best approach to list the appropriate dates of these days
>> between the "startdate" and "enddate" range? I would mention that
>> "startdate" and "enddate" are the same values for all rows in the rowset if
>> that makes a difference.
>>
>> Any ideas ?
>>
>>
>> Thanks,
>>
>> Anthony
>>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Dec 16 2014 - 21:45:39 CET