SQL between dates, but interrupted at a specific time and continues at a specific time

Question

I have an MSSQL query that runs order performance over a period of time. It basically calculates how much time it takes from the start date to the end date in minutes.

What I am doing is getting the date and time when the order is created (a), and getting the date and time when the order is completed (b), and subtracting them, and then multiplying them by 1440 to get the minutes between 2 dates.

,FLOOR(((CAST(b.[AuditInsertTimestamp2] AS FLOAT)) - (CAST(a.[AuditInsertTimestamp2] AS FLOAT))) * 1440) AS [Minutes] 

This works great, but it also means the closing time of our business. What I want to do is simply return the time between these dates while our business is open (from 6:30 to 17:00). Dates can span several days or just one day. Any ideas?!?!?!

Thanks!!!!

Answer 1

Here are the final questions if anyone wants to know ....

 /****** Object: UserDefinedFunction [dbo].[udf_TEST] Script Date: 08/11/2010 16:49:06 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[udf_TEST] ( @StartDate DATETIME, @EndDate DATETIME ) RETURNS VARCHAR(MAX) --DECLARE @StartDate DATETIME --DECLARE @EndDate DATETIME --SET @StartDate = '2010-07-06 14:46:37.577' --SET @EndDate = '2010-07-09 09:04:31.290' BEGIN DECLARE @FinalMinutes AS DECIMAL IF (CONVERT(VARCHAR(13), @StartDate, 114) < CONVERT(VARCHAR(13), @EndDate, 114)) BEGIN DECLARE @NonWorkTime1 INT SET @NonWorkTime1 = 780 --How many minutes are between order start and end time including non working time DECLARE @AllMins1 INT --Declares how many minutes are in a day and makes it float to get remainder minutes when divided DECLARE @MinsInDay1 DECIMAL SET @MinsInDay1 = 1440.0 --Finds how many minutes are between start and end time excluding weekends and assignes to variable SET @AllMins1 = ((DATEDIFF(mi, @StartDate, @EndDate)) -(((DATEDIFF(wk, @StartDate, @EndDate) * 2) * 24) * 60) -(((CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) * 24) * 60) -(((CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END) * 24) * 60)) --Calculates how many days have elapsed in the minutes that the order has taken DECLARE @MinDays1 INT SET @MinDays1 = (@AllMins1/@MinsInDay1) --Subtracts complete day non worked minutes from final minutes between orders SET @FinalMinutes = (@AllMins1 - (@MinDays1 * @NonWorkTime1) + 360 + 420) END ELSE BEGIN --How many minutes a day are not worked for trips DECLARE @NonWorkTime INT SET @NonWorkTime = 780 --How many minutes are between order start and end time including non working time DECLARE @AllMins INT --Declares how many minutes are in a day and makes it float to get remainder minutes when divided DECLARE @MinsInDay DECIMAL SET @MinsInDay = 1440.0 --Finds how many minutes are between start and end time excluding weekends and assignes to variable SET @AllMins = ((DATEDIFF(mi, @StartDate, @EndDate)) -(((DATEDIFF(wk, @StartDate, @EndDate) * 2) * 24) * 60) -(((CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) * 24) * 60) -(((CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END) * 24) * 60)) --Calculates how many days have elapsed in the minutes that the order has taken DECLARE @MinDays INT SET @MinDays = (@AllMins/@MinsInDay) --Subtracts complete day non worked minutes from final minutes between orders SET @FinalMinutes = (@AllMins - (@MinDays * @NonWorkTime)) END RETURN -(@FinalMinutes) END 

In my main query, I have a case argument which, if it is less than one day between @StartDate and @Enddate, it simply subtracts the time otherwise if it is more than a day or if it is less than a day but less than 24 hours he sends it to this function. Everything seems to work Perfect !!!

Answer 2

I would use your code to calculate all the minutes between these two dates, but then I would subtract the number of minutes during which your business is closed.

To do this, use this function to calculate the number of working days (therefore, delete the weekend) and multiply the output by 630 (10 hours and half * 60 = the number of minutes per day when your company is open).

Having added everything, it will be something like this:

 (your result) - (number of work days between the beginning and the end of the request) * 630 

It does not take into account the holidays. Hurrah!

EDIT : since this link requires registration, here is a short version of the code itself; check the source source for details, as I am not the author of it:

 SELECT (DATEDIFF(dd, @StartDate, @EndDate) + 1) -(DATEDIFF(wk, @StartDate, @EndDate) * 2) -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END) 

Answer 3

In the simplest, just count the number of days and multiply them by 630 minutes (10.5 hours your business is open every day).

If your decision should be more complex (excluding weekends, holidays, etc.), I would say that the logic is better removed from the database and at the business level.