Search code examples
sqloracle-databasedual-table

Multiple columns from DUAL?

I'm using Oracle 12c. I need to generate dates for the start and end of weeks which begin on Thursday and end the following Wednesday. An example of the output I'd like is -

enter image description here

I have the following SQL to generate the Start Date(s) -

SELECT startdate
  FROM (SELECT next_day(date '2020-03-12' - 1, 'Thursday') + (level - 1) * 7 AS startdate
          FROM dual
       CONNECT BY level <=
                   ((date'2024-03-31' - next_day(date '2020-03-12' - 1, 'Wednesday') + 7) / 7))

and this for End Dates -

(SELECT enddate
   FROM (SELECT next_day(date '2020-03-12' - 1, 'Wednesday') + (level - 1) * 7 as enddate
           FROM dual
         CONNECT BY level <= ((date'2024-03-31' - next_day(date'2020-03-12' - 1, 'Thursday') + 7) / 7)))

Is it even possible to combine these in a single SQL query so the output of the query matches the desired format?

If so, then the addition of the week number would also be rather nice...:)

Solution

  • Generate the start date and then add 6 days to get the end date:

    SELECT startdate,
       startdate + INTERVAL '6' DAY AS enddate,
       week
FROM   (
  SELECT NEXT_DAY(date'2020-03-12' - 1, 'Thursday')
           + ( level - 1 ) * INTERVAL '7' DAY as startdate,
         LEVEL AS week
  FROM   DUAL
  CONNECT BY
         NEXT_DAY(date'2020-03-12' - 1, 'Thursday')
           + ( level - 1 ) * INTERVAL '7' DAY
           + INTERVAL '6' DAY
           <= date'2024-03-31'
)

    Which outputs:

    STARTDATE ENDDATE WEEK
    2020-03-12 00:00:00 2020-03-18 00:00:00 1
    2020-03-19 00:00:00 2020-03-25 00:00:00 2
    2020-03-26 00:00:00 2020-04-01 00:00:00 3
    ... ... ...
    2024-03-07 00:00:00 2024-03-13 00:00:00 209
    2024-03-14 00:00:00 2024-03-20 00:00:00 210
    2024-03-21 00:00:00 2024-03-27 00:00:00 211

    db<>fiddle here