Let's look at the following example: you have an air traffic controller who worked for 34 hours continuously and you want to calculate how many hours were "overtime", or outside 08:00 - 16:00 period. If he started working at Jan 1st 2012 07:00 and finished his shift on Jan 2nd 2012 17:00. Performing this calculation manually, you would get that he worked 1080 minutes (18 hours) overtime - 9 hours on the first day, and 9 hours on the second day. The function I am going to show you calculates this:
CREATE
FUNCTION
[dbo].[GetMinuteCount]
(
-- Add the parameters for the function here
@StartTime DateTime,
@StopTime DateTime,
@StartTimeFilter
varchar
(10),
@StopTimeFilter
varchar
(10)
)
RETURNS
int
AS
BEGIN
DECLARE
@MinuteCount
int
SET
@MinuteCount = 0
-- Start & End time for the first day
DECLARE
@StartTime1 DateTime
DECLARE
@StopTime1 DateTime
-- Start & End time for the last day
DECLARE
@StartTime2 DateTime
DECLARE
@StopTime2 DateTime
DECLARE
@DateCounter DateTime
SET
@DateCounter = DATEADD(d, 1, @StartTime)
SET
@StartTime1 =
CONVERT
(DateTime, (
CONVERT
(nvarchar(10), @StartTime , 101) +
' '
+ @StartTimeFilter))
SET
@StartTime2 =
CONVERT
(DateTime, (
CONVERT
(nvarchar(10), @StopTime , 101) +
' '
+ @StartTimeFilter))
IF @StopTimeFilter =
'24:00'
SET
@StopTime1 = DATEADD(
day
, 1,
CONVERT
(DateTime, (
CONVERT
(nvarchar(10), @StartTime , 101))))
ELSE
SET
@StopTime1 =
CONVERT
(DateTime, (
CONVERT
(nvarchar(10), @StartTime , 101) +
' '
+ @StopTimeFilter))
IF @StopTimeFilter =
'24:00'
SET
@StopTime2 = DATEADD(
day
, 1,
CONVERT
(DateTime, (
CONVERT
(nvarchar(10), @StopTime , 101))))
ELSE
SET
@StopTime2 =
CONVERT
(DateTime, (
CONVERT
(nvarchar(10), @StopTime , 101) +
' '
+ @StopTimeFilter))
IF @StartTime1 > @StopTime1
RETURN
dbo.GetMinuteCount(@StartTime, @StopTime,
'00:00'
, @StopTimeFilter)
+
dbo.GetMinuteCount(@StartTime, @StopTime, @StartTimeFilter,
'24:00'
)
-- Easy part: both start and stop time are are within the same day
IF (
SELECT
DATEDIFF(d, @StartTime, @StopTime)) = 0
BEGIN
IF @StartTime <= @StartTime1
SET
@StartTime = @StartTime1
IF @StopTime >= @StopTime1
SET
@StopTime = @StopTime1
SET
@MinuteCount = DATEDIFF(
minute
, @StartTime, @StopTime)
IF @MinuteCount < 0
SET
@MinuteCount = 0
END
ELSE
IF (
SELECT
DATEDIFF(d, @StartTime, @StopTime)) > 0
BEGIN
-- Add minutes from the first day
IF @StartTime <= @StartTime1
SET
@StartTime = @StartTime1
IF DATEDIFF(
minute
, @StartTime, @StopTime1) > 0
SET
@MinuteCount = DATEDIFF(
minute
, @StartTime, @StopTime1)
-- Add minutes from the rest of days
WHILE (
SELECT
DATEDIFF(d, @DateCounter, @StopTime)) >= 0
BEGIN
IF (
SELECT
DATEDIFF(d, @DateCounter, @StopTime)) > 0
-- Add minutes from @StartTimeFilter to @StopTimeFilter
SET
@MinuteCount = @MinuteCount
+ DATEDIFF
(
minute
,
CONVERT
(DateTime, (
CONVERT
(nvarchar(10), @DateCounter , 101) +
' '
+ @StartTimeFilter)),
CASE
@StopTimeFilter
WHEN
'24:00'
THEN
DATEADD(
Day
, 1,
CONVERT
(DateTime, (
CONVERT
(nvarchar(10), @DateCounter , 101))))
ELSE
CONVERT
(DateTime, (
CONVERT
(nvarchar(10), @DateCounter , 101) +
' '
+ @StopTimeFilter))
END
)
ELSE
BEGIN
IF @StopTime >= @StopTime2
SET
@StopTime = @StopTime2
IF @StopTime
BETWEEN
@StartTime2
AND
@StopTime2
SET
@MinuteCount = @MinuteCount + DATEDIFF(
minute
, @StartTime2, @StopTime)
END
SET
@DateCounter = DATEADD(d, 1, @DateCounter)
END
END
RETURN
@MinuteCount
END
In our example, @StartTime and @StopTime parameters represent the beggining and the end of our air traffic controller shift. @StartTimeFilter and @StopTimeFilter parameters represent the "time-frame" we want our calculation to be performed for.
Now, let's create 3 groups of "time-window" parameters (work.time, over-time and total):
), but I know you aren't going to use my function for this final calculation because you could get away with SQL Server in-built DATEDIFF function :).
Thank you for reading, I hope you'll find this function useful.