TVF_ModuleCodeYearlyMapping

The list of table valued functions used by Guildford College Reports and any anyone else wishes to share
Post Reply [phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable
User avatar
robinwilson16
Site Admin
Posts: 185
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
[phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable

TVF_ModuleCodeYearlyMapping

Post by robinwilson16 » Thu Mar 02, 2017 11:23 pm

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

Post Reply [phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable
[phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable
[phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable

Who is online

Users browsing this forum: No registered users and 0 guests