robjlong
2/26/2015 - 3:22 PM
Date / Int Range Calculation Function
Date / Int Range Calculation Function
-- dbo.fn_RangeCalc
--
-- Returns Range Value Information Based on Inputs
--
--
--
--
--
--------------------------------------------------------------------------------
-- Change History
--------------------------------------------------------------------------------
-- Version Date Author Description
-- ------- ---------- ----------- ------------------------------------
-- 1.0 02/25/2015 Rob Long Initial Creation
--------------------------------------------------------------------------------
CREATE FUNCTION dbo.fn_RangeCalc
(
@DateValue DATETIME = NULL
, @IntValue INT = NULL
)
RETURNS TABLE
AS
RETURN
( SELECT YearOffsetText = CASE WHEN DATEDIFF(YEAR, GETDATE(), @DateValue) = 0 THEN 'This Year'
WHEN DATEDIFF(YEAR, GETDATE(), @DateValue) = 1 THEN 'Next Year'
WHEN DATEDIFF(YEAR, GETDATE(), @DateValue) = -1 THEN 'Last Year'
WHEN DATEDIFF(YEAR, GETDATE(), @DateValue) > 1
THEN 'In ' + CAST(ABS(DATEDIFF(YEAR, GETDATE(), @DateValue)) AS VARCHAR(10)) + ' Years'
WHEN DATEDIFF(YEAR, GETDATE(), @DateValue) < -1
THEN CAST(ABS(DATEDIFF(YEAR, GETDATE(), @DateValue)) AS VARCHAR(10)) + ' Years Ago'
END
, MonthOffsetText = CASE WHEN DATEDIFF(MONTH, GETDATE(), @DateValue) = 0 THEN 'This Month'
WHEN DATEDIFF(MONTH, GETDATE(), @DateValue) = 1 THEN 'Next Month'
WHEN DATEDIFF(MONTH, GETDATE(), @DateValue) = -1 THEN 'Last Month'
WHEN DATEDIFF(MONTH, GETDATE(), @DateValue) > 1
THEN 'In ' + CAST(ABS(DATEDIFF(MONTH, GETDATE(), @DateValue)) AS VARCHAR(10)) + ' Months'
WHEN DATEDIFF(MONTH, GETDATE(), @DateValue) < -1
THEN CAST(ABS(DATEDIFF(MONTH, GETDATE(), @DateValue)) AS VARCHAR(10)) + ' Months Ago'
END
, WeekOffsetText = CASE WHEN DATEDIFF(WEEK, GETDATE(), @DateValue) = 0 THEN 'This Week'
WHEN DATEDIFF(WEEK, GETDATE(), @DateValue) = 1 THEN 'Next Week'
WHEN DATEDIFF(WEEK, GETDATE(), @DateValue) = -1 THEN 'Last Week'
WHEN DATEDIFF(WEEK, GETDATE(), @DateValue) > 1
THEN 'In ' + CAST(ABS(DATEDIFF(WEEK, GETDATE(), @DateValue)) AS VARCHAR(10)) + ' Weeks'
WHEN DATEDIFF(WEEK, GETDATE(), @DateValue) < -1
THEN CAST(ABS(DATEDIFF(WEEK, GETDATE(), @DateValue)) AS VARCHAR(10)) + ' Weeks Ago'
END
, DayOffsetText = CASE WHEN DATEDIFF(DAY, GETDATE(), @DateValue) = 0 THEN 'Today'
WHEN DATEDIFF(DAY, GETDATE(), @DateValue) = 1 THEN 'Tomorrow'
WHEN DATEDIFF(DAY, GETDATE(), @DateValue) = -1 THEN 'Yesterday'
WHEN DATEDIFF(DAY, GETDATE(), @DateValue) > 1
THEN 'In ' + CAST(ABS(DATEDIFF(DAY, GETDATE(), @DateValue)) AS VARCHAR(10)) + ' Days'
WHEN DATEDIFF(DAY, GETDATE(), @DateValue) < -1
THEN CAST(ABS(DATEDIFF(DAY, GETDATE(), @DateValue)) AS VARCHAR(10)) + ' Days Ago'
END
, YearOffset = DATEDIFF(YEAR, GETDATE(), @DateValue)
, MonthOffset = DATEDIFF(MONTH, GETDATE(), @DateValue)
, WeekOffset = DATEDIFF(WEEK, GETDATE(), @DateValue)
, DayOffset = DATEDIFF(DAY, GETDATE(), @DateValue)
, MonthRangeText = CASE WHEN DATEDIFF(MONTH, GETDATE(), @DateValue) = 0 THEN 'This Month'
WHEN DATEDIFF(MONTH, GETDATE(), @DateValue) = 1 THEN 'Next Month'
WHEN DATEDIFF(MONTH, GETDATE(), @DateValue) = -1 THEN 'Last Month'
WHEN DATEDIFF(MONTH, GETDATE(), @DateValue) BETWEEN -3 AND -2 THEN '2 to 3 Months Ago'
WHEN DATEDIFF(MONTH, GETDATE(), @DateValue) BETWEEN -6 AND -3 THEN '3 to 6 Months Ago'
WHEN DATEDIFF(MONTH, GETDATE(), @DateValue) BETWEEN -9 AND -6 THEN '6 to 9 Months Ago'
WHEN DATEDIFF(MONTH, GETDATE(), @DateValue) BETWEEN -12 AND -9 THEN '6 to 12 Months Ago'
WHEN DATEDIFF(MONTH, GETDATE(), @DateValue) BETWEEN -18 AND -12 THEN '12 to 18 Months Ago'
WHEN DATEDIFF(MONTH, GETDATE(), @DateValue) BETWEEN -24 AND -18 THEN '18 to 24 Months Ago'
WHEN DATEDIFF(MONTH, GETDATE(), @DateValue) < -24 THEN 'Less than 24 Months Ago'
WHEN DATEDIFF(MONTH, GETDATE(), @DateValue) BETWEEN 0 AND 1 THEN 'This Month'
WHEN DATEDIFF(MONTH, GETDATE(), @DateValue) BETWEEN 2 AND 3 THEN '2 to 3 Months'
WHEN DATEDIFF(MONTH, GETDATE(), @DateValue) BETWEEN 3 AND 6 THEN '3 to 6 Months'
WHEN DATEDIFF(MONTH, GETDATE(), @DateValue) BETWEEN 6 AND 9 THEN '6 to 9 Months'
WHEN DATEDIFF(MONTH, GETDATE(), @DateValue) BETWEEN 9 AND 12 THEN '6 to 12 Months'
WHEN DATEDIFF(MONTH, GETDATE(), @DateValue) BETWEEN 12 AND 18 THEN '12 to 18 Months'
WHEN DATEDIFF(MONTH, GETDATE(), @DateValue) BETWEEN 18 AND 24 THEN '18 to 24 Months'
WHEN DATEDIFF(MONTH, GETDATE(), @DateValue) > 24 THEN 'More than 24 Months'
END
, WeekRangeText = CASE WHEN DATEDIFF(WEEK, GETDATE(), @DateValue) = 0 THEN 'This Week'
WHEN DATEDIFF(WEEK, GETDATE(), @DateValue) = 1 THEN 'Next Week'
WHEN DATEDIFF(WEEK, GETDATE(), @DateValue) = -1 THEN 'Last Week'
WHEN DATEDIFF(WEEK, GETDATE(), @DateValue) BETWEEN -3 AND -2 THEN '2 to 3 Weeks Ago'
WHEN DATEDIFF(WEEK, GETDATE(), @DateValue) BETWEEN -6 AND -3 THEN '3 to 6 Weeks Ago'
WHEN DATEDIFF(WEEK, GETDATE(), @DateValue) BETWEEN -9 AND -6 THEN '6 to 9 Weeks Ago'
WHEN DATEDIFF(WEEK, GETDATE(), @DateValue) BETWEEN -12 AND -9 THEN '6 to 12 Weeks Ago'
WHEN DATEDIFF(WEEK, GETDATE(), @DateValue) BETWEEN -18 AND -12 THEN '12 to 18 Weeks Ago'
WHEN DATEDIFF(WEEK, GETDATE(), @DateValue) BETWEEN -24 AND -18 THEN '18 to 24 Weeks Ago'
WHEN DATEDIFF(WEEK, GETDATE(), @DateValue) < -24 THEN 'Less than 24 Weeks Ago'
WHEN DATEDIFF(WEEK, GETDATE(), @DateValue) BETWEEN 2 AND 3 THEN '2 to 3 Weeks'
WHEN DATEDIFF(WEEK, GETDATE(), @DateValue) BETWEEN 3 AND 6 THEN '3 to 6 Weeks'
WHEN DATEDIFF(WEEK, GETDATE(), @DateValue) BETWEEN 6 AND 9 THEN '6 to 9 Weeks'
WHEN DATEDIFF(WEEK, GETDATE(), @DateValue) BETWEEN 9 AND 12 THEN '6 to 12 Weeks'
WHEN DATEDIFF(WEEK, GETDATE(), @DateValue) BETWEEN 12 AND 18 THEN '12 to 18 Weeks'
WHEN DATEDIFF(WEEK, GETDATE(), @DateValue) BETWEEN 18 AND 24 THEN '18 to 24 Weeks'
WHEN DATEDIFF(WEEK, GETDATE(), @DateValue) > 24 THEN 'More than 24 Weeks'
END
, YearRangeText = CASE WHEN DATEDIFF(YEAR, GETDATE(), @DateValue) = 0 THEN 'This Year'
WHEN DATEDIFF(YEAR, GETDATE(), @DateValue) = 1 THEN 'Next Year'
WHEN DATEDIFF(YEAR, GETDATE(), @DateValue) = -1 THEN 'Last Year'
WHEN DATEDIFF(YEAR, GETDATE(), @DateValue) BETWEEN -3 AND -2 THEN '2 to 3 Years Ago'
WHEN DATEDIFF(YEAR, GETDATE(), @DateValue) BETWEEN -5 AND -3 THEN '3 to 5 Years Ago'
WHEN DATEDIFF(YEAR, GETDATE(), @DateValue) BETWEEN -7 AND -5 THEN '5 to 7 Years Ago'
WHEN DATEDIFF(YEAR, GETDATE(), @DateValue) BETWEEN -10 AND -7 THEN '7 to 10 Years Ago'
WHEN DATEDIFF(YEAR, GETDATE(), @DateValue) BETWEEN -15 AND -10 THEN '10 to 15 Years Ago'
WHEN DATEDIFF(YEAR, GETDATE(), @DateValue) BETWEEN -20 AND -15 THEN '15 to 20 Years Ago'
WHEN DATEDIFF(YEAR, GETDATE(), @DateValue) < -20 THEN 'Less than 20 Years Ago'
WHEN DATEDIFF(YEAR, GETDATE(), @DateValue) BETWEEN 2 AND 3 THEN '2 to 3 Years'
WHEN DATEDIFF(YEAR, GETDATE(), @DateValue) BETWEEN 3 AND 5 THEN '3 to 6 Years'
WHEN DATEDIFF(YEAR, GETDATE(), @DateValue) BETWEEN 5 AND 7 THEN '6 to 9 Years'
WHEN DATEDIFF(YEAR, GETDATE(), @DateValue) BETWEEN 7 AND 10 THEN '6 to 12 Years'
WHEN DATEDIFF(YEAR, GETDATE(), @DateValue) BETWEEN 10 AND 15 THEN '10 to 18 Years'
WHEN DATEDIFF(YEAR, GETDATE(), @DateValue) BETWEEN 15 AND 20 THEN '15 to 20 Years'
WHEN DATEDIFF(YEAR, GETDATE(), @DateValue) > 20 THEN 'More than 20 Years'
END
, FinanceAgingText = CASE WHEN DATEDIFF(DAY, GETDATE(), @DateValue) BETWEEN -30 AND 0 THEN '0 - 30 Days'
WHEN DATEDIFF(DAY, GETDATE(), @DateValue) BETWEEN -60 AND -31 THEN '31 - 60 Days'
WHEN DATEDIFF(DAY, GETDATE(), @DateValue) BETWEEN -90 AND -61 THEN '61 - 90 Days'
WHEN DATEDIFF(DAY, GETDATE(), @DateValue) < -90 THEN 'Over 90 Days'
ELSE ''
END
, DATEDIFF(MONTH, GETDATE(), @DateValue) DateValueMonthKey
, DATEDIFF(DAY, GETDATE(), @DateValue) DateValueDayKey
, @IntValue IntValueKey
)
GO