Enhancing Time Slicing with Stored Procedure: Part 2

Welcome to the second part of our two-part series on Enhancing Time Slicing with Stored Procedure: Part 2. Building on the previous post, where we created a user-defined function (UDF) for evenly slicing 24 hours, this instalment introduces a stored procedure (dbo.usp_getTimeSlicedInMin) to further enhance your database capabilities.

The Stored Procedure

Let’s delve into the creation of the stored procedure:

CREATE PROC dbo.usp_getTimeSlicedInMin (@Interval INT)
AS
------ ==== Test Data =====
--DECLARE @Interval INT
--SET @Interval = 60
 ------ ========================

SELECT left(StartTime, len(StartTime) - 3) StartTime
    ,left(StopTime, len(StopTime) - 3) StopTime
    ,LEFT(StartTime, 2) * 60 + RIGHT(LEFT(StartTime, 5), 2) StartTimeInMin
    ,LEFT(StopTime, 2) * 60 + RIGHT(LEFT(StopTime, 5), 2) + 1 StopTimeInMin -- one has to be added is added (see notes below)
FROM udf_EvenlySlice24hours(@Interval)

/*  ====== Notes 
1 is added to StopTimeInMin so that in the calling SP the parameter passed by the user need can include
the last minute till which he wants to end time. This is done for user friendliness. 
For example, if the user wants to end the timer at 5 pm, he should put in 1020 = (17 * 60). 
Later on in the code, 3 milliseconds should be deducted like below:

@StopTime = DATEADD(ms, -3, dateadd(minute, [last minute till which he wants to end time], @StopTime))
-- Subtract -3 ms to get the last time
*/

Understanding the Stored Procedure

The stored procedure dbo.usp_getTimeSlicedInMin utilizes the previously created UDF udf_EvenlySlice24hours to retrieve time periods based on a specified interval. Here’s a breakdown of the key components:

• The SELECT statement extracts StartTime and StopTime from the UDF result.

• The expressions using LEFT, RIGHT, and LEN functions are employed to manipulate the format of StartTime and StopTime for readability.

SELECT left(StartTime, len(StartTime) - 3) StartTime
    ,left(StopTime, len(StopTime) - 3) StopTime

• The subsequent calculations convert StartTime and StopTime into minutes for better usability.

,LEFT(StartTime, 2) * 60 + RIGHT(LEFT(StartTime, 5), 2) StartTimeInMin
,LEFT(StopTime, 2) * 60 + RIGHT(LEFT(StopTime, 5), 2) + 1 StopTimeInMin

User-Friendly Time Ending

The addition of 1 to StopTimeInMin allows users to specify the last minute they want to include in the result. This adjustment enhances user-friendliness when defining the endpoint of a time range. The notes in the code provide clarity on this approach.

Sample Usage

Similar to the previous post, you can test and use this stored procedure by calling it with a specific interval:

-- Sample Usage
EXEC dbo.usp_getTimeSlicedInMin @Interval = 60

The stored procedure leverages the UDF to generate time periods and presents the results with additional minute-based information.

Result of the Stored Procedure

The result might look like this:

StartTime StopTime StartTimeInMin StopTimeInMin
--------- -------- -------------- -------------
00:00     00:59    0              59
01:00     01:59    60             119
02:00     02:59    120            179
...       ...      ...            ...
22:00     22:59    1320           1379
23:00     23:59    1380           1439

The result extracts StartTime and StopTime from the UDF result, manipulates the format for readability, and converts times into minutes. The stored procedure further allows users to specify the last minute they want to include in the result for enhanced flexibility.

In this two-part series, we've explored the creation of a UDF and a stored procedure to efficiently slice 24 hours. These tools can be valuable in scenarios such as scheduling, reporting, or data aggregation. Stay tuned for more Business Intelligence-related posts. Happy coding!