Downloads: Sample Database

There are times when you need to perform a calculation on values stored in two different tables. The first value is stored in a summary level table, and the second is stored in a detail table. I will use the Adventure Works employee tables to demonstrate.

Let’s say you need to calculate the accrued vacation time for your employees that exceeds the company limit of 120 hours and derive the total liability to the company in terms of dollars. Using the Adventure Works database, that would require three tables: HumanResources.Employee, Person.Contact, and HumanResources.EmployeePayHistory. To start, we know we need to identify the employee so we include the EmployeeId field from the Employee table and the LastName and FirstName fields from the Contact table. Make sure and include the schema when declaring the tables, as this is required for any schema that is not ‘dbo’. This is a good practice anyway. Also, note the use of the table aliases ‘e’ and ‘c’ to save typing and add clarity.

select      e.EmployeeId
            ,c.LastName + ', ' + c.FirstName as EmployeeName
from        HumanResources.Employee e
inner join  Person.Contact c on e.ContactId = c.ContactId

Now we can easily calculate the accrued hours by adding two fields from the employees table: VacationHours and SickLeaveHours. We are assuming the business definition for the field is ‘hours accrued’, but this would need to be confirmed with the business owner. Since the column does not allow nulls, we do not need to trap for that using the ISNULL or COALESCE function. However, don’t forget to subtract the acceptable limit of 120 so we only see the delta.

select      e.EmployeeId
            ,c.LastName + ', ' + c.FirstName as EmployeeName
            ,e.VacationHours + e.SickLeaveHours - 120 as AccruedPtoHoursInExcess
from        HumanResources.Employee e
inner join  Person.Contact c on e.ContactId = c.ContactId

Since this will result in negative numbers for employees that have not accrued at least 120 hours, we might decide to trap that using the CASE statement and only return either a zero or a positive number. For our purposes, though, it makes sense to just show employees with a positive number. We will add a WHERE clause to limit our list. As a good practice, you should limit your results earlier in your development process or risk overburdening your database with an oversize result set. If you do not what your WHERE clause will be, you can still add a TOP declaration to your SELECT statement and limit the results to a manageable number of rows.  It is also a good idea to end your T-SQL statements with a semicolon, so I added one to our query.

select      e.EmployeeId
            ,c.LastName + ', ' + c.FirstName as EmployeeName
            ,e.VacationHours + e.SickLeaveHours - 120 as AccruedPtoHoursInExcess
from        HumanResources.Employee e
inner join  Person.Contact c on e.ContactId = c.ContactId
where       e.VacationHours + e.SickLeaveHours > 120;

Now for the tricky part. The pay rate for the employees is stored in the EmployeePayHistory table, but the table has a time component. In other words, there is a record added every time the pay rate changes. If we just add the table like follows, then we risk returning more than one record for a single employee. Note the new formula to caculate liability. I use parenthesis to enforce the correct order of operations:

select      e.EmployeeId
            ,c.LastName + ', ' + c.FirstName as EmployeeName
            ,VacationHours + SickLeaveHours - 120 as AccruedPtoHoursInExcess
            ,cast((VacationHours + SickLeaveHours - 120) * eph.Rate as decimal(6,2)) as Liability
from        HumanResources.Employee e
inner join  Person.Contact c on e.ContactId = c.ContactId
inner join  HumanResources.EMployeePayHistory eph on e.EmployeeId = eph.EmployeeId
where       VacationHours + SickLeaveHours > 120;

In my database, the latest query increases the row count from 97 to 99 because Rob Walters ends up with three records. We could just assume that Rob’s pay rate always increases, but that is prone to error. Instead, we need to add a subquery to only filter on the latest pay rates for each employee as determined by the RateChangeDate. The following query provides the filter we need:

select   EmployeeId
         ,max(RateChangeDate) as RateChangeDate
from     HumanResources.EmployeePayHistory
group by EmployeeId

Now, all we need to do is add this into our main query so that it acts like a filter table, and join the original  EmployeePayHistory table to the derived filter table instead:

select      e.EmployeeId
            ,c.LastName + ', ' + c.FirstName as EmployeeName
            ,VacationHours + SickLeaveHours - 120 as AccruedPtoHoursInExcess
            ,cast((VacationHours + SickLeaveHours - 120) * eph.Rate as decimal(6,2)) as Liability
from        HumanResources.Employee e
inner join  Person.Contact c on e.ContactId = c.ContactId
inner join  (
                select   EmployeeId
                         ,max(RateChangeDate) as RateChangeDate
                from     HumanResources.EmployeePayHistory
                group by EmployeeId
            ) f on e.EmployeeId = f.EmployeeId
inner join  HumanResources.EmployeePayHistory eph on f.EmployeeId = eph.EmployeeId
                                                    and f.RateChangeDate = eph.RateChangeDate
where       VacationHours + SickLeaveHours > 120;

The query now returns our original 97 rows and shows the current liability which is defined as ‘the current pay rate times the accrued PTO over 120 hours’.