A friend asked me if it was possible to sort on just a portion of a text field, but not necessarily the first word. For example, lets say you have a full name field that is formatted  as ‘[FirstName] [LastName]‘. There is a simple, if not flawless, way to do this with T-SQL string functions. First, you need to generate a small list of names to work with. I will use a table variable to do this. It is easy to spin up and is disposed of as soon as the query is done running.

declare @MyNames table (FullName nvarchar(20));
insert @MyNames
values ('Betty Ferguson'),('Sam L Jackson'),('Horatio Alger');

Note that I used the much simpler SQL Server 2008 syntax to add the names. If you are using SQL Server 2005, you will need to add the names like this:

insert @MyNames
values ('Betty Ferguson');
insert @MyNames
values ('Sam L Jackson');
insert @MyNames
values ('Horatio Alger');

Since you can’t control people adding a middle initial, I will find the last name by counting the number of characters starting from the right before I find a space character. The best function to use for this is charindex.

select charindex(' ', FullName) as 'LastNameLetterCount' from @MyNames;

The problem is that the charindex function counts from the left. In order to count from the right, I need to flop the text over by using the reverse function. This function will take a string of characters such as ‘My dog spot’ and return it as ‘tops god yM’. I also subtract 1 so that I only return the length of the last name, not the last name and the space.
Click to continue »

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 »