计算SQL Server中两个日期之间的工作时间(不包括周末) [英] Calculate the working hours between two dates in sql server excluding weekend
问题描述
我正在研究一个出租汽车的出租系统数据库.我需要在给定的日期时间范围内仅工作时间(不包括周末)计算汽车的总借贷时间(时差之和).
I am working on a rent system database where a car is given on rent. I need to calculate the (sum of time difference) total time of loan for a car in the given date time range for only working hours and excluding weekend.
用于阐明场景的示例
我们给定的日期范围，其中子句为1/02/17和5/2/17
our given date range in where clause is 1/02/17 and 5/2/17
乔在2017年2月1日上午9点预订了一辆汽车，并在2017年2月1日下午5点归还了汽车……包括8小时的预订.
Joe books a car on 01/02/2017 at 9am and returns it at 5pm on 01/02/2017 … 8 hour booking included.
我们给定的日期范围，其中where子句是19/02/17和20/2/17
our given date range in where clause is 19/02/17 and 20/2/17
在2017年2月19日上午9点取消预订汽车，并在2017年2月21日下午5点归还汽车.由于第3天超出了我们的日期范围范围，因此仅包括16小时预订(即19日和20日)点为2017年2月20日.
Trish books car on 19/02/2017 at 9am and returns it at 5pm on 21/02/2017 … 16 hour booking included only (ie. 19th & 20th), as the 3rd day falls outside our Date Range end point of 20/02/2017.
我们给定的日期范围，其中子句为1/02/17和5/2/17
our given date range in where clause is 1/02/17 and 5/2/17
汤姆于2017年1月31日上午9点预订汽车，并于2017年2月1日下午12点归还汽车…仅包括3小时预订(即2月1日的½天)作为第一天(即31日) 1月)超出了我们的日期范围起始点2017年1月2日
Tom books car on 31/01/2017 at 9am and returns it at 12pm on 01/02/2017 … 3 hour booking included only (ie. ½ day on 1st Feb), as the the 1st day (ie. 31st Jan) falls outside our Date Range starting point of 01/02/2017
如果任何列值不同(例如，公司名称，贷款车辆注册，服务顾问或驾驶员号码牌)，则该行将为唯一行.但是，如果这些列都是相同的，并且唯一的区别是预订日期(即，如果Joe在日期范围"期内使用相同的公司，服务顾问和驾驶员号码牌两次预订同一辆车)，则应为分组/计算为一条记录.
If any of the column values differ (ie. Company Name, Loan Vehicle Registration, Service Advisor, or Drivers Number Plate)then, that will be a unique row. But if those column are all the same and the only difference is the booking date (ie. if Joe books same car out twice within the Date Range period, using the same Company, Service Advisor, and Drivers Number Plate), then that should be grouped/calculated as one record line.
我当前的存储过程是
ALTER PROCEDURE [dbo].[bookingAnalysis]
@from_date nvarchar(50)=NULL,
@to_date nvarchar(50)=NULL
AS
BEGIN
SET NOCOUNT ON;
Select cmp.CompanyName,v.NumberPlate as Loan_Vehicle_Registration,m.ModelDescription as Vehicle_Description,
cnt.FirstName,cnt.LastName,DATEDIFF(HOUR,b.DueOutDate,b.ActualReturnedDate)as
Duration_of_Loan,b.bookingID,b.DriversNumberPlate from Bookings as b
inner join Companies as cmp on b.AssignedCompanyID=cmp.CompanyID
inner join Vehicles as v on v.VehicleID=b.VehicleID
inner join Models as m on m.ModelID=v.ModelID inner join Contacts
cnt on cnt.ContactID=b.ContactID where b.DueOutDate>= @from_date and b.ActualReturnedDate<= @to_date
END
目前，我在b.DueoutDate和b.ActualReturnDate之间获得datediff
Currently I am getting datediff between b.DueoutDate and b.ActualReturnDate
推荐答案
如果您首先创建一个日历表(每个日期一行)，则要做起来会容易得多.然后，您可以使用该服务过滤掉周末，然后在预订有效的每一天都会得到一行结果.
It's going to be a lot simpler to do this if you first create a calendar table, with one row per date. You can use that then to filter out weekends, and you will get one row as a result for each of the days when the booking is valid.
之后，您只需要检查:
- 如果开始日期与日历日期相同->检查可能要从开始日期中扣除的小时数
- 如果结束日期与日历日期相同->检查可能要从结束日期中扣除的小时数
然后将所有其他天加起来8个小时(如果那是您的工作日).
Then just sum all other days with 8 hours, if that was your working day.
这篇关于计算SQL Server中两个日期之间的工作时间(不包括周末)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
