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
)