Problem solve Get help with specific problems with your technologies, process and projects.

Aggregate functions in a left outer join

I need to create a simple query in MS Access to calculate employee entitled leave, subtract from the sum of leave taken (number of days) and give the available leave days remaining.

The situation you describe sounds like you have two tables. The first table, employees, has one row per employee, and a column for entitled leave.

empid  empname          emptitle   empentitled
 24    P.H.Boss         Manager         15
 36    K.Brown          Analyst         10
 44    E.R.Win          Modeller        10
 58    H.McGillicuddy   DBA             10

The second table, leavetaken, has employee leave taken, and let's assume this is accomplished simply by recording the date the employee was away.

empid  leavedate
 24    2002-09-13
 36    2002-09-13
 58    2002-09-17
 58    2002-09-18
 58    2002-09-19
 24    2002-09-20
 36    2002-09-20
 58    2002-09-24

Assuming there's one row per employee per day of leave taken, your query would be:

select empname
     , ( empentitled 
       - count(leavedate) ) as empremaining
  from employee
left outer
  join leavetaken
    on employee.empid = leavetaken.empid
    by empname
     , empentitled

This query features a left outer join and an aggregate function. The left outer join is required so that all employees are listed, including any like E.R.Win who had no leave taken. An inner join would omit employees which have no matching rows in the leavetaken table. Grouping is required, to produce the aggregate result for each employee. In this case all we need is a count, since we assumed that each row represents one day.

The trick with using the COUNT() aggregate function to produce the correct count in a left outer join is to use COUNT(column) instead of COUNT(*). All aggregate functions except COUNT(*) ignore nulls. In a left outer join, unmatched rows have nulls in any column from the right table. Therefore, COUNT(leavedate) has to produce a zero for employees that have no matching row from the leavedate table. If we use COUNT(*), we get the count of the number of rows, regardless of whether the employee row has a matching leavetaken row. Since grouping occurs at the employee level, COUNT(*) would yield a 1 for every employee that has no matching rows, but otherwise would produce the right answer when the employee does have matching rows.

Suppose that, instead of one row per day of leave taken, the leavetaken table has a start date and end date:

empid  leavestart  leaveend

 24    2002-09-13  2002-09-13
 36    2002-09-13  2002-09-13
 58    2002-09-17  2002-09-19
 24    2002-09-20  2002-09-20
 36    2002-09-20  2002-09-20
 58    2002-09-24  2002-09-24

Now we must use SUM() instead of COUNT(). We're still after the number of days, which we can get individually for each row by doing a date calculation. Note that when the start and end dates are equal, we assume that's one day of leave, but since subtracting the same date from itself would yield zero, we have to add one:

leaveend - leavestart + 1

Fortunately, MS Access automatically produces an answer in days whenever we subtract one date from another, but we could also use the DATEDIFF() function.

So the solution for SUM() would be:

select empname
     , ( empentitled 
       - sum( case
                when leavestart is null
                then 0
                else leaveend - leavestart + 1
              end )   
       ) as empremaining
  from employee
left outer
  join leavetaken
    on employee.empid = leavetaken.empid
    by empname
     , empentitled

Dig Deeper on Oracle and SQL