One of the projects my company is currently working on has a requirement to be able to store timesheet data in a more flexible manner.  The existing Access forms application is being converted to Silverlight 3 and has a fairly rigid (albeit standard) database structure for timesheets.  Hours and quantities are associated directly with days using columns such as MonHours, MonQuant, etc.  (see the table image to the right).  The catch is that the company needs to be able to store several other pieces of data in the future.  Of course, if they need a new column that tracks overtime, they’d have to modify the database table and add in 7 new columns if the existing table structure is used which definitely isn’t optimal.

After thinking through a few options we decided to go with a more complex yet flexible solution.  It’s only useful (in my opinion anyway) when the data that will be collected can change in the future.  I’m not a big fan of architecting for “what if” type scenarios since in my experience most of those scenarios are never realized, but in this situation it was a requirement to be able to handle different types of timesheet data without having to re-work the table structure and add new columns.  We ended up going with the following table structure to store timesheet values:

Here’s a quick overview of what the different tables store:

• TimeSheet – Associates a job, employee and week ending date together
• TimeSheet_WorkCode – Associates work codes with a timesheet
• TimeSheetDayValue – This is where the values are actually stored.  It can store hours, overtime, quantity or anything else really.  The DayValue column stores the actual value and the type of value being stored (hours, quantity, etc.)  is tracked by the TimeSheetDayValueTypeID which links to the TimeSheetDayValueType table.
• TimeSheetDayValueType – This table tracks the type of data being stored in TimeSheetDayValue (currently just hours and quantity of materials installed).  The type of data is tracked in the ValueType column which is a simple varchar data type.
• Day – This is a simple lookup table that contains all of the day names (Monday, Tuesday, Wednesday, etc.)

While the table structure provides a flexible way to store different types of timesheet data, it doesn’t store the data in a format that’s compatible with timesheets.  Normally, an employee will select a work code and then enter data for the hours worked on each day.  With the initial table structure that we inherited from the original database (image to the right) that was easy since the existing columns match up perfectly with grid header columns.  With the new table structure we gain flexibility but add complexity since we have to reshape the data to fit into a timesheet grid.

Fortunately, SQL Server 2005 or higher supports pivots which allow data to be “pivoted”.  In other words, row data can be turned into columns.  If I run the following query against the tables shown earlier I’ll get back rows but they won’t match up with Monday, Tuesday, Wednesday timesheet header columns as you can see next:

SELECT TS.TimeSheetID, TS.WeekEnding, TS.EmployeeID, TS.JobID, TSWC.WorkCodeID,
TSDV.DayValue, D.Day + TSDVT.ValueType as RotateColumn
FROM TimeSheet TS
INNER JOIN Timesheet_WorkCode TSWC on TS.TimeSheetID = TSWC.TimeSheetID
INNER JOIN TimeSheetDayValue TSDV on TSWC.TimeSheetWorkCodeID = TSDV.TimeSheetWorkCodeID
INNER JOIN TimeSheetDayValueType TSDVT on TSDV.TimeSheetDayValueTypeID = TSDVT.TimeSheetDayValueTypeID
INNER JOIN Day D on TSDV.DayID = D.DayID
WHERE TS.JobID = 1 AND EmployeeID = 1 AND WeekEnding = '5/10/2009'

However, by using a pivot I can make it so that the values shown above for RotateColumn are “pivoted” to be columns in the resultset so that everything fits into a single row on a timesheet grid.  There are a few tricks that have to be leveraged to make it all work such as leveraging some of the SQL Server 2005+ XML functionality but we felt it was a good tradeoff to get the data storage flexibility required by the client.  Here’s the pivot query we’re currently using and the resultset that it returns.  We’ll likely change things as the application is optimized more, but you’ll get the idea.  The query is in a stored procedure which accepts jobID, employeeID and week ending date and returns timesheet data for different work codes.

DECLARE @Cols varchar(500)
SELECT @Cols = STUFF((SELECT ',' + QUOTENAME(D.Day + TSDVT.ValueType) AS [text()]
FROM Day D
CROSS JOIN TimeSheetDayValueType TSDVT
FOR XML PATH('')), 1, 1, '')

DECLARE @Query nvarchar(MAX)
SELECT @Query = '
SELECT * FROM
(
    SELECT TS.TimeSheetID, TS.WeekEnding, TS.EmployeeID, TS.JobID, TSWC.WorkCodeID,
    TSDV.DayValue, D.Day + TSDVT.ValueType as RotateColumn
    FROM TimeSheet TS
    INNER JOIN Timesheet_WorkCode TSWC on TS.TimeSheetID = TSWC.TimeSheetID
    INNER JOIN TimeSheetDayValue TSDV on TSWC.TimeSheetWorkCodeID = TSDV.TimeSheetWorkCodeID
    INNER JOIN TimeSheetDayValueType TSDVT on TSDV.TimeSheetDayValueTypeID = TSDVT.TimeSheetDayValueTypeID
    INNER JOIN Day D on TSDV.DayID = D.DayID
    WHERE TS.JobID = ' + CONVERT(NVARCHAR,@JobID) + 
    ' AND EmployeeID = ' + CONVERT(NVARCHAR,@EmployeeID) + ' AND WeekEnding = ''' + @WeekEnding + '''
) AS SourceTable
PIVOT (SUM(DayValue) FOR RotateColumn IN (' + @Cols + ')) AS TimeSheetRow'
-- FOR XML AUTO, ELEMENTS XSINIL
EXEC sp_executesql @Query

You can see that the RotateColumn values shown earlier have been changed to be columns using SQL Server’s pivot functionality which is exactly what we needed for the Silverlight 3 application that displays the data.  We had to resort to some dynamic SQL to make it work, but the end result is that we’re able to shape the data as needed yet still retain the flexibility provided by the table structure.  As new types of timesheet values are required the table structure and query can be left intact.  Pivots to the rescue…pretty nice when you need them.