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 :).