Creating a Sequence of Weeks between two dates by counting each Friday

I'm using Oracle-SQL to accomplish this however.

An example is easiest be easiest to explain, Let's say I have a job_id (27) that starts Feb-1-2023 (Wednesday) and ends March-1-2023 (also Wednesday). All of these columns, id, start_date, end_date, exist in the same table.

The table would look something like:

Job_id	Seq#	Date
27	1	Feb-3-2023
27	2	Feb-10-2023
27	3	Feb-17-2023
27	4	Feb-24-2023
27	5	Mar-3-2023

Of course this would need to be done for all job_id's in the jobs table.

The purpose of this is to create a Gaussian bell curve of hours worked, given an expected peak_time and std. dev. This way I will be able to have a slight forecasting simulator.

But for now I need this query to accomplish this! Any suggestions?

EDIT: I was able to find the first Friday after the Start_date with this query,

SELECT start_date, NEXT_DAY(start_date - 1, 'FRIDAY') AS first_friday
FROM JOBS;

as a starting point,

Any advice is appreciated!

Solution

Here's one option:

Sample data:

SQL> with test (job_id, start_date, end_date) as
  2    (select 27, date '2023-02-01', date '2023-03-01' from dual union all
  3     select 30, date '2023-02-06', date '2023-02-25' from dual
  4    )

Query begins here:

  5  select job_id,
  6    column_value as seq#,
  7    next_day(start_date -1 , 'FRIDAY') + ((column_value - 1) * 7) as datum
  8  from test cross join
  9    table(cast(multiset(select level from dual
 10                        connect by level <= (end_date - start_date) / 7 + 1
 11                       ) as sys.odcinumberlist))
 12  order by job_id, seq#;

    JOB_ID       SEQ# DATUM
---------- ---------- -----------
        27          1 Feb-03-2023
        27          2 Feb-10-2023
        27          3 Feb-17-2023
        27          4 Feb-24-2023
        27          5 Mar-03-2023
        30          1 Feb-10-2023
        30          2 Feb-17-2023
        30          3 Feb-24-2023

8 rows selected.

SQL>