Convert Time to Hour Block

gistfile1.sql

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  	Mark Moloney
-- Create date: 4/10/2012
-- Description:	Convert a time to the appropriate hour block
-- =============================================
CREATE FUNCTION ConvertTimeToHourBlock 
(
	-- Add the parameters for the function here
	@time int
	,@lagInMinutes int
)
RETURNS int
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Result int
	DECLARE @hours int
	DECLARE @minutes int
	DECLARE @seconds int
	DECLARE @durationInSeconds int
	DECLARE @adjustedDuration int

	-- Add the T-SQL statements to compute the return value here
	SET @hours = @time / 10000
	SET @minutes = @time / 100 % 100
	SET @seconds = @time % 100
	SET @durationInSeconds = (@hours * 60 * 60) + (@minutes * 60) + @seconds
	SET @adjustedDuration = @durationInSeconds - (@lagInMinutes * 60)
	
	SELECT @Result = (@adjustedDuration / (60 * 60)) * 10000

	-- Return the result of the function
	RETURN @Result

END
GO