TVF_WeekNumbers

[robinwilson16]

Lists all the week numbers and dates within the selected year as defined in UNIT-e Settings

Code: Select all

CREATE FUNCTION [dbo].[TVF_WeekNumbers] (
	@Year Varchar(4)
)
RETURNS TABLE
AS
RETURN

(-- declare @Year varchar(4) = '2016';
	WITH cte as
	(
		SELECT 
			WeekNum = 1,
			StartDate = DATEADD(wk, DATEDIFF(wk, 6, @Year + '-' + SMON.set_value + '-01') + (SWK.set_value), 7),
			EndDate = DATEADD(wk, DATEDIFF(wk, 7, @Year + '-' + SMON.set_value + '-01') + (SWK.set_value + 1), 6),
			TimetablingMonthStart = SMON.set_value,
			TimetablingWeekStart = SWK.set_value
		FROM ulive.dbo.caps_settings SMON
		INNER JOIN ulive.dbo.caps_settings SWK
			ON SWK.set_key = 'Application\Resource Manager\WeekPattern\Week Number Week'
		WHERE
			SMON.set_key = 'Application\Resource Manager\WeekPattern\Week Number Month'

		UNION ALL

		SELECT 
			WeekNum + 1,
			DATEADD(ww, 1, StartDate),
			DATEADD(ww, 1, EndDate),
			TimetablingMonthStart,
			TimetablingWeekStart
		FROM cte
		WHERE
			DATEADD(ww, 1, StartDate)<= DATEADD(wk, -1, DATEADD ( yy, +1, DATEADD(wk, DATEDIFF(wk, 6, @Year + '-' + TimetablingMonthStart + '-01') + (TimetablingWeekStart), 6) ) )
	)
	SELECT
		WeekNum,
		StartDate,
		EndDate
	FROM cte
)