CODEWITHSUNDEEP
sql-serverdatetime
user2439970
user2439970

Reputation: 129

SQL Return Hours betwewn 2 dates, excluding weekends AND non-shift hours

I want to calculate how many hours an item has been used, using the start and end dates. Start & end dates will always be a weekday but of course can span multiple weeks. I've got it working to exclude weekends but not sure how to exclude the non-working hours.

Example:

declare @d1 datetime, @d2 datetime
select @d1 = '04/25/2014 02:42',  @d2 = '04/28/2014 17:14'

select (datediff(mi, @d1, @d2) / 60) - (datediff(wk, @d1, @d2) * 48)

Working hours are Monday 06:00 - Friday 21:00.

So in the example stated, it returns 38 but it should be 29, I believe.

Any help will be greatly appreciated! Thanks.

[Edit]

@Fnightangel

You my friend, are a genius! Thank you very much. This is a great solution - clear & concise and works a treat :).

I improved your code (as you mentioned yourself, it wasn't quite 100% right), by adding this IF statement to the beginning of the logic:

IF (DATEDIFF(DD, @D1, @D2) <= 0) OR (DATEDIFF(WK, @D1, @D2) * 2 <= 0)
    RETURN
      (DATEDIFF(MI, @D1, @D2) / 60)
ELSE
    ....

Upvotes: 3

Views: 1178

Answers (1)

fnightangel
fnightangel

Reputation: 426

I really don't know if this is the best solution, but was the only one i can think of.

See if this helps:

Define your variables

DECLARE @D1 DATETIME, @D2 DATETIME, @CURRENTDATE DATETIME
SELECT @D1 = '04/25/2014 02:42',  @D2 = '04/28/2014 17:14'

Then, I created 2 tables storing all mondays and all fridays to later count how many hours we have to sub

CREATE TABLE #MONDAYS(MONDAYS VARCHAR(8) NULL)
CREATE TABLE #FRIDAYS(FRIDAYS VARCHAR(8) NULL)

SET @CURRENTDATE = CONVERT(VARCHAR(8), @D1, 112)
WHILE @CURRENTDATE <= CONVERT(VARCHAR(8), @D2, 112)
BEGIN
    IF DATEPART(DW, @CURRENTDATE) = 2 --MONDAY
        INSERT INTO #MONDAYS(MONDAYS) VALUES (CONVERT(VARCHAR(8), @CURRENTDATE, 112))
    ELSE
        IF DATEPART(DW, @CURRENTDATE) = 6 --FRIDAY
            INSERT INTO #FRIDAYS(FRIDAYS) VALUES (CONVERT(VARCHAR(8), @CURRENTDATE, 112))

    SET @CURRENTDATE = DATEADD(DAY, 1, CONVERT(VARCHAR(8), @CURRENTDATE, 112))
END

Calculate how many non-working hours based on mondays and fridays

DECLARE @NONWORKING AS INT

SET @NONWORKING = ((SELECT COUNT(*) FROM #FRIDAYS) * 3) -- 3 NON WORKING HOURS ON FRIDAYS
                + ((SELECT COUNT(*) FROM #MONDAYS) * 6) -- 6 NON WORKING HOURS ON MONDAYS

And finally, calculate

SELECT
(DATEDIFF(MI, @D1, @D2) / 60) 
- (DATEDIFF(WK, @D1, @D2) * 48)
- @NONWORKING

Hope this helps a bit. Fell free to improve my ideia.

Upvotes: 1

Related Questions