SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Calculating varying process capacity


Calculating varying process capacity

Author
Message
George W (no relation)
George W (no relation)
SSChasing Mays
SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)

Group: General Forum Members
Points: 607 Visits: 451
Hello again, SQL experts,

I'm facing an interesting challenge. I have a production process that runs from 8 AM to 7 PM. Process capacity is 750 units per hour *except* for the 11 AM to noon hour and the 3 PM to 4 PM hour, which each have a capacity of 600 units. My challenge is to estimate the remaining capacity to end of day at any given time (down to the minute) during the day.

For example, at 8 AM, remaining capacity is 7,950, which is the total daily capacity. At 11:45 AM, remaining capacity is 5,250, which is .25 * 11 AM capacity (650) + capacity from 12:00 PM - 7:00 PM.

All of the solutions that pop into my head seem pretty clunky, so I'm wondering if anyone knows of an elegant way to do this in SQL.

Thanks in advance!

George
sgmunson
sgmunson
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39591 Visits: 5379
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)
Smile Smile Smile
Health & Nutrition
Steve Jones
Steve Jones
SSC Guru
SSC Guru (299K reputation)SSC Guru (299K reputation)SSC Guru (299K reputation)SSC Guru (299K reputation)SSC Guru (299K reputation)SSC Guru (299K reputation)SSC Guru (299K reputation)SSC Guru (299K reputation)

Group: Administrators
Points: 299836 Visits: 20005
I like Steve's solution. I'd be tempted to make a permanent table that tracks capacity by time (and potentially by machine/location/etc). Then use that as a way of performing calculations.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)

Group: General Forum Members
Points: 202026 Visits: 40018

Since you are only looking for an estimate of capacity remaining this should work:

DECLARE @DailyCapacity INT = 7950, -- Daily capacity
@Minutes DECIMAL(18,2) = 660, -- Total minutes available per day
@StartTime TIME = '08:00:00', -- Daily start time from capacity calculation
@TestTime DATETIME = GETDATE();

SELECT @DailyCapacity, @Minutes, @StartTime, ROUND(@DailyCapacity * ((@Minutes - DATEDIFF(MINUTE,@StartTime,CAST(@TestTime AS TIME))) / @Minutes),0);



Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (459K reputation)SSC Guru (459K reputation)SSC Guru (459K reputation)SSC Guru (459K reputation)SSC Guru (459K reputation)SSC Guru (459K reputation)SSC Guru (459K reputation)SSC Guru (459K reputation)

Group: General Forum Members
Points: 459437 Visits: 43757
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. Wink
,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. Wink
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.


--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
George W (no relation)
George W (no relation)
SSChasing Mays
SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)

Group: General Forum Members
Points: 607 Visits: 451
Thanks, all! Very helpful!

George
drew.allen
drew.allen
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33003 Visits: 13052
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. Wink
,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. Wink
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


J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
George W (no relation)
George W (no relation)
SSChasing Mays
SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)

Group: General Forum Members
Points: 607 Visits: 451
Nice, Drew... very clever!
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (459K reputation)SSC Guru (459K reputation)SSC Guru (459K reputation)SSC Guru (459K reputation)SSC Guru (459K reputation)SSC Guru (459K reputation)SSC Guru (459K reputation)SSC Guru (459K reputation)

Group: General Forum Members
Points: 459437 Visits: 43757
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. Wink
,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. Wink
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


Heh... remind me not to post code after midnight. Nice job, Drew.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
aaron.reese
aaron.reese
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5723 Visits: 1004
@ Jeff,

Is that midnight your time, my time or SQLServerCentral time :0
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search