Not another SQL time between dates minus weekends question

Question

I have two dates: CREATION_DATE and START_DATE. START_DATE will always be later than CREATION_DATE. I need to calculate the number of minutes between them, except for minutes which happen on a weekend.

Every solution I can find assumes one of those dates occurs on a weekend, but alas, if CREATION_DATE is on a Friday, and START_DATE is a Monday, all of Saturday and Sunday is counted.

I've even tried calculating minutes from CREATION_DATE to the next 12am occurs plus minutes from first 12am Monday to START_DATE, but that doesn't work either.

I have found a solution if I only wanted to count days. I need to know down to minutes.

Our DB is hosted an I am not able to create VB functions so my solution must be all SQL.

Answer 1

You can take a look at this solution, and see if meets your needs. Basically, I did the following:

1. Take the number of whole days betwen StartDate and EndDate that aren't weekend days, and multiply by 2:

   SELECT COUNT(*) * 24 * 60 FROM WholeDaysBetween WHERE wkday <= 5

2. Take the minutes from StartDate (hours*60 + minutes)

   (24 * 60) - (DATEPART(HOUR, @StartDate) * 60) - (DATEPART(MINUTE, @StartDate))

3. Take the minutes from EndDate (hours*60 + minutes)

   (DATEPART(HOUR, @EndDate) * 60) + (DATEPART(MINUTE, @EndDate))

To get the number of whole days between, I used a recursive CTE:

WITH
    WholeDaysBetween(dt, wkday) AS
    (
        SELECT DATEADD(DAY, 1, @StartDate), DATEPART(WEEKDAY, DATEADD(DAY, 1, @StartDate))
        UNION ALL
        SELECT DATEADD(DAY, 1, dt), DATEPART(WEEKDAY, DATEADD(DAY, 1, dt))
        FROM WholeDaysBetween
        WHERE dt < DATEADD(DAY, -1, @EndDate)
    )

Of course, for this to work, you have to adjust your datefirst settings.

The final query is as follows (I used the same sample data as in your comment):

set datefirst 1; -- day starts on Monday

declare @StartDate datetime = '2018-09-21 23:59:00';
declare @EndDate datetime = '2018-09-24 00:01:00';

WITH
    WholeDaysBetween(dt, wkday) AS
    (
        SELECT DATEADD(DAY, 1, @StartDate), DATEPART(WEEKDAY, DATEADD(DAY, 1, @StartDate))
        UNION ALL
        SELECT DATEADD(DAY, 1, dt), DATEPART(WEEKDAY, DATEADD(DAY, 1, dt))
        FROM WholeDaysBetween
        WHERE dt < DATEADD(DAY, -1, @EndDate)
    )

SELECT
    -- whole weekdays between @StartDate and @EndDate, 
    -- multiplied by minutes per day
    (
        SELECT COUNT(*) * 24 * 60
        FROM WholeDaysBetween
        WHERE wkday <= 5
    ) 
    +
    -- minutes from @StartDate date to end of @StartDate
    -- as long as @StartDate isn't on weekend
    (
        SELECT 
            CASE 
                WHEN DATEPART(WEEKDAY, @StartDate) <= 5
                THEN
                (24 * 60) - 
                (DATEPART(HOUR, @StartDate) * 60) - 
                (DATEPART(MINUTE, @StartDate))
                ELSE 0
            END
    )
    +
    -- minutes from start of @EndDate's date to @EndDate
    -- as long as @EndDate isn't on weekend
    (
        SELECT 
            CASE
            WHEN DATEPART(WEEKDAY, @EndDate) <= 5
            THEN
                (DATEPART(HOUR, @EndDate) * 60) +
                (DATEPART(MINUTE, @EndDate))
            ELSE 0
            END
    )

Answer 2

If you aren't worried about accounting for holidays, you can do this as a simple math problem without having to monkey around with a tally table or doing any counting. The following works by dropping the time portion off the begin and end date parameters and calculates the number of working days from that and multiples that figure by 3660. From there, if the begin date is a week day the begin date mins are subtracted... if end date is a weekday, those mins are added.

DECLARE
    @BegDate DATETIME = '2018-09-13 03:30:30',
    @EndDate DATETIME = '2018-09-18 03:35:27';

SELECT 
    working_mins = bm.base_mins
                    - ((1 - (x.is_beg_sat + x.is_beg_sun)) * x.beg_mins)    -- if the begin date is a week day, subtract the mins from midnight.
                    + ((1 - (x.is_end_sat + x.is_end_sun)) * x.end_mins)    -- if the end date is a week day add the mins from midnight.
    --,*
FROM
    ( VALUES (
            DATEADD(DAY, DATEDIFF(DAY, 0, @BegDate), 0), 
            DATEADD(DAY, DATEDIFF(DAY, 0, @EndDate), 0)
            ) ) d (beg_date, end_date)
    CROSS APPLY ( VALUES (
            DATEDIFF(MINUTE, d.beg_date, @BegDate),
            DATEDIFF(MINUTE, d.end_date, @EndDate),
            DATEDIFF(DAY, d.beg_date, d.end_date),
            DATEDIFF(WEEK, d.beg_date, d.end_date) * 2,
            1 - SIGN(DATEPART(WEEKDAY, d.beg_date) % 7),
            1 - SIGN(DATEPART(WEEKDAY, d.end_date) % 7),
            1 - SIGN((DATEPART(WEEKDAY, d.beg_date) + 7) % 8),
            1 - SIGN((DATEPART(WEEKDAY, d.end_date) + 7) % 8)
            ) ) x (beg_mins, end_mins, total_days, weekend_days, is_beg_sat, is_end_sat, is_beg_sun, is_end_sun)
    CROSS APPLY ( VALUES (1440 * (x.total_days - x.weekend_days + x.is_beg_sat - x.is_end_sat)) ) bm (base_mins);

Answer 3

The basic idea is to generate a record for all minutes between the start and finish, including those on weekends. Then use the WHERE clause to filter out those you don't want. In many cases, this is done by joining to a Calendar table, so you can also look at holidays or other special events, but for this purpose we can just use the DATEPART() function.

One this is done, we use a GROUP BY to roll things back up to the original date values and the COUNT() function to know how much work we did.

This basic concept works whether you're counting days, minutes, months, whatever.

It's not clear in the question, but I'm gonna assume your start and end values are columns in a table, rather than variable names (no @).

WITH Numbers(Number) AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY s1.[object_id]) - 1
    FROM sys.all_columns AS s1
    CROSS JOIN sys.all_columns AS s2
)
SELECT t.CREATION_DATE, t.START_DATE, COUNT(*) AS Num_Minutes
FROM [MyTable] t
INNER JOIN Numbers n on n.Number <= DATEDIFF(minute, t.CREATION_DATE, t.START_DATE)
WHERE DATEPART(dw, DATEADD(minute, n.Number, t.CREATION_DATE)) NOT IN (7,1)
GROUP BY t.CREATION_DATE, t.START_DATE

But this has the potential to be very slow, depending on how far apart the dates are. You can improve this by using various other ways to generate the Numbers table to get a starting point that better approximates the needs of your actual data.