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.