Search found 177 matches

by robinwilson16
Fri Mar 03, 2017 12:00 am
Forum: Table Valued Functions
Topic: TVF_SubscriptionFileName
Replies: 0
Views: 1499

TVF_SubscriptionFileName

Used for generating file paths for SSRS report subscriptions removing all the characters that are not allowed in file paths CREATE FUNCTION [dbo].[TVF_SubscriptionFileName] ( @String NVARCHAR(MAX) ) RETURNS TABLE AS RETURN --DECLARE @String NVARCHAR(MAX) = 'First & Second/3rd' SELECT Value = REPLACE...
by robinwilson16
Thu Mar 02, 2017 11:57 pm
Forum: Table Valued Functions
Topic: TVF_StudentEnrolmentCreateDate
Replies: 0
Views: 1452

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: 1512

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: 1534

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: 1521

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: 1546

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: 1541

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: 1473

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: 1527

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: 1437

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...