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

From: Arnel Andrada <arnel.andrada_at_ardentsun.com>
Date: Wed, 17 Dec 2014 10:17:07 -0500
Message-ID: <54919E73.3040708_at_ardentsun.com>

Hi Anthony,

Here is a possible solution:

rs is a table which is equivalent to your rowset. It contains a column called ID (presumably, the schedule ID) that uniquely identifies a row (I didn't bother to define it as a primary key). If you don't require it, delete all its occurrences.

t_numdays returns the start date and the number of days in the range for the entire rowset. The query is predicated on your statement that all rows in the rowset have the same start and end dates.

t_dates_in_range consists of all dates in the range, including each date's day of week number (1 to 7, where 1 = Sunday), day of week code (as you've encoded the days; I added N = Sunday and S = Saturday), and week of month number (1 to 5).

t_dates_in_rowset returns the dates, within the range, of the days associated with each row in the rowset.

Note: The order of the day of week codes in rs.days does not matter (for example, "MWF" is equivalent to "FMW").

CREATE TABLE RS
(

  ID  NUMBER(1)         NOT NULL,
  DAYS      VARCHAR2(7 CHAR)  NOT NULL,
  START_DT  DATE              NOT NULL,
  END_DT    DATE              NOT NULL

);

SET DEFINE OFF;
Insert into RS

   (ID, DAYS, START_DT, END_DT)
 Values
   (1, 'MT', TO_DATE('12/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/15/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS')); Insert into RS

   (ID, DAYS, START_DT, END_DT)
 Values
   (2, 'W', TO_DATE('12/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/15/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS')); Insert into RS

   (ID, DAYS, START_DT, END_DT)
 Values
   (3, 'RFMW', TO_DATE('12/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/15/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS')); Insert into RS

   (ID, DAYS, START_DT, END_DT)
 Values
   (4, 'TM', TO_DATE('12/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/15/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS')); COMMIT; WITH
   t_numdays

      AS (SELECT start_dt,
                 (end_dt - start_dt + 1) AS numdays
            FROM rs
           WHERE ROWNUM <= 1),
   t_dates_in_range
      AS (SELECT dt,
                 TO_CHAR(dt, 'd') AS day_no,
                 TRANSLATE(TO_CHAR(dt, 'd'), '1234567', 'NMTWRFS') day_cd,
                 TO_CHAR(dt, 'w') AS week_no
            FROM (SELECT start_dt + ROWNUM - 1 AS dt
                    FROM t_numdays
                    CONNECT BY ROWNUM <= numdays)),
   t_dates_in_rowset
      AS (SELECT rs.id,
                 t.dt,
                 t.day_cd,
                 t.day_no,
                 t.week_no
            FROM rs
                 INNER JOIN t_dates_in_range t
                    ON INSTR(rs.days, t.day_cd) > 0)

SELECT *
  FROM (SELECT id, day_cd, dt, week_no, day_no

          FROM t_dates_in_rowset)
          PIVOT (MIN(dt) FOR week_no IN ('1' AS WEEK_1, '2' AS WEEK_2,
'3' AS WEEK_3,
                                         '4' AS WEEK_4, '5' AS WEEK_5))

 ORDER BY id ASC, day_no ASC;

The result is:

    ID    DAY_CD    DAY_NO    WEEK_1      WEEK_2      WEEK_3     
    WEEK_4      WEEK_5
    1     M         2         12/1/2014   12/8/2014   12/15/2014 
    null        null
    1     T         3         12/2/2014   12/9/2014   null       
    null        null
    2     W         4         12/3/2014   12/10/2014  null       
    null        null
    3     M         2         12/1/2014   12/8/2014   12/15/2014 
    null        null
    3     W         4         12/3/2014   12/10/2014  null       
    null        null
    3     R         5         12/4/2014   12/11/2014  null       
    null        null
    3     F         6         12/5/2014   12/12/2014  null       
    null        null
    4     M         2         12/1/2014   12/8/2014   12/15/2014 
    null        null
    4     T         3         12/2/2014   12/9/2014   null       
    null        null

Modify the above query to satisfy your desired output. For example, without the id (there's no need to remove it from the CTEs in this example)

    SELECT *

      FROM (SELECT day_cd, dt, week_no, day_no
              FROM t_dates_in_rowset)
              PIVOT (MIN(dt) FOR week_no IN ('1' AS WEEK_1, '2' AS
    WEEK_2, '3' AS WEEK_3, '4' AS WEEK_4, '5' AS WEEK_5))
     ORDER BY day_no ASC;

the result set is:

    DAY_CD DAY_NO WEEK_1 WEEK_2 WEEK_3 WEEK_4     WEEK_5

    M         2         12/1/2014   12/8/2014   12/15/2014  null        null
    T         3         12/2/2014   12/9/2014   null        null        null
    W         4         12/3/2014   12/10/2014  null        null        null
    R         5         12/4/2014   12/11/2014  null        null        null
    F         6         12/5/2014   12/12/2014  null        null        null

Regards,
Arnel

On 12/16/2014 2:51 PM, Anthony Ballo 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
> ��i��0���zX���+��n��{�+i�^l===

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

Received on Wed Dec 17 2014 - 16:17:07 CET