drew.allen - Monday, December 11, 2017 8:45 AM

Jeff Moden - Sunday, December 10, 2017 10:10 PM

I'm with Steve.  I love "Helper Tables" because you can change their content instead of changing code.  If it were my task, here's the table that I'd build.


 CREATE TABLE dbo.CapacityByPeriod
        (
         PeriodStartTime            TIME NOT NULL
        ,PeriodEndTime              TIME NOT NULL
        ,PeriodCapacity             INT  NOT NULL
        ,PeriodMinutes              AS DATEDIFF(mi,PeriodStartTime,PeriodEndTime) PERSISTED --Just in case it ever comes up. ;)
        ,PeriodPerMinuteCapacity    AS PeriodCapacity*1.0/DATEDIFF(mi,PeriodStartTime,PeriodEndTime) PERSISTED
        CONSTRAINT PK_CapacityByPeriod PRIMARY KEY CLUSTERED (PeriodStartTime)
        )
;
 INSERT INTO dbo.CapacityByPeriod
        (PeriodStartTime,PeriodEndTime,PeriodCapacity)
 VALUES  ('00:00','08:00', 0  )
        ,('08:00','09:00', 750)
        ,('09:00','10:00', 750)
        ,('10:00','11:00', 750)
        ,('11:00','12:00', 600)
        ,('12:00','13:00', 750)
        ,('13:00','14:00', 750)
        ,('14:00','15:00', 750)
        ,('15:00','16:00', 600)
        ,('16:00','17:00', 750)
        ,('17:00','18:00', 750)
        ,('18:00','19:00', 750)
        ,('19:00','23:59:59.9999999', 0)
;


Then, I'd build this function and use it according to the "Usage" examples in the header.  If your DBA(s) object to functions or helper tables, please see the "Developer Notes" in the header of the function for how to convince them otherwise.


 CREATE FUNCTION dbo.TimeCapacityRemaining
/****************************************************************************************
 Purpose:
 Given a 24 hour time of day, return the remaining capacity for the day according to the
 dbo.CapacityByPeriod table.
-----------------------------------------------------------------------------------------
 Dependencies:
 dbo.CapacityByPeriod (Table) must be in the same database.
-----------------------------------------------------------------------------------------
 Usage:
--===== Syntax
 SELECT CapacityRemaining FROM dbo.TimeCapacityRemaining(@pSomeTime)
;
--===== Example Usage for 6:42 PM
 SELECT CapacityRemaining FROM dbo.TimeCapacityRemaining('18:42')
;
--===== Example Usage using a variable
DECLARE @SomeTime TIME = '18:42';
 SELECT CapacityRemaining FROM dbo.TimeCapacityRemaining(@SomeTime)
;
--===== Example Usage when played against a TIME column in a table.
 SELECT  st.TimeColumn
        ,cr.CapacityRemaining
   FROM dbo.SomeTable st
  CROSS APPLY dbo.TimeCapacityRemaining(st.TimeColumn) cr
;
-----------------------------------------------------------------------------------------
 Developer Notes:
 1. If your DBA(s) object to using functions, tell them that this is a high performance
    "iTVF" (Inline Table Value Function) that works as fast as if the code were inline
    in a query.  If they don't believe you or continue to object just because it's a
    function, direct them to Reference #1 below for repeatable and demonstrable proof
    that it's not a problem.
 2. If they still insist on not letting you use it just because it's a function, thank
    them for their time and then recommend to management that they get a smarter DBA.
    Before they leave, ask them if they know how to get the current date and time. ;)
 3. If the DBA(s) object to the use of "Helper Tables", refer to Note 2 above.
-----------------------------------------------------------------------------------------
 References:
 1. http://www.sqlservercentral.com/articles/T-SQL/91724/
 2. https://www.sqlservercentral.com/Forums/1911664/Calculating-varying-process-capacity
-----------------------------------------------------------------------------------------
 Revision History:
 Rev 00 - 10 Dec 2017 - Jeff Moden
                      - Initial creation and unit test according to Reference 2.
****************************************************************************************/
--===== Declare the I/O for this function
        (@pSomeTime TIME)
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== Return the remaining capacity for the day using the given time as a starting point.
 SELECT CapacityRemaining =
        (--=== Aggregate the capacity of the remaining whole periods.
         SELECT SUM(PeriodCapacity)
           FROM dbo.CapacityByPeriod
          WHERE PeriodStartTime >= @pSomeTime
        )
      + (--==== Calculate the capacity of time remaining in the current period.
         SELECT DATEDIFF(mi,@pSomeTime,PeriodEndTime) * PeriodPerMinuteCapacity
           FROM dbo.CapacityByPeriod
          WHERE PeriodStartTime <= @pSomeTime AND @pSomeTime < PeriodEndTime
        )
;


As a bit of a sidebar, the times in the table do NOT need to be at the top of the hour.  They can be anytime provided that you pay attention and avoid gaps and overlaps in the table and that the first start time is 00:00 and the last end time is 23:59:59.99999.

Again, this is a situation where you can improve the efficiency by thinking outside of the box.  You can cut the number of scans/reads in half.  By focusing on the period start time, you are forced to divide your results into two subsets: periods that start after the relevant time and the one period that starts before, but ends after the relevant time.  If, instead, you focus on the period end time, you only have one set: periods that end after the relevant time (regardless of whether they start before or after the relevant time).

SELECT SUM(
    CASE 
        WHEN PeriodStartTime <= @pSomeTime
        THEN DATEDIFF(mi,@pSomeTime,PeriodEndTime) * PeriodPerMinuteCapacity
        ELSE PeriodCapacity
    END
    ) AS Capacity_Remaining
FROM dbo.CapacityByPeriod
WHERE @pSomeTime < PeriodEndTime
;


Drew