SQL between dates but cut off at certain time and continue and certain time.

Question

I have an MSSQL query that runs order productivity for a time period. Basically it calculates how long an order takes from start date to completion date in minutes.

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

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

This works perfectly fine but this also counts the time that our business is closed. What I want to do is just return how much time between these dates is taken while our business is open (6:30AM - 5:00PM). The dates can span multiple days or just a single day. Any ideas?!?!?!

Thanks!!!!

Answer 1

I'd use your code to calculate all the minutes between those two dates, but afterwards i'd subtract the number of minutes during which your business is closed.

To do so, use this function to calculate the number of working days (hence removing weekends) and multiply the output by 630 (10 hours and a half * 60 = the number of minutes per day that your business is open).

Adding it all up, it would be something like this:

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

It won't account for holidays though. Cheers!

EDIT: since that link requires registration, here's the short version of the code itself; do check the original source for the 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 2

At the very simplest, just count the number of days and multiply by 630 minutes (the 10.5 hours your business is open each day).

If your solution needs to be any more complex than that (excluding weekends, holidays, etc.), I'd say that logic is better off out of the database and in a business layer.

Answer 3

Here is the final queries if anyone cares 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 statment that if its less than a day between @StartDate and @Enddate it just subtracts the time otherwise if its more than a day or if its less than a day but its less than 24 hours it sends it to this funtction. Everything seems to work Perfect!!!