I need to calculate the difference between two dates and display the result as the number of weeks, days, hours and minutes. This in itself is not a problem, but I want to use only office hours in the query, i.e., ignore weekends and do not calculate hours after 18:30 and before 08:00, public holidays excluded etc.
The best way to approach this problem is with the use of a Calendar table. Office hours are from 08:00 to 18:30? Okay, that's 10.5 hours.
Caldate DayOfWeek OfficeHours Holiday 2001-05-24 Thu 10.5 2001-05-25 Fri 10.5 2001-05-26 Sat 0 2001-05-27 Sun 0 2001-05-28 Mon 0 Memorial Day 2001-05-29 Tue 10.5
To find the office hours between two dates, you use a query like this --
select sum(OfficeHours) from Calendar where Caldate >= #startdate# and Caldate < #enddate#
where #startdate# and #enddate# are your variables.
The main task then will be updating the Calendar table annually -- probably by hand, i.e. by preparing the data in, say, Excel, and then loading it into the table with a database utility.
For More Information
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our SQL guru is waiting to answer your technical questions.