创建两个日期之间的星期序列,通过计算每个星期五。

huangapple go评论64阅读模式
英文:

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&gt; with test (job_id, start_date, end_date) as
  2    (select 27, date &#39;2023-02-01&#39;, date &#39;2023-03-01&#39; from dual union all
  3     select 30, date &#39;2023-02-06&#39;, date &#39;2023-02-25&#39; from dual
  4    )

Query begins here:

  5  select job_id,
  6    column_value as seq#,
  7    next_day(start_date -1 , &#39;FRIDAY&#39;) + ((column_value - 1) * 7) as datum
  8  from test cross join
  9    table(cast(multiset(select level from dual
 10                        connect by level &lt;= (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&gt;

huangapple
  • 本文由 发表于 2023年2月27日 06:45:03
  • 转载请务必保留本文链接:https://go.coder-hub.com/75575445.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定