Re: SQL: Listing the dates for a particular day of the week in a range
- From: Matt Anderson <matta576@xxxxxxxxx>
- To: merrickronan1@xxxxxxxxx, aballo@xxxxxxx
- Date: Tue, 16 Dec 2014 20:45:39 +0000
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@xxxxxxxxx>
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@xxxxxxx> 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
>>
>
Other related posts:
