Question 246548 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql query for employee assignments when peer(s) are absent

I have a mysql table with the following structure:

Assign Pri Sec Ter Qua
21 Tom Mark Sally Tammy
60 Sally Ace Tom Mark
170 Mark Sally Ace Tammy
220 Ace Tammy Tom Sally
17 Tammy Sally Mark Ace
439 Sally Tom Tammy Mark

I want to display the employees assignments based on primary assignments and if there are any absences, based on their status as secondary, tertiary or quaternary. So for Sally, her assignments are 60 and 439 every day. But if Tom and Mark are absent, her assignments would be 60, 439, 21 and 170. What I have only pulls assignments based on if Sally is secondary, tertiary or quaternary but doesn’t take into account if, when Sally is tertiary or quaternary, if the person in front of them (secondary or tertiary) is in the office and has taken that assignment. So with my query, she would get assignments, 60, 439, 21, 170, 220 and 17.

This is what I have so far that doesn’t have the logic I need.

SELECT
    Assign
FROM
    assign_table
WHERE
    Pri = 'Tom'
    AND
    sec = 'Sally'
    OR
    pri = 'Tom'
    AND
    ter = 'Sally'
    OR
    pri = 'Tom'
    AND qua = 'Sally'
    OR
    pri = 'Sally'
ORDER BY
    Assign;

I should mention I have another table with employee names and an absent column that contains 0 or 1 if they are in the office or absent that particular day (employee_credentials).

Any help is appreciated.

Tags:

2

Answers


  1. 0

    Join the assign_table and employee_credentials and use the conditional logic to determine the assignments based on absence of status of other employees. May be this code would work

    SELECT DISTINCT a.Assign
FROM assign_table a
JOIN employee_credentials e ON a.Pri = e.EmployeeName
WHERE e.Absent = 0
UNION
SELECT DISTINCT a.Assign
FROM assign_table a
JOIN employee_credentials e ON a.Sec = e.EmployeeName
WHERE e.Absent = 0
AND a.Pri NOT IN (SELECT EmployeeName FROM employee_credentials WHERE Absent = 0)
UNION
SELECT DISTINCT a.Assign
FROM assign_table a
JOIN employee_credentials e ON a.Ter = e.EmployeeName
WHERE e.Absent = 0
AND a.Pri NOT IN (SELECT EmployeeName FROM employee_credentials WHERE Absent = 0)
AND a.Sec NOT IN (SELECT EmployeeName FROM employee_credentials WHERE Absent = 0)
UNION
SELECT DISTINCT a.Assign
FROM assign_table a
JOIN employee_credentials e ON a.Qua = e.EmployeeName
WHERE e.Absent = 0
AND a.Pri NOT IN (SELECT EmployeeName FROM employee_credentials WHERE Absent = 0)
AND a.Sec NOT IN (SELECT EmployeeName FROM employee_credentials WHERE Absent = 0)
AND a.Ter NOT IN (SELECT EmployeeName FROM employee_credentials WHERE Absent = 0)
ORDER BY Assign;

    we can use UNION to combine.
    Thanks

    Login or Signup to reply.
  2. 0

    If you are using MySQL ≥ 8.0.14, you can use CROSS JOIN LATERAL to unpivot the data in assign_table. See Lateral Derived Tables.

    Assuming your employee_credentials table looks like this:

    EmployeeName Dt Absent
    Ace 2023-09-17 0
    Mark 2023-09-17 1
    Sally 2023-09-17 0
    Tammy 2023-09-17 0
    Tom 2023-09-17 1

    You can use the following query to unpivot assign_table and join to employee_credentials to determine whether the employee is absent, or not. You then use ROW_NUMBER() to assign a new priority for each assignment.

    WITH assign_not_absent AS (
    SELECT a.Assign, x.EmployeeName, x.Priority, ROW_NUMBER() OVER (PARTITION BY a.Assign ORDER BY x.Priority) AS NewPriority
    FROM assign_table a
    CROSS JOIN LATERAL (
        SELECT Pri, 1 UNION ALL
        SELECT Sec, 2 UNION ALL
        SELECT Ter, 3 UNION ALL
        SELECT Qua, 4
    ) x (EmployeeName, Priority)
    JOIN employee_credentials ec
        ON x.EmployeeName = ec.EmployeeName
        AND ec.Absent = 0
        AND ec.Dt = '2023-09-17'
)
SELECT * FROM assign_not_absent WHERE NewPriority = 1 AND EmployeeName = 'Sally';

    Output:

    Assign EmployeeName Priority NewPriority
    21 Sally 3 1
    60 Sally 1 1
    170 Sally 2 1
    439 Sally 1 1

    Your assign_table holds an ordered many-to-many relationship between employees and tasks/assignments, so a better structure might look like:

    employees

    id name other_columns
    1 Ace …
    2 Mark …
    3 Sally …
    4 Tammy …
    5 Tom …

    assignments_employees

    assignment_id employee_id priority
    21 5 1
    21 2 2
    21 3 3
    21 4 4
    60 3 1
    etc

    employee_absence

    employee_id dt absent
    1 2023-09-17 0
    2 2023-09-17 1
    3 2023-09-17 0
    4 2023-09-17 0
    5 2023-09-17 1

    With this structure the query becomes:

    WITH assign_not_absent AS (
    SELECT ae.assignment_id, ae.employee_id, ae.priority, ROW_NUMBER() OVER (PARTITION BY ae.assignment_id ORDER BY ae.priority) AS new_priority
    FROM assignments_employees ae
    JOIN employee_absence ea
        ON ae.employee_id = ea.employee_id
        AND ea.absent = 0
        AND ea.dt = '2023-09-17'
)
SELECT ana.assignment_id, e.name, ana.priority, ana.new_priority
FROM assign_not_absent ana
JOIN employees e
    ON ana.employee_id = e.id
WHERE ana.new_priority = 1
AND e.name = 'Sally';

    Output:

    assignment_id name priority new_priority
    21 Sally 3 1
    60 Sally 1 1
    170 Sally 2 1
    439 Sally 1 1

    The CTEs in both of these queries can be made more efficient by restricting assignments to those to which Sally is already assigned.

    Here’s a db<>fiddle to play around with.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search