How do I perform the following SQL (or PL/SQL) Query

November 17, 2021

If facing the following requirement: The user needs a table where he can see an employeeID, employeeName and the days where employees weren’t working. The raw data is stored in the following tables:

Employee: Emp_ID, Emp_Name

WorkDays: Emp_ID, Date

The process is that everytime an employee has worked, his ID and the date of the day is inserted into the WorkDays table.

Now I need a SQL or PL/SQL Query where I choose a date range (f.e. between 2021-10-01 and 2021-11.01) and for every employee who hasn’t worked on at least 1 day during that time (meaning there is a row "missing" in the WorkDays table for a particular ID and Date), he is displayed in one row with the dates he hasn’t worked. The dates are all in one column of the row, f.e. seperated by commas.

For better understanding, if there are two employees who haven’t worked during that time the final table should look something like this:

Emp_ID | Emp_Name |             Hasn't Worked               |
1      | John     |      2021-10-01, 2021-10-03, 2021-10-06 |
3      | Mary     |      2021-10-02, 2021-10-03             |

I don’t want my code to get done for me but instead get help with the problem of querying a table where i have to loop trough the data and display multiple results (the dates) in one corresponding row.

Thanks a lot for help!
Jack

>Solution :

Use a row generator to create a calendar and then use a PARTITION OUTER JOIN and find the non-matched rows and then aggregate:

WITH calendar (day) AS (
  SELECT DATE '2021-10-01' FROM DUAL
UNION ALL
  SELECT day + 1 FROM calendar WHERE day < DATE '2021-11-01'
)
SELECT w.emp_id,
       MAX(emp_name) AS emp_name,
       LISTAGG(TO_CHAR(c.day, 'YYYY-MM-DD'), ', ') WITHIN GROUP (ORDER BY c.day)
         AS non_work_days
FROM   calendar c
       LEFT OUTER JOIN workdays w
       PARTITION BY (w.emp_id)
       ON (c.day = w."DATE")
       LEFT OUTER JOIN employee e
       ON (w.emp_id = e.emp_id)
WHERE  w."DATE" IS NULL
GROUP BY w.emp_id;

or you can use CROSS JOIN:

WITH calendar (day) AS (
  SELECT DATE '2021-10-01' FROM DUAL
UNION ALL
  SELECT day + 1 FROM calendar WHERE day < DATE '2021-11-01'
)
SELECT e.emp_id,
       MAX(e.emp_name) AS emp_name,
       LISTAGG(TO_CHAR(c.day, 'YYYY-MM-DD'), ', ') WITHIN GROUP (ORDER BY c.day)
         AS non_work_days
FROM   calendar c
       CROSS JOIN employee e
       LEFT OUTER JOIN workdays w
       ON (c.day = w."DATE" AND e.emp_id = w.emp_id)
WHERE  w."DATE" IS NULL
GROUP BY e.emp_id;

Which, for the sample data:

CREATE TABLE Employee (Emp_ID, Emp_Name) AS
SELECT 1, 'Alice' FROM DUAL UNION ALL
SELECT 2, 'Beryl' FROM DUAL;

CREATE TABLE WorkDays (Emp_ID, "DATE") AS
WITH calendar (dt) AS (
  SELECT DATE '2021-10-01' + LEVEL - 1 FROM DUAL CONNECT BY LEVEL <= 32
)
SELECT 1, dt FROM calendar WHERE dt NOT IN (DATE '2021-10-01', DATE '2021-10-03', DATE '2021-10-06')
UNION ALL
SELECT 2, dt FROM calendar WHERE dt NOT IN (DATE '2021-10-02', DATE '2021-10-03')

Both output: