USE [HL_VA_DATA]
GO

/****** Object:  UserDefinedFunction [dbo].[fnDateTimeGetBoundary_Back]    Script Date: 09/28/2013 23:40:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[fnDateTimeGetBoundary_Back] 
(
	-- Add the parameters for the function here (indate, plusorminus, and string for compute type day, weeks, etc
	@indate varchar(20),
	@span int,
	@typecompute varchar(20)
	
)
RETURNS datetime
AS
BEGIN

	declare @curdatetime datetime 
	set @curdatetime = case when @indate = 'today' then getdate() else cast(@indate as datetime) end
	declare @boundarydatetime datetime
	declare @curdt datetime = DateAdd(dd,0,DATEDIFF(dd, 0, getdate()))

	set @boundarydatetime = case 
							when @typecompute = 'minutes'  then dateadd(MI,(DATEDIFF(MI, 0, @curdatetime)) - @span,0)
							when @typecompute = 'hours'	  then dateadd(HH,(DATEDIFF(HH, 0, @curdatetime)) - @span,0)										
							when @typecompute = 'days'	  then dateadd(DD,(DATEDIFF(DD, 0, @curdatetime)) - @span,0)  
							when @typecompute = 'weeks'	  then DATEADD(WK, (DATEDIFF(WK, 0, @curdatetime)) - @span, 0) - 1  -- Monday - 1
							when @typecompute = 'weekends' then DateAdd(WW, -1 * @span, (DateAdd(dd, 7-(DATEPART(dw, @curdt)),@curdt))) 
							when @typecompute = 'weeks - weekdays' then DATEADD(WK, (DATEDIFF(WK, 0, @curdatetime)) - @span, 0) - 1  -- Monday - 1			
							when @typecompute = 'months'	  then dateadd(MM,(DATEDIFF(MM, 0, @curdatetime)) - @span,0) 
							when @typecompute = 'quarters' then dateadd(QQ,(DATEDIFF(QQ, 0, @curdatetime)) - @span,0) 
							when @typecompute = 'years'	  then dateadd(YY,(DATEDIFF(YY, 0, @curdatetime)) - @span,0)
							else CAST ('00000000' as datetime)
						end
	
	RETURN @boundarydatetime

END


GO


USE [HL_VA_DATA]
GO

/****** Object:  UserDefinedFunction [dbo].[fnDateTimeGetBoundary_Forward]    Script Date: 09/28/2013 23:40:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[fnDateTimeGetBoundary_Forward] 
(
	-- Add the parameters for the function here (indate, plusorminus, and string for compute type day, weeks, etc
	@indate varchar(20),
	@span int,
	@typecompute varchar(20)
	
)
RETURNS datetime
AS
BEGIN

	declare @curdatetime datetime 
	set @curdatetime = case when @indate = 'today' then getdate() else cast(@indate as datetime) end
	declare @boundarydatetime datetime
	declare @curdt datetime = DateAdd(dd,0,DATEDIFF(dd, 0, getdate()))

	set @boundarydatetime = case 
								when @typecompute = 'minutes'  then DATEADD(ms,-1,DATEADD(MI, (DATEDIFF(MI, 0, @curdatetime)) + @span + 1,0))  -- current min + 1 - 1 sec
								when @typecompute = 'hours'	then DATEADD(ms,-1,DATEADD(HH, (DATEDIFF(HH, 0, @curdatetime)) + @span + 1,0))  -- next hour		
								when @typecompute = 'days'		then DATEADD(MS,-1,DATEADD(DD, (DATEDIFF(DD, 0, @curdatetime)) + @span + 1,0))  -- current day + 1 - 1 sec
								when @typecompute = 'weeks'	then DATEADD(ms, -1,DATEADD(WK, (DATEDIFF(WK, 0, @curdatetime)) + @span, 6))
								when @typecompute = 'weekends' then DATEADD(ms, -1,DateAdd(WW, @span, (DateAdd(dd, 7-(DATEPART(dw, @curdt)),@curdt)) ) + 2)
								when @typecompute = 'weeks - weekdays'	then DATEADD(ms, -1,DATEADD(wk, (DATEDIFF(WK, 0, @curdatetime)) + @span, 5))
								when @typecompute = 'months'	then DATEADD(ms,-1,DATEADD(MM, (DATEDIFF(MM, 0, @curdatetime)) + @span + 1,0))
								when @typecompute = 'quarters' then DATEADD(ms,-1,DATEADD(QQ, (DATEDIFF(QQ, 0, @curdatetime)) + @span + 1,0))  -- current quarter  + 1 - 1 sec
								when @typecompute = 'years'	then DATEADD(ms,-1,DATEADD(YY, (DATEDIFF(YY, 0, @curdatetime)) + @span + 1,0))  -- current year + 1 - 1 sec
								else CAST ('00000000' as datetime)
							end

	RETURN @boundarydatetime

END


GO