Page 1 of 1

TVF_ModuleCodeYearlyMapping

Posted: Thu Mar 02, 2017 11:23 pm
by robinwilson16
Used to return a course code as would have been coded in a previous year, useful for yearly comparisons. This coding will be specific for here but you should be able to adapt if part of a course code in the same in both years and the rest includes the session/year

Code: Select all

CREATE FUNCTION [dbo].[TVF_ModuleCodeYearlyMapping] (
	@Session VARCHAR(7), 
	@ModuleID numeric(16, 0),
	@Years int
)
RETURNS TABLE
AS
RETURN

	SELECT
		/*ModuleCode = M.m_reference,
		CourseCode = LEFT ( M.m_reference, LEN ( M.m_reference ) - CHARINDEX ( '-', REVERSE ( M.m_reference ) ) ),
		Instance = RIGHT ( M.m_reference, CHARINDEX ( '-', REVERSE ( M.m_reference ) ) - 1 ),
		NewInstance = 
			CAST ( CAST ( SUBSTRING ( RIGHT ( M.m_reference, CHARINDEX ( '-', REVERSE ( M.m_reference ) ) - 1 ), 1, 2 ) AS int ) + @Years AS VARCHAR(2) )
			+ CAST ( CAST ( SUBSTRING ( RIGHT ( M.m_reference, CHARINDEX ( '-', REVERSE ( M.m_reference ) ) - 1 ), 3, 2 ) AS int ) + @Years AS VARCHAR(2) )
			+ SUBSTRING ( RIGHT ( M.m_reference, CHARINDEX ( '-', REVERSE ( M.m_reference ) ) - 1 ), 5, 2 ),*/
		Value = 
			LEFT ( M.m_reference, LEN ( M.m_reference ) - CHARINDEX ( '-', REVERSE ( M.m_reference ) ) )
			+ '-'
			+ CAST ( CAST ( SUBSTRING ( RIGHT ( M.m_reference, CHARINDEX ( '-', REVERSE ( M.m_reference ) ) - 1 ), 1, 2 ) AS int ) + @Years AS VARCHAR(2) )
			+ CAST ( CAST ( SUBSTRING ( RIGHT ( M.m_reference, CHARINDEX ( '-', REVERSE ( M.m_reference ) ) - 1 ), 3, 2 ) AS int ) + @Years AS VARCHAR(2) )
			+ SUBSTRING ( RIGHT ( M.m_reference, CHARINDEX ( '-', REVERSE ( M.m_reference ) ) - 1 ), 5, 2 )
	FROM ulive.dbo.capd_module M
	WHERE
		M.m_id = @ModuleID
		AND M.m_start <= LEFT ( @Session, 2 ) + RIGHT ( @Session, 2 ) + '-07-31'
		AND M.m_end >= LEFT ( @Session, 4 ) + '-08-01'
		AND CHARINDEX ( '-', M.m_reference ) > 0
		AND CHARINDEX ( '-', REVERSE ( M.m_reference ) ) = 7
		AND ISNUMERIC ( SUBSTRING ( RIGHT ( M.m_reference, CHARINDEX ( '-', REVERSE ( M.m_reference ) ) - 1 ), 1, 2 ) ) = 1
		AND ISNUMERIC ( SUBSTRING ( RIGHT ( M.m_reference, CHARINDEX ( '-', REVERSE ( M.m_reference ) ) - 1 ), 3, 2 ) ) = 1