I suspect there's an all math way to do it, but take a look at the following, which illustrates the process somewhat:DECLARE @TIME AS time = GETDATE();
DECLARE @TIME_2 AS time = CASE WHEN DATEPART(hour, @TIME) = 23 THEN @TIME ELSE DATEADD(hour, 1, CAST(@TIME AS datetime)) END;
DECLARE @HOUR AS tinyint = DATEPART(hour, @TIME);
DECLARE @MINUTES AS tinyint = DATEPART(minute, @TIME);
CREATE TABLE #CAPACITY_OVER_TIME (
TIME_SEQUENCE_NUMBER tinyint NOT NULL PRIMARY KEY CLUSTERED,
START_TIME time NOT NULL,
END_TIME time NOT NULL,
CAPACITY int NOT NULL
);
INSERT INTO #CAPACITY_OVER_TIME (TIME_SEQUENCE_NUMBER, START_TIME, END_TIME, CAPACITY)
SELECT 0, '00:00:00.000', '01:00:00.000', 0 UNION ALL
SELECT 1, '01:00:00.000', '02:00:00.000', 0 UNION ALL
SELECT 2, '02:00:00.000', '03:00:00.000', 0 UNION ALL
SELECT 3, '03:00:00.000', '04:00:00.000', 0 UNION ALL
SELECT 4, '04:00:00.000', '05:00:00.000', 0 UNION ALL
SELECT 5, '05:00:00.000', '06:00:00.000', 0 UNION ALL
SELECT 6, '06:00:00.000', '07:00:00.000', 0 UNION ALL
SELECT 7, '07:00:00.000', '08:00:00.000', 0 UNION ALL
SELECT 8, '08:00:00.000', '09:00:00.000', 750 UNION ALL
SELECT 9, '09:00:00.000', '10:00:00.000', 750 UNION ALL
SELECT 10, '10:00:00.000', '11:00:00.000', 750 UNION ALL
SELECT 11, '11:00:00.000', '12:00:00.000', 600 UNION ALL
SELECT 12, '12:00:00.000', '13:00:00.000', 750 UNION ALL
SELECT 13, '13:00:00.000', '14:00:00.000', 750 UNION ALL
SELECT 14, '14:00:00.000', '15:00:00.000', 750 UNION ALL
SELECT 15, '15:00:00.000', '16:00:00.000', 600 UNION ALL
SELECT 16, '16:00:00.000', '17:00:00.000', 750 UNION ALL
SELECT 17, '17:00:00.000', '18:00:00.000', 750 UNION ALL
SELECT 18, '18:00:00.000', '19:00:00.000', 750 UNION ALL
SELECT 19, '19:00:00.000', '20:00:00.000', 0 UNION ALL
SELECT 20, '20:00:00.000', '21:00:00.000', 0 UNION ALL
SELECT 21, '21:00:00.000', '22:00:00.000', 0 UNION ALL
SELECT 22, '22:00:00.000', '23:00:00.000', 0 UNION ALL
SELECT 23, '23:00:00.000', '23:59:59.997', 0;
SELECT @TIME AS TIME_TO_MEASURE, @TIME_2 AS NEXT_HOUR, @HOUR AS CURRENT_HOUR, 60 - @MINUTES AS MINUTES_REMAINING,
*,
ROUND(
SUM(CASE
WHEN C.END_TIME > @TIME_2 THEN C.CAPACITY
ELSE 0
END +
CASE
WHEN C.TIME_SEQUENCE_NUMBER = @HOUR THEN ((60 - @MINUTES) / 60.) * C.CAPACITY
ELSE 0
END) OVER(ORDER BY C.TIME_SEQUENCE_NUMBER), 0) AS REMAINING_CAPACITY
FROM #CAPACITY_OVER_TIME AS C
WHERE C.END_TIME > @TIME_2
OR C.TIME_SEQUENCE_NUMBER = @HOUR;
DROP TABLE #CAPACITY_OVER_TIME;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)