Get number of days worked in quarter by quarter Oracle 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!

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 ('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') -- Monday to Friday only
GROUP BY
    e.employee
  , to_char(d.date_value,'YYYY')  || '-Q' || to_char(d.date_value,'Q') 

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.

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 <= 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, 'D' ) not in (7)

And you can get the quarter for a given date using TO_CHAR(dr.query_date , 'Q')

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 <= TRUNC(:end_date) - TRUNC(:start_date) + 1
),
employee as ( 
select 'x' as  employee,       to_date('1/1/2023','dd/mm/rrrr')  date_from,   to_date('9/2/2023','dd/mm/rrrr') 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;

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.

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, '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

Which, for the sample data:

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;

Outputs:

fiddle