Database Programming: The Time Zone Conversion Beast, Once Comatose, Returns to Taunt Us a Second Time (or, An Answer for Iain)
- John Cleese as a French knight (picture at left); Monty Python and the Holy Grail
One of the reasons I started blogging over four years ago was my conviction that the coding challenges with which my employer was presenting me, while stimulating and challenging, were far from unique. I hate to say “I told you so,” but then I didn’t tell you, I told me, so it would be both pointless and wrong to say “I told you so.” But, “I told me so” doesn’t sound right either.
So it’s probably just best to move on.
The point of this little ramble is that I get an extra little jolt of satisfaction from this exercise when I get questions from readers who are trying to adapt to suit their needs the code presented in an old post. It’s even more fun when a question prompts me to expand upon a design to fulfill an unanticipated need.
So it is with Iain Johnson’s comments on a post that’s over three years old: Database Programming: Slaying The Time Zone Conversion Beast (or at least rendering it comatose) . Maybe he found it because my buddy Jimmy May gave it some link love lately. Perhaps he’s one of dozens(!) of folks who find their way here every day via one of the search engines.
Wherever Iain came from, he’s got a very interesting requirement:
… for a global helpdesk with regional centres… I need to calculate two SLA times for a call - the time by when the caller should have been contacted about their issue (typically within an hour) and the time by which the problem should be solved for them (typically within 2 working days).
The calculation needs to take account of working hours (so a call logged 30 minutes before the helpdesk closes for the day, should be 'contacted' within 30 minutes of the helpdesk opening on it's next working day. I also need to take account of public holidays, i.e. non-working days, other than weekends.
Well, I love stuff like this.. and what ends up being the coolest thing of all is that, once the proper data are in place, we can write this query in one glorified T-SQL statement.
I’ve attached a little proof-of-concept to this post. You’ll find a read-me document as well as two SQL scripts. In order to keep this post from getting unwieldy, I’ll put my commentary in the read-me. Purists should note that this new solution makes use of a recursive common table expression, so while our previous conversations were applicable to SQL Server 2000, this solution will only work on SQL Server 2005 and above. I’ve tested it on SQL Server 2005 SP3 and SQL Server 2008 SP1.
The code isn’t perfect, but it’s pretty interesting. Thanks to Iain for his thought-provoking question, and his otherworldly patience in awaiting my answer.
-wp
this copyrighted material was originally posted at https://blogs.technet.com/wardpond.
the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.
the author welcomes and appreciates links to and citations of his work. however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites.