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


Now we can replace the subquery in our query from the last post with a reference to our view like this:
select       e.EmployeeId
             ,c.LastName + ', ' + c.FirstName as EmployeeName
             ,e.VacationHours + e.SickLeaveHours - 120 as AccruedPtoHoursInExcess
             ,cast((e.VacationHours + e.SickLeaveHours - 120) * f.Rate as decimal(6,2)) as Liability
from         HumanResources.Employee e
inner join   Person.Contact c on e.ContactId = c.ContactId
inner join   HumanResources.CurrentEmployeePayRateView f on e.EmployeeId = f.EmployeeId                            
where        VacationHours + SickLeaveHours > 120;

A couple of notes: The view acts like a table in more ways than one. You can set permissions on it for specific user by using the “grant select” statement just like on a table. It is also possible to put an index on a view, but I do not recommend this for beginners as an index on a view can become complicated to manage.