This post adds to a previous post:Working with additive values in tables with different levels of detail

In the last post, we used a subquery to only show the latest value in a table that has a time component. The table holds an employee’s pay rate history and includes a “RateChangeDate” field. Although our use of a subquery makes sense, over time it will become tedious to type the same code every time we want to show an employee’s current pay rate. In addition, the use of the subquery adds risk to the quality of our application. The business logic represented by the subquery will need to be tested every place the subquery appears, increasing the odds for an error down the road.

Since we know the subquery is a handy block of code, we will take advantage of a common database feature called a “view”. In a nutshell, a view is a persistent select query that is given a name and can be referenced just like a table. With a view, business logic can be defined once and shared between future queries and even between programmers.

Taking our last example, lets turn the subquery for the latest pay rate and turn it into a view. Note how we use the “create” statement just like when making a table.
create view HumanResources.CurrentEmployeePayrateView
as
select    e.EmployeeId
          ,eph.Rate as Payrate
from      (
               select   EmployeeId
                        ,max(RateChangeDate) as RateChangeDate
               from     HumanResources.EmployeePayHistory
               group by EmployeeId
           ) e
inner join HumanResources.EmployeePayHistory eph on e.EmployeeId = eph.EmployeeId
                                    and e.RateChangeDate = eph.RateChangeDate

Click to continue »

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
Click to continue »