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 »