I am by no stretch a SQL expert but writing queries to retrieve and manipulate data is one of the more enjoyable tasks I get to work on. And one of my favorite aspects about writing queries is that there are a number of ways to accomplish the same task. With some being more effecient than others depending upon the situation

Bit of Background

I was working for a city doing work for their fire department. They asked for a report which would show the number of incidents broken out by station and shift. This seemed pretty straight forward at first glance, but then as with many things, the more you dig into the problem, the more work it becomes. The way the data was structured made the report much more work than it needed to be. Most of the data was easy enough to find with the exception of the shift.

For this particular department they had 3 main shifts labeled; A, B and C. Shift were 24 hours on, 48 hours off with the shift starting and ending at 7am. The trouble was that the database had a timestamp as to when the incident occurred, not the shift assigned. So, the problem became how to determine what shift a particular time was associated with.

To be honest one of the first ideas I had was to build a table with columns for the start date/time, end date/time and shift. This would of been straight forward and is easy to use. However, to me this did not seem the best way, and it was a table I would need to manage in the future.

The End Result

The concept I decided on was a function where I could send the date/time and it would return the shift. I wanted it to be easy to understand as well as I wanted it to work with any date, future or past. Before creating the function I wanted to write a query to prove (or disprove) the concept and here is what I came up with.

DECLARE @ShiftDate DATETIME = '12/31/2005 06:00:00';
DECLARE @baseDate DATETIME = '1/1/2006 07:00:00';
DECLARE @compareDate DATETIME;
DECLARE @out CHAR(1);
SET @compareDate = CASE
                       WHEN DATEPART(HH, @ShiftDate) < 7 THEN
                           DATEADD(DD, -1, @ShiftDate)
                       ELSE
                           @ShiftDate
                   END;
SELECT CASE CASE
                WHEN @baseDate > @compareDate THEN
                    DATEDIFF(DD, @compareDate, @baseDate) % 3
                ELSE
                    DATEDIFF(DD, @baseDate, @compareDate) % 3
            END
           WHEN 0 THEN
               'A'
           WHEN 1 THEN
               'B'
           WHEN 2 THEN
               'C'
       END;

Amazingly enough it works. (Or at least I think it does). I wish I could say that this was what I first started with, but it wasn't. It took some tweaking to get it to work as I wanted, however, it does work.

Explanation

For those who care or would like to know I am going to explain the above code line-by-line. Sort of anyway. The first four lines are of course parameters:

• @ShiftDate - the parameter that will be passed to the function.
• @baseDate - the static point in time that is equal to the A shift.
• @compareDate - the date used to compare to the static point in time. This was not needed but made reading the query a little easier.
• @out - the output which should be A, B or C

This bit preps the @compareDate based on the hour because the shift runs for 24 hours starting at 7am.

SET @compareDate = CASE
                       WHEN DATEPART(HH, @ShiftDate) < 7 THEN
                           DATEADD(DD, -1, @ShiftDate)
                       ELSE
                           @ShiftDate
                   END;

This bit is really what does all the work. Using the mod function against the difference of days between the dates will return either 0, 1 or 2 which is then converted to A, B or C respectively.

                WHEN @baseDate > @compareDate THEN
                    DATEDIFF(DD, @compareDate, @baseDate) % 3
                ELSE
                    DATEDIFF(DD, @baseDate, @compareDate) % 3

Conclusion

I would like to think this is a pretty good solution and is pretty easy to follow. However, as I stated in the beginning, one of my favorite aspects of SQL is that there are usually multiple ways to accomplish the same task. What I would really like is to get some feedback on the concept and query to see what other ways there are to solve the problem.