-1

I'm trying to calculate the difference between two dates excluding the weekends and only count the time from 8pm - 6am. I want to calculate that difference in Days, Hours and Minutes.

For that I have this:

DECLARE @Start_Date DATETIME
DECLARE @End_Date DATETIME

SET @Start_Date = '2017-06-23 10:43:41.000'
SET @End_Date = '2017-06-27 11:58:52.000'

SELECT (DATEDIFF(dd, @Start_Date, @End_Date) + 1)
                -(DATEDIFF(wk, @Start_Date, @End_Date) * 2)
                -(CASE WHEN DATENAME(dw, @Start_Date) = 'Sunday' THEN 1 ELSE 0 END)
                -(CASE WHEN DATENAME(dw, @End_Date) = 'Saturday' THEN 1 ELSE 0 END) AS [Time to First Atualization- Days],
            datediff(hour, @Start_Date, @End_Date) - (datediff(wk, @Start_Date, @End_Date) * 48) -
                case when datepart(dw, @Start_Date)  = 1 then 1 else 0 end +
                case when datepart(dw, @End_Date)  = 1 then 1 else 0 end AS [Time to First Atualization- Hours],
                datediff(minute, @Start_Date, @End_Date) - (datediff(wk, @Start_Date, @End_Date) * 2880) -
                case when datepart(dw, @Start_Date)  = 1 then 1 else 0 end +
                case when datepart(dw, @End_Date)  = 1 then 1 else 0 end AS [Time to First Atualization- Minutes]

The number of days the query return the correct value but to calculate the number of hours and minutes it's wrong...

How can I solve this?

Thanks!

Pedro Alves
  • 1,004
  • 1
  • 21
  • 47
  • Can you please explain what is your logic here in the query? – Noor A Shuvo Jul 13 '17 at 11:28
  • I want to know the number of work days, hours and minutes of an employee during his work hours. He only work at business days and have an schedule from 8pm and 6am. – Pedro Alves Jul 13 '17 at 11:30
  • @PedroAlves if i run your script I've got 49. what are you expecting? – MJK Jul 13 '17 at 11:48
  • I'm expecting 21hours. Why: 8h from the first day (10am-6pm) + 10h from second day - 26/07/2017 (8am - 6pm) + 3h from third day 27/07/2017 - 8am - 6pm (8am - 11pm) – Pedro Alves Jul 13 '17 at 11:53
  • You have written 8pm (Actually 8am) and 6am (actually 6pm) in your 2nd comment – Noor A Shuvo Jul 13 '17 at 12:00
  • Possible duplicate of [Calculate the working hours between two dates in sql server excluding weekend](https://stackoverflow.com/questions/42449416/calculate-the-working-hours-between-two-dates-in-sql-server-excluding-weekend) – Ben Jul 13 '17 at 12:09
  • @ Noor A Shuvo sorry, is an error in my second comment. Consider 8am-6pm – Pedro Alves Jul 13 '17 at 13:59

1 Answers1

1

I worked out something from scratch, and it seems to cover all your needs, though you can update us back if something's missing.

Considering it's a fresh start and coming from a different angle, you might discover certain techniques or ideas out of it. Also, it does seem simpler to me but maybe that's because I'm reviewing my own work...

One last note, I'll be relying on a trick I read before, that applies MIN and MAX in a row-wise fashion, abstract example:

SELECT MAX([value]) AS [MAX], MIN([value]) AS [MIN]
FROM (
VALUES (CURRENT_TIMESTAMP), (@Start_Date), (@End_Date), (NULL), (0)
) AS [data]([value])

First off, thought of figuring the amount of time outside start & end days:

SELECT MinutesExcludingStartAndEndDays = MAX([value])
FROM (VALUES (0), ((DATEDIFF(DAY, @Start_Date, @End_Date) - 1) * 840)) AS [data]([value])

Second, figuring the time during starting day, against 8pm (or end time if both days match):

SELECT MinutesOnStartDay = DATEDIFF(MINUTE, @Start_Date, MIN([value]))
FROM (VALUES (@End_Date), (DATETIMEFROMPARTS(YEAR(@Start_Date), MONTH(@Start_Date), DAY(@Start_Date), 20, 0, 0, 0))) AS [data]([value])

Third is very similar to second, however note that if start and end days were the same, we should not count both second and third. I decided to handle that with a CASE statement within third:

SELECT MinutesOnEndDayIfNotStartDay = CASE DATEDIFF(DAY, @Start_Date, @End_Date) WHEN 0 THEN 0 ELSE DATEDIFF(MINUTE, MAX([value]), @End_Date) END
FROM (VALUES (@Start_Date), (DATETIMEFROMPARTS(YEAR(@End_Date), MONTH(@End_Date), DAY(@End_Date), 6, 0, 0, 0))) AS [data]([value])

Fourth, if either start or end dates land on a weekend, it should be pushed away from there:

DECLARE @Mod int

SET @Mod = CONVERT(int, @Start_Date) % 7
IF @Mod IN (5, 6)
    SET @Start_Date = DATEADD(DAY, CASE @Mod WHEN 5 THEN 2 WHEN 6 THEN 1 ELSE 0/0 END, DATETIMEFROMPARTS(YEAR(@Start_Date), MONTH(@Start_Date), DAY(@Start_Date), 6, 0, 0, 0))

SET @Mod = CONVERT(int, @End_Date) % 7
IF @Mod IN (5, 6)
    SET @End_Date = DATEADD(DAY, CASE @Mod WHEN 5 THEN -1 WHEN 6 THEN -2 ELSE 0/0 END, DATETIMEFROMPARTS(YEAR(@End_Date), MONTH(@End_Date), DAY(@End_Date), 20, 0, 0, 0))

Lastly, the issue of having weekend days fully encompassed within your target period, for that have a look at this question, from the votes there I can only guess they worked it out already.

KtX2SkD
  • 752
  • 4
  • 12