TVF_WeekNumbers

The list of table valued functions used by Guildford College Reports and any anyone else wishes to share
Post Reply
User avatar
robinwilson16
Site Admin
Posts: 184
Joined: Sun Oct 16, 2016 6:46 pm
Forename: Robin
Surname: Wilson
College Name: FEA
Position: MIS Consultant
LinkedIn Profile: https://www.linkedin.com/in/robinwilson16

TVF_WeekNumbers

Post by robinwilson16 » Thu Mar 02, 2017 10:50 pm

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
)

Post Reply

Who is online

Users browsing this forum: No registered users and 2 guests