Search found 176 matches

by robinwilson16
Thu Mar 02, 2017 11:57 pm
Forum: Table Valued Functions
Topic: TVF_StudentEnrolmentCreateDate
Replies: 0
Views: 108

TVF_StudentEnrolmentCreateDate

Displays the created date of the specified enrolment using the audit data CREATE FUNCTION [dbo].[TVF_StudentEnrolmentCreateDate] ( @Session VARCHAR(7), @StudentEnrolmentID numeric(16, 0) ) RETURNS TABLE AS RETURN /* DECLARE @Session NVARCHAR(7) = '2015-16' DECLARE @StudentEnrolmentID numeric(16, 0) ...
by robinwilson16
Thu Mar 02, 2017 11:56 pm
Forum: Table Valued Functions
Topic: TVF_StudentApplicationCreateDate
Replies: 0
Views: 104

TVF_StudentApplicationCreateDate

Displays the created date of the specified student application using the audit data CREATE FUNCTION [dbo].[TVF_StudentApplicationCreateDate] ( @Session VARCHAR(7), @StudentApplicationID numeric(16, 0) ) RETURNS TABLE AS RETURN /* DECLARE @Session NVARCHAR(7) = '2015-16' DECLARE @StudentApplicationID...
by robinwilson16
Thu Mar 02, 2017 11:55 pm
Forum: Table Valued Functions
Topic: TVF_StaffDetailsFromWindowsLogin
Replies: 0
Views: 130

TVF_StaffDetailsFromWindowsLogin

Used to determine the role of a member of staff depending on if they are a director of faculty, a head of an area, a lecturer or non-teaching. Used in some C# web applications that customise the view depending on the role of the logged on user using Windows Auth CREATE FUNCTION [dbo].[TVF_StaffDetai...
by robinwilson16
Thu Mar 02, 2017 11:52 pm
Forum: Table Valued Functions
Topic: TVF_SplitStrings
Replies: 0
Views: 123

TVF_SplitStrings

Used to split up a single string of data using a specific delimiter and returns this as multiple rows. Used by the address function CREATE FUNCTION [dbo].[TVF_SplitStrings] ( @List NVARCHAR(MAX), @Delimiter NVARCHAR(255) ) RETURNS TABLE AS RETURN SELECT RowNumber = ROW_NUMBER() OVER (ORDER BY Number...
by robinwilson16
Thu Mar 02, 2017 11:49 pm
Forum: Table Valued Functions
Topic: TVF_SessionCodeYearlyMapping
Replies: 0
Views: 110

TVF_SessionCodeYearlyMapping

Given a year/session and the number of years (can be negative or positive) returns the year/session the specified number of years behind or ahead. Useful for comparing two years of data CREATE FUNCTION [dbo].[TVF_SessionCodeYearlyMapping] ( @Session VARCHAR(7), @Years int ) RETURNS TABLE AS RETURN S...
by robinwilson16
Thu Mar 02, 2017 11:47 pm
Forum: Table Valued Functions
Topic: TVF_RegisterRooms
Replies: 0
Views: 115

TVF_RegisterRooms

Returns all the rooms attached to a register as a single comma separated list CREATE FUNCTION [dbo].[TVF_RegisterRooms] ( @RegisterID numeric(16, 0) ) RETURNS TABLE AS RETURN SELECT Value = STUFF ( ( SELECT DISTINCT ', ' + RM.r_reference FROM ulive.dbo.capd_roomregister RREG2 INNER JOIN ulive.dbo.ca...
by robinwilson16
Thu Mar 02, 2017 11:46 pm
Forum: Table Valued Functions
Topic: TVF_RegisterMainRoom
Replies: 0
Views: 112

TVF_RegisterMainRoom

Returns the main room from a register which is used the most often where more than one room is used CREATE FUNCTION [dbo].[TVF_RegisterMainRoom] (@Session VARCHAR(10), @RegRef VARCHAR(20)) RETURNS TABLE AS RETURN /* DECLARE @RegRef VARCHAR(20) = 'REG018120' */ SELECT TOP 1 Value = ROOM.r_reference F...
by robinwilson16
Thu Mar 02, 2017 11:45 pm
Forum: Table Valued Functions
Topic: TVF_RegisterMainLecturer
Replies: 0
Views: 118

TVF_RegisterMainLecturer

Returns the main lecturer on a register CREATE FUNCTION [dbo].[TVF_RegisterMainLecturer] (@Session VARCHAR(10), @RegRef VARCHAR(20)) RETURNS TABLE AS RETURN /* DECLARE @RegRef VARCHAR(20) = 'REG018120' */ SELECT TOP 1 Value = STF.s_staffreference FROM ulive.dbo.capd_register REG INNER JOIN ulive.dbo...
by robinwilson16
Thu Mar 02, 2017 11:44 pm
Forum: Table Valued Functions
Topic: TVF_ProvisionType
Replies: 0
Views: 142

TVF_ProvisionType

Returns the provision type of the selected learner - to be used in Ofsted reporting for the 4 provision types: 16-18 Study Programmes 19+ Study Programmes 14-16 Provision High Needs Learners CREATE FUNCTION [dbo].[TVF_ProvisionType] ( @Session VARCHAR(7), @StudentRef VARCHAR(12) ) RETURNS TABLE AS R...
by robinwilson16
Thu Mar 02, 2017 11:40 pm
Forum: Table Valued Functions
Topic: TVF_ProAchieveRatesByQual
Replies: 0
Views: 120

TVF_ProAchieveRatesByQual

Returns the retention, pass and achievement rates from ProAchieve for every qualification. It uses OpenQuery as otherwise the LB_GEN_FUN_CalculateRate function cannot be called from a linked database CREATE FUNCTION [dbo].[TVF_ProAchieveRatesByQual] ( @Session VARCHAR(10) ) RETURNS TABLE AS RETURN -...