获取每个季度的工作天数的Oracle SQL代码。

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

Get number of days worked in quarter by quarter Oracle SQL

问题

以下是翻译好的部分:

"我如何获得以下结果,查询将通过提供开始和结束日期来运行,例如用户可以在2023年1月1日至2023年6月20日之间运行此查询。

表格数据:

员工       开始日期     结束日期   
x         2023/1/1   2023/2/9
y         2023/10/1  2023/3/30 

预期结果:

员工       开始日期     结束日期       年份    季度                      备注
X         2023/1/1   2023/2/9     2023      第1季度         员工X在90天中活跃了40天。
y         2023/10/1  2023/3/30    2022      第4季度         员工y在90天中活跃了90天。
y         2023/10/1  2023/3/30    2023      第1季度         员工y在90天中活跃了90天。

我需要在没有PL/SQL的情况下实现这个,是否可能?

谢谢!"

英文:

How do I get below results, query will be run by giving begin and end dates, for example user can run this query from 1/1/2023 - 6/20/2023

table data:

employee  date_from  date_to   
x         1/1/2023   2/9/2023
y         10/1/2023  3/30/2023 

expected results:

employee  date_from  date_to       year   quarter                   comments
X         1/1/2023   2/9/2023     2023      q1         Employee X was active 40 out of the 90 days.
y         10/1/2023  3/30/2023    2022      q4         Employee y was active 90 out of the 90 days.
y         10/1/2023  3/30/2023    2023      q1         Employee y was active 90 out of the 90 days.

I need this with out PL/SQL is this possible?

Thank you!

答案1

得分: 0

是的,可以在不使用PL/SQL的情况下实现。您需要一个日期表(可以“即时生成”),但通常也会将“日历”存储起来。从存储或生成的日历中,您可以排除周末,然后您的查询将使用该日历作为“from表”,并通过员工范围的日期范围内的日期来连接数据,然后通过分组计算每个季度中的日历日期。

CREATE TABLE mytable (
  employee VARCHAR2(50),
  date_from DATE,
  date_to DATE
);

INSERT INTO mytable (employee, date_from, date_to) 
  VALUES ('x', TO_DATE('1/1/2023', 'MM/DD/YYYY'), TO_DATE('2/9/2023', 'MM/DD/YYYY'));
INSERT INTO mytable (employee, date_from, date_to) 
  VALUES ('y', TO_DATE('10/1/2023', 'MM/DD/YYYY'), TO_DATE('3/30/2023', 'MM/DD/YYYY'));

WITH d AS (
  SELECT 
    TRUNC(DATE '2022-01-01') + (LEVEL - 1) AS date_value
  FROM 
    dual
  CONNECT BY LEVEL <= (DATE '2023-07-01' - DATE '2022-01-01') + 1
)
SELECT 
    e.employee
  , to_char(d.date_value,'YYYY')  || '-Q' || to_char(d.date_value,'Q')  Qtr
  , count(d.date_value) days
FROM d
  INNER JOIN mytable e on d.date_value between e.date_from and e.date_to
WHERE to_char(d.date_value,'D') in ('1','2','3','4','5') -- 仅限周一至周五
GROUP BY
    e.employee
  , to_char(d.date_value,'YYYY')  || '-Q' || to_char(d.date_value,'Q')

请注意,要显示整体从/到日期之间的所有季度,您应该使用左连接而不是上面显示的内连接,并且需要相应更改WHERE子句。

英文:

Yes it is possible without plsql. You need a table of dates (which can be generated "on the fly") but it is not unusual to store a "calendar" instead. From that stored or generated calendar, you can exclude weekends for example. Then, your query uses that calendar as the "from table" and the employee is data joined via the date falling inside the from/to date of the employee ranges, then via a group by, count the calendar dates in each quarter.

CREATE TABLE mytable (
  employee VARCHAR2(50),
  date_from DATE,
  date_to DATE
);

INSERT INTO mytable (employee, date_from, date_to) 
  VALUES (&#39;x&#39;, TO_DATE(&#39;1/1/2023&#39;, &#39;MM/DD/YYYY&#39;), TO_DATE(&#39;2/9/2023&#39;, &#39;MM/DD/YYYY&#39;));
INSERT INTO mytable (employee, date_from, date_to) 
  VALUES (&#39;y&#39;, TO_DATE(&#39;10/1/2023&#39;, &#39;MM/DD/YYYY&#39;), TO_DATE(&#39;3/30/2023&#39;, &#39;MM/DD/YYYY&#39;));


WITH d AS (
  SELECT 
    TRUNC(DATE &#39;2022-01-01&#39;) + (LEVEL - 1) AS date_value
  FROM 
    dual
  CONNECT BY LEVEL &lt;= (DATE &#39;2023-07-01&#39; - DATE &#39;2022-01-01&#39;) + 1
)
SELECT 
    e.employee
  , to_char(d.date_value,&#39;YYYY&#39;)  || &#39;-Q&#39; || to_char(d.date_value,&#39;Q&#39;)  Qtr
  , count(d.date_value) days
FROM d
  INNER JOIN mytable e on d.date_value between e.date_from and e.date_to
WHERE to_char(d.date_value,&#39;D&#39;) in (&#39;1&#39;,&#39;2&#39;,&#39;3&#39;,&#39;4&#39;,&#39;5&#39;) -- Monday to Friday only
GROUP BY
    e.employee
  , to_char(d.date_value,&#39;YYYY&#39;)  || &#39;-Q&#39; || to_char(d.date_value,&#39;Q&#39;) 
EMPLOYEE QTR DAYS
x 2023-Q1 29

fiddle

Note To display all quarters between the overall from/to dates you would use a LEFT JOIN instead if the inner join shown above, and would also need to change the where clause accordingly.

答案2

得分: 0

以下是已翻译的内容:

可以使用以下查询获取两个给定日期之间的所有日期:

SELECT TRUNC(:start_date) + (LEVEL - 1) AS query_date
FROM dual
CONNECT BY LEVEL <= TRUNC(:end_date) - TRUNC(:start_date) + 1

还可以通过添加以下条件来过滤掉星期日(我认为星期几的编号可能是特定于国家的):

WHERE TO_CHAR(dr.query_date, 'D') NOT IN (7)

您可以使用 TO_CHAR(dr.query_date, 'Q') 来获取给定日期的季度。

因此,最终的查询应该类似于以下内容:

WITH date_range AS (
  SELECT TRUNC(:start_date) + (LEVEL - 1) AS query_date
  FROM dual
  CONNECT BY LEVEL <= TRUNC(:end_date) - TRUNC(:start_date) + 1
),
employee AS ( 
  SELECT 'x' AS employee, TO_DATE('1/1/2023', 'dd/mm/rrrr') AS date_from, TO_DATE('9/2/2023', 'dd/mm/rrrr') AS date_to FROM dual
  UNION ALL 
  SELECT 'y', TO_DATE('1/10/2022', 'dd/mm/rrrr'), TO_DATE('30/3/2023', 'dd/mm/rrrr') FROM dual
)
SELECT
  e.employee,
  e.date_from,
  e.date_to,
  EXTRACT(YEAR FROM dr.query_date) AS year,
  'q' || TO_CHAR(dr.query_date, 'Q') AS quarter,
  'Employee ' || e.employee || ' was active ' || COUNT(dr.query_date) || ' out of the 90 days.' AS comments
FROM
  employee e
JOIN
  date_range dr ON dr.query_date BETWEEN TRUNC(e.date_from) AND TRUNC(e.date_to)
WHERE TO_CHAR(dr.query_date, 'D') NOT IN (7)
GROUP BY
  e.employee,
  e.date_from,
  e.date_to,
  EXTRACT(YEAR FROM dr.query_date),
  'q' || TO_CHAR(dr.query_date, 'Q')
ORDER BY
  e.employee,
  e.date_from;

结果:
对于范围:

1/10/2022 - 20/06/2023

x	1/1/2023	9/2/2023	2023	q1	Employee x was active 35 out of the 90 days.
y	1/10/2022	30/3/2023	2022	q4	Employee y was active 78 out of the 90 days.
y	1/10/2022	30/3/2023	2023	q1	Employee y was active 77 out of the 90 days.
英文:

You can get all the dates between two given dates using the following query

SELECT TRUNC(:start_date) + (LEVEL - 1) AS query_date
      FROM dual
      CONNECT BY LEVEL &lt;= TRUNC(:end_date) - TRUNC(:start_date) + 1

Also you could filter out Sundays by adding (I think the numbering is country specific)

where TO_CHAR(dr.query_date, &#39;D&#39; ) not in (7)

And you can get the quarter for a given date using TO_CHAR(dr.query_date , &#39;Q&#39;)

Thus the final query should look something like this:

WITH date_range AS (
  SELECT TRUNC(:start_date) + (LEVEL - 1) AS query_date
  FROM dual
  CONNECT BY LEVEL &lt;= TRUNC(:end_date) - TRUNC(:start_date) + 1
),
employee as ( 
select &#39;x&#39; as  employee,       to_date(&#39;1/1/2023&#39;,&#39;dd/mm/rrrr&#39;)  date_from,   to_date(&#39;9/2/2023&#39;,&#39;dd/mm/rrrr&#39;) date_to from dual
union all 
select &#39;y&#39;,       to_date( &#39;1/10/2022&#39;,&#39;dd/mm/rrrr&#39;), to_date(&#39;30/3/2023&#39;,&#39;dd/mm/rrrr&#39;)   from dual
)
SELECT
  e.employee,
  e.date_from,
  e.date_to,
  EXTRACT(YEAR FROM dr.query_date) AS year,
  &#39;q&#39; || TO_CHAR(dr.query_date , &#39;Q&#39;) AS quarter
  ,&#39;Employee &#39; || e.employee || &#39; was active &#39; || COUNT(dr.query_date) || &#39; out of the &#39; || 90 || &#39; days.&#39; AS comments
FROM
  employee e
JOIN
  date_range dr ON dr.query_date BETWEEN TRUNC(e.date_from) AND TRUNC(e.date_to)
  where TO_CHAR(dr.query_date, &#39;D&#39; ) not in (7)
GROUP BY
  e.employee,
  e.date_from,
  e.date_to,
  EXTRACT(YEAR FROM dr.query_date),
  &#39;q&#39; || TO_CHAR(dr.query_date, &#39;Q&#39;)
ORDER BY
  e.employee,
  e.date_from;

Results:
For the range:

1/10/2022 - 20/06/2023

x	1/1/2023	9/2/2023	2023	q1	Employee x was active 35 out of the 90 days.
y	1/10/2022	30/3/2023	2022	q4	Employee y was active 78 out of the 90 days.
y	1/10/2022	30/3/2023	2023	q1	Employee y was active 77 out of the 90 days.

答案3

得分: 0

Here is the code with the provided SQL query and sample data translation:

不需要生成开始和结束日期之间的所有日期;相反,您可以更高效地使用递归查询生成连续的季度:

```lang-sql
WITH quarters (employee, date_from, quarter_start, date_to) AS (
  SELECT employee,
         date_from,
         TRUNC(date_from, 'Q'),
         date_to
  FROM   employees
UNION ALL
  SELECT employee,
         date_from,
         ADD_MONTHS(quarter_start, 3),
         date_to
  FROM   quarters
  WHERE  ADD_MONTHS(quarter_start, 3) <= date_to
)
SEARCH DEPTH FIRST BY employee SET order_id
SELECT employee,
       date_from,
       date_to,
       EXTRACT(YEAR FROM quarter_start) AS year,
       TO_CHAR(quarter_start, '"Q"Q') AS quarter,
       LEAST(date_to + 1, ADD_MONTHS(quarter_start, 3))
       - GREATEST(quarter_start, date_from) AS days,
       'Employee ' || employee || ' was employed for '
       || (LEAST(date_to + 1, ADD_MONTHS(quarter_start, 3))
          - GREATEST(quarter_start, date_from))
       || ' out of the ' || (ADD_MONTHS(quarter_start, 3) - quarter_start)
       || ' days.' AS comments
FROM   quarters

对于样本数据:

CREATE TABLE employees (employee, date_from, date_to) AS
SELECT 'x', DATE '2023-01-01', DATE '2023-02-09' FROM DUAL UNION ALL
SELECT 'y', DATE '2022-10-01', DATE '2023-03-03' FROM DUAL;

输出:

EMPLOYEE DATE_FROM DATE_TO YEAR QUARTER DAYS COMMENTS
x 2023-01-01 00:00:00 2023-02-09 00:00:00 2023 Q1 40 Employee x was employed for 40 out of the 90 days.
y 2022-10-01 00:00:00 2023-03-03 00:00:00 2022 Q4 92 Employee y was employed for 92 out of the 92 days.
y 2022-10-01 00:00:00 2023-03-03 00:00:00 2023 Q1 62 Employee y was employed for 62 out of the 90 days.

[fiddle](https://dbfiddle.uk/kkTy5c6n)
英文:

You do not need to generate all the days between the start and end dates; instead you can be more efficient and just generate successive quarters using a recursive query:

WITH quarters (employee, date_from, quarter_start, date_to) AS (
  SELECT employee,
         date_from,
         TRUNC(date_from, &#39;Q&#39;),
         date_to
  FROM   employees
UNION ALL
  SELECT employee,
         date_from,
         ADD_MONTHS(quarter_start, 3),
         date_to
  FROM   quarters
  WHERE  ADD_MONTHS(quarter_start, 3) &lt;= date_to
)
SEARCH DEPTH FIRST BY employee SET order_id
SELECT employee,
       date_from,
       date_to,
       EXTRACT(YEAR FROM quarter_start) AS year,
       TO_CHAR(quarter_start, &#39;&quot;Q&quot;Q&#39;) AS quarter,
       LEAST(date_to + 1, ADD_MONTHS(quarter_start, 3))
       - GREATEST(quarter_start, date_from) AS days,
       &#39;Employee &#39; || employee || &#39; was employed for &#39;
       || (LEAST(date_to + 1, ADD_MONTHS(quarter_start, 3))
          - GREATEST(quarter_start, date_from))
       || &#39; out of the &#39; || (ADD_MONTHS(quarter_start, 3) - quarter_start)
       || &#39; days.&#39; AS comments
FROM   quarters

Which, for the sample data:

CREATE TABLE employees (employee, date_from, date_to) AS
SELECT &#39;x&#39;, DATE &#39;2023-01-01&#39;, DATE &#39;2023-02-09&#39; FROM DUAL UNION ALL
SELECT &#39;y&#39;, DATE &#39;2022-10-01&#39;, DATE &#39;2023-03-03&#39; FROM DUAL;

Outputs:

EMPLOYEE DATE_FROM DATE_TO YEAR QUARTER DAYS COMMENTS
x 2023-01-01 00:00:00 2023-02-09 00:00:00 2023 Q1 40 Employee x was employed for 40 out of the 90 days.
y 2022-10-01 00:00:00 2023-03-03 00:00:00 2022 Q4 92 Employee y was employed for 92 out of the 92 days.
y 2022-10-01 00:00:00 2023-03-03 00:00:00 2023 Q1 62 Employee y was employed for 62 out of the 90 days.

fiddle

huangapple
  • 本文由 发表于 2023年7月20日 11:09:28
  • 转载请务必保留本文链接:https://go.coder-hub.com/76726443.html
匿名

发表评论

匿名网友

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

确定