1

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.

Examples to clarify the scenarios

our given date range in where clause is 1/02/17 and 5/2/17

Joe books a car on 01/02/2017 at 9am and returns it at 5pm on 01/02/2017 … 8 hour booking included.

our given date range in where clause is 19/02/17 and 20/2/17

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.

our given date range in where clause is 1/02/17 and 5/2/17

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

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.

My current stored procedure is 

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

Currently I am getting datediff between b.DueoutDate and b.ActualReturnDate

James Z
  • 12,209
  • 10
  • 24
  • 44
dev
  • 555
  • 1
  • 13
  • 31
  • Possible duplicate of [Count work days between two dates](http://stackoverflow.com/questions/252519/count-work-days-between-two-dates) – tavnab Feb 24 '17 at 22:34
  • @tavnab please study the whole question . – dev Feb 24 '17 at 22:36
  • Take a look at http://stackoverflow.com/questions/252519 as your starting point for working out the number of weekdays between two dates, and then add on the hours for your edge-cases (i.e. round your starting date up, round your ending date down, use those to get the number of weekdays, and if your start/end dates are on weekdays, then add the number of weekday hours you lost at either end to that to get your total hours) – tavnab Feb 24 '17 at 22:38
  • I dont want to get the just working hours of a week please study the whole question @tavnab – dev Feb 24 '17 at 22:42
  • welcome to SO. I suggest focusing on 1 problem at a time per question (in this case, given two date-times, how to calculate the total number of weekday hours they cover). The fact that you have another range and want to use the overlapping range as the range to check is another question (an easy one, as you can just get the MAX of the two lower-ends and the MIN of the two upper-ends as your "true" range), as is the information on how the data is stored. – tavnab Feb 24 '17 at 22:46
  • @tavnab thanks alot dear, but i am still confuse , would you please elaborate with some example? – dev Feb 25 '17 at 12:51
  • 1
    You should really make an example (with data, preferably insert clauses) with a single search criteria and expected results from that for all the cases. You're just making this too complex by changing also the search criteria for every step – James Z Feb 26 '17 at 06:19

1 Answers1

0

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.

After that you just have to check:

  • if the start date is the same as calendar date -> check possible hours to be deducted from the start
  • if the end date is the same as calendar date -> check possible hours to be deducted from the end

Then just sum all other days with 8 hours, if that was your working day.

James Z
  • 12,209
  • 10
  • 24
  • 44