USE [Job]
GO
/****** Object:  UserDefinedFunction [dbo].[fnGetDateRanges]    Script Date: 01/15/2013 11:03:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/*
Author:			Delora Bradish
Date Created:	February 22, 2008
Description:	Get date comparisons
Where Used:		SSRS Report defaults
Notes:			
To run:			SELECT * FROM fnGetDateRanges ('2/29/2012')
				SELECT * FROM fnGetDateRanges (GetDate())
Revision History:	
*/

ALTER FUNCTION [dbo].[fnGetDateRanges] (@i_Date	DateTime)
RETURNS table
AS
RETURN
(
SELECT @i_Date as iDate
		, CurrentDate = Convert(DateTime,Convert(varchar(8),@i_Date,112),1)
		, CurrentDay = Day(@i_Date)
		, CurrentWeek = DatePart(Week, @i_Date)
		, CurrentMonth = Month(@i_Date)
		, CurrentYearMonth = Convert(int,Convert(char(4),Year(@i_Date)) + RIGHT(RTrim('00' + Convert(char(2),Month(@i_Date))),2))
		, PriorDay1 = Convert(DateTime,Convert(varchar(8),@i_Date -1,112),1)
		, PriorWeek = DatePart(Week, @i_Date - 7)
		, PriorMonth = DatePart(mm,dateadd(day,(day(@i_date)-1)*-1,dateadd(month,-1,@i_date)))

		--you will probably want to trap for leap years here
		, PriorYear1 = DatePart(yy, @i_Date - 365) 
		, PriorYear2 = DatePart(yy, @i_Date - 730)
		, PriorYear3 = DatePart(yy, @i_Date - 1086)
		, PriorYear4 = DatePart(yy, @i_Date - 1451)
		, PriorYear5 = DatePart(yy, @i_Date - 1816)
		, PriorYear6 = DatePart(yy, @i_Date - 2181)

		, SameDayLastYear = CASE WHEN Right(Convert(char(8),@i_Date,112),4) = '0229'
								THEN @i_Date - 366
								ELSE @i_Date - 365
							END

		, FirstDayCurrentMonth = DATEADD(mm, DATEDIFF(mm, 0, @i_Date), 0)
		, FirstDayCurrentWeek = DATEADD(ww, DATEDIFF(ww, 0, @i_Date), 0)
		, FirstDayCurrentYear = DATEADD(yy, DATEDIFF(yy, 0, @i_Date), 0)
		, LastDayCurrentMonth = dateadd(mm, datediff(mm, 0, dateadd(mm, 1, @i_date)), -1)
		, LastDayCurrentWeek = dateadd(ww, datediff(ww, 0, dateadd(ww, 1, @i_date)), -1)
		, LastDayCurrentYear = dateadd(yy, datediff(yy, 0, dateadd(yy, 1, @i_date)), -1)

		, FirstDayPriorMonth1 = Convert(Date,Convert(varchar(2),Month(DateAdd(month,-1,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-1,@i_Date))))
		, FirstDayPriorMonth2 = Convert(Date,Convert(varchar(2),Month(DateAdd(month,-2,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-2,@i_Date))))
		, FirstDayPriorMonth3 = Convert(Date,Convert(varchar(2),Month(DateAdd(month,-3,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-3,@i_Date))))
		, FirstDayPriorMonth4 = Convert(Date,Convert(varchar(2),Month(DateAdd(month,-4,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-4,@i_Date))))
		, FirstDayPriorMonth5 = Convert(Date,Convert(varchar(2),Month(DateAdd(month,-5,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-5,@i_Date))))
		, FirstDayPriorMonth6 = Convert(Date,Convert(varchar(2),Month(DateAdd(month,-6,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-6,@i_Date))))
		, FirstDayPriorMonth7 = Convert(Date,Convert(varchar(2),Month(DateAdd(month,-7,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-7,@i_Date))))
		, FirstDayPriorMonth8 = Convert(Date,Convert(varchar(2),Month(DateAdd(month,-8,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-8,@i_Date))))
		, FirstDayPriorMonth0 = Convert(Date,Convert(varchar(2),Month(DateAdd(month,-9,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-9,@i_Date))))
		, FirstDayPriorMonth10 = Convert(Date,Convert(varchar(2),Month(DateAdd(month,-10,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-10,@i_Date))))
		, FirstDayPriorMonth11 = Convert(Date,Convert(varchar(2),Month(DateAdd(month,-11,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-11,@i_Date))))
		, FirstDayPriorMonth12 = Convert(Date,Convert(varchar(2),Month(DateAdd(month,-12,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-12,@i_Date))))
		, FirstDayPriorMonth13 = Convert(Date,Convert(varchar(2),Month(DateAdd(month,-13,@i_Date)))+ '/1/' + Convert(char(4),Year(DateAdd(month,-13,@i_Date))))

		--this is the prior YYYYMM from the date passed, not the prior year's month number
		, PriorMonth1 = Month(DateAdd(month,-1,@i_Date))
		, PriorMonth2 = Month(DateAdd(month,-2,@i_Date))
		, PriorMonth3 = Month(DateAdd(month,-3,@i_Date))
		, PriorMonth4 = Month(DateAdd(month,-4,@i_Date))
		, PriorMonth5 = Month(DateAdd(month,-5,@i_Date))
		, PriorMonth6 = Month(DateAdd(month,-6,@i_Date))
		, PriorMonth7 = Month(DateAdd(month,-7,@i_Date))
		, PriorMonth8 = Month(DateAdd(month,-8,@i_Date))
		, PriorMonth9 = Month(DateAdd(month,-9,@i_Date))
		, PriorMonth10 = Month(DateAdd(month,-10,@i_Date))
		, PriorMonth11 = Month(DateAdd(month,-11,@i_Date))
		, PriorMonth12 = Month(DateAdd(month,-12,@i_Date))
		, PriorMonth13 = Month(DateAdd(month,-13,@i_Date))
		
		, PriorYearMonth1 = Convert(int,Convert(char(4),Year(DateAdd(month,-1,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-1,@i_Date)))),2))
		, PriorYearMonth2 = Convert(int,Convert(char(4),Year(DateAdd(month,-2,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-2,@i_Date)))),2))
		, PriorYearMonth3 = Convert(int,Convert(char(4),Year(DateAdd(month,-3,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-3,@i_Date)))),2))
		, PriorYearMonth4 = Convert(int,Convert(char(4),Year(DateAdd(month,-4,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-4,@i_Date)))),2))
		, PriorYearMonth5 = Convert(int,Convert(char(4),Year(DateAdd(month,-5,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-5,@i_Date)))),2))
		, PriorYearMonth6 = Convert(int,Convert(char(4),Year(DateAdd(month,-6,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-6,@i_Date)))),2))
		, PriorYearMonth7 = Convert(int,Convert(char(4),Year(DateAdd(month,-7,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-7,@i_Date)))),2))
		, PriorYearMonth8 = Convert(int,Convert(char(4),Year(DateAdd(month,-8,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-8,@i_Date)))),2))
		, PriorYearMonth9 = Convert(int,Convert(char(4),Year(DateAdd(month,-9,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-9,@i_Date)))),2))
		, PriorYearMonth10 = Convert(int,Convert(char(4),Year(DateAdd(month,-10,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-10,@i_Date)))),2))
		, PriorYearMonth11 = Convert(int,Convert(char(4),Year(DateAdd(month,-11,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-11,@i_Date)))),2))
		, PriorYearMonth12 = Convert(int,Convert(char(4),Year(DateAdd(month,-12,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-12,@i_Date)))),2))
		, PriorYearMonth13 = Convert(int,Convert(char(4),Year(DateAdd(month,-13,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,-13,@i_Date)))),2))
		
		--this is the next YYYYMM from the date past, not next year's month number
		, NextYearMonth1 = Convert(int,Convert(char(4),Year(DateAdd(month,+1,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,+1,@i_Date)))),2))
		, NextYearMonth2 = Convert(int,Convert(char(4),Year(DateAdd(month,+2,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,+2,@i_Date)))),2))
		, NextYearMonth3 = Convert(int,Convert(char(4),Year(DateAdd(month,+3,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,+3,@i_Date)))),2))
		, NextYearMonth4 = Convert(int,Convert(char(4),Year(DateAdd(month,+4,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,+4,@i_Date)))),2))
		, NextYearMonth5 = Convert(int,Convert(char(4),Year(DateAdd(month,+5,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,+5,@i_Date)))),2))
		, NextYearMonth6 = Convert(int,Convert(char(4),Year(DateAdd(month,+6,@i_Date))) + RIGHT(RTrim('00' + Convert(char(2),Month(DateAdd(month,+6,@i_Date)))),2))
		
		, FirstDayPriorWeek1 = DATEADD(ww, DATEDIFF(ww, 0, @i_Date), 0) - 7

		, FirstDayPriorYear1 = DATEADD(yy, DATEDIFF(yy, 366, @i_Date), 0)
		, FirstDayPriorYear2 = DATEADD(yy, DATEDIFF(yy, 731, @i_Date), 0)
		, FirstDayPriorYear3 = DATEADD(yy, DATEDIFF(yy, 1096, @i_Date), 0)
		, FirstDayPriorYear4 = DATEADD(yy, DATEDIFF(yy, 1461, @i_Date), 0)
		, FirstDayPriorYear5 = DATEADD(yy, DATEDIFF(yy, 1826, @i_Date), 0)
		, FirstDayPriorYear6 = DATEADD(yy, DATEDIFF(yy, 2191, @i_Date), 0)

		, LastDayPriorMonth1 = Convert(DateTime,Convert(char(8),dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,@i_date), 0)),112),1)
		--this calculation assumes a Monday (start) to Sunday (end) week
		, LastDayPriorWeek1 = Convert(DateTime,Convert(char(8),dateadd(ms,-3,DATEADD(ww, DATEDIFF(ww,0,@i_date), 0)),112),1)
		, LastYearLastDayPriorMonth1 = Convert(DateTime,Convert(char(8),dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,365,@i_date), 0)),112),1)
		
		--you may want to trap for leap years here
		, LastDayPriorYear1 = DATEADD(yy, DATEDIFF(yy, 1, @i_Date), -1)
		, LastDayPriorYear2 = DATEADD(yy, DATEDIFF(yy, 366, @i_Date), -1)
		, LastDayPriorYear3 = DATEADD(yy, DATEDIFF(yy, 731, @i_Date), -1)
		, LastDayPriorYear4 = DATEADD(yy, DATEDIFF(yy, 1096, @i_Date), -1)
		, LastDayPriorYear5 = DATEADD(yy, DATEDIFF(yy, 1461, @i_Date), -1)
		, LastDayPriorYear6 = DATEADD(yy, DATEDIFF(yy, 1826, @i_Date), -1)

		, PriorYTD1 = DATEADD(yy, -1, Convert(varchar(25),@i_Date,112))
		, PriorYTD2 = DATEADD(yy, -2, Convert(varchar(25),@i_Date,112))
		, PriorYTD3 =  DATEADD(yy, -3, Convert(varchar(25),@i_Date,112))
		, PriorYTD4 = DATEADD(yy, -4, Convert(varchar(25),@i_Date,112))
		, PriorYTD5 = DATEADD(yy, -5, Convert(varchar(25),@i_Date,112))
		, PriorYTD6 = DATEADD(yy, -6, Convert(varchar(25),@i_Date,112))
		
		, NextYTD1 = DATEADD(yy, +1, Convert(varchar(25),@i_Date,112))
		, NextYTD2 = DATEADD(yy, +2, Convert(varchar(25),@i_Date,112))
		, NextYTD3 = DATEADD(yy, +3, Convert(varchar(25),@i_Date,112))
		, NextYTD4 = DATEADD(yy, +4, Convert(varchar(25),@i_Date,112))
		, NextYTD5 = DATEADD(yy, +5, Convert(varchar(25),@i_Date,112))
		, NextYTD6 = DATEADD(yy, +6, Convert(varchar(25),@i_Date,112))

		, FirstDayYearPriorMonth = CAST ('1/1/' + Cast(year(@i_Date) - case month(@i_Date) WHEN 1 THEN 1 ELSE 0 END as varchar(10)) as datetime)
	
)

