英文:
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 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!
英文:
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!
答案1
得分: 1
以下是翻译好的代码部分:
这是一个选项:
示例数据:
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 )
查询从这里开始:
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#;
希望这对你有帮助。
英文:
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>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论