Search found 176 matches

by robinwilson16
Thu Mar 02, 2017 10:44 pm
Forum: Table Valued Functions
Topic: TVF_AuditStudentApplication
Replies: 0
Views: 57

TVF_AuditStudentApplication

Returns the status of an application at a certain point in time. Useful for accurately reporting on the number of applications on a certain date. Use with caution on large queries as will show your query down - running for all applications here takes about 3.5mins CREATE FUNCTION [dbo].[TVF_AuditStu...
by robinwilson16
Thu Mar 02, 2017 10:41 pm
Forum: Table Valued Functions
Topic: TVF_AgeSept
Replies: 0
Views: 70

TVF_AgeSept

Returns the age of a learner on 31st Aug for the year specified CREATE FUNCTION [dbo].[TVF_AgeSept] ( @Session VARCHAR(10), @StudentRef VARCHAR(20) ) RETURNS TABLE AS RETURN SELECT Value = FLOOR ( DATEDIFF ( DAY, P.p_dob, CAST ( LEFT ( @Session, 4 ) + '-09-01' AS DATETIME2 ) ) / 365.23076923074 ) FR...
by robinwilson16
Thu Mar 02, 2017 10:40 pm
Forum: Table Valued Functions
Topic: TVF_AgeEnrStart
Replies: 0
Views: 32

TVF_AgeEnrStart

Returns the age of a learner when the programme started CREATE FUNCTION [dbo].[TVF_AgeEnrStart] ( @Session VARCHAR(7), @ProgEnrID numeric(16,0), @StudentRef VARCHAR(20) ) RETURNS TABLE AS RETURN --DECLARE @StudentRef varchar(20) = '16001195' SELECT DISTINCT Value = FLOOR ( DATEDIFF ( DAY, P.p_dob, M...
by robinwilson16
Thu Mar 02, 2017 10:39 pm
Forum: Table Valued Functions
Topic: TVF_AgeAppStart
Replies: 0
Views: 35

TVF_AgeAppStart

Returns the age of a learner when the application started - ok as long as learner only has one programme-level application per year CREATE FUNCTION [dbo].[TVF_AgeAppStart] ( @Session VARCHAR(10), @StudentRef VARCHAR(20) ) RETURNS TABLE AS RETURN --DECLARE @Session VARCHAR(7) = '2015-16' --DECLARE @S...
by robinwilson16
Thu Mar 02, 2017 10:36 pm
Forum: Table Valued Functions
Topic: TVF_AddressDetails
Replies: 0
Views: 31

TVF_AddressDetails

Splits the learner address into separate fields using the TVF_SplitStrings split strings function CREATE FUNCTION [dbo].[TVF_AddressDetails] ( @AddressID numeric(16, 0) ) RETURNS TABLE AS RETURN /* DECLARE @AddressID numeric(16, 0) = 727000001678952 */ SELECT AddressLine1 = MIN ( CASE WHEN ADS.RowNu...
by robinwilson16
Thu Mar 02, 2017 10:34 pm
Forum: Table Valued Functions
Topic: TVF_ActivityStaffMainFaculty
Replies: 0
Views: 30

TVF_ActivityStaffMainFaculty

Returns the main faculty a staff member teaches in (some staff teach across multiple faculties) CREATE FUNCTION [dbo].[TVF_ActivityStaffMainFaculty] ( @Session VARCHAR(7), @StaffCode VARCHAR(20) ) RETURNS TABLE AS RETURN --DECLARE @Session VARCHAR(7) = '2015-16' SELECT TOP 1 Value = FAC.d_reference ...
by robinwilson16
Thu Mar 02, 2017 10:32 pm
Forum: Table Valued Functions
Topic: TVF_ActivityStaffFaculties
Replies: 0
Views: 31

TVF_ActivityStaffFaculties

Returns the faculties a staff member teaches in (used for staff utilisation report) CREATE FUNCTION [dbo].[TVF_ActivityStaffFaculties] ( @Session VARCHAR(10), @StaffCode VARCHAR(20) ) RETURNS TABLE AS RETURN --DECLARE @StaffCode VARCHAR(20) = '017518' SELECT Value = STUFF ( ( SELECT DISTINCT ', ' + ...
by robinwilson16
Thu Mar 02, 2017 10:30 pm
Forum: Table Valued Functions
Topic: TVF_ActivityRooms
Replies: 0
Views: 31

TVF_ActivityRooms

Returns the rooms attached to an activity as a single comma separated list CREATE FUNCTION [dbo].[TVF_ActivityRooms] ( @ActivityID numeric(16, 0) ) RETURNS TABLE AS RETURN SELECT /*Used in student timetables - ProMonitor, ProPortal, Moodle & student dashboard*/ Value = STUFF ( ( SELECT DISTINCT ', '...
by robinwilson16
Thu Mar 02, 2017 10:29 pm
Forum: Table Valued Functions
Topic: TVF_ActivityLecturers
Replies: 0
Views: 33

TVF_ActivityLecturers

Returns the staff attached to an activity as a single comma separated list CREATE FUNCTION [dbo].[TVF_ActivityLecturers] ( @ActivityID numeric(16, 0) ) RETURNS TABLE AS RETURN SELECT /*Used in student timetables - ProMonitor, ProPortal, Moodle & student dashboard*/ Value = STUFF ( ( SELECT DISTINCT ...
by robinwilson16
Thu Mar 02, 2017 10:28 pm
Forum: Table Valued Functions
Topic: TVF_AcademicYear
Replies: 0
Views: 34

TVF_AcademicYear

Returns the current academic year/session. Input is a date

Code: Select all

CREATE FUNCTION [dbo].[TVF_AcademicYear] (@CurrentDate Date)
RETURNS TABLE
AS
RETURN

SELECT
	Value = SES.s_name
FROM ulive.dbo.capd_session SES
WHERE
	SES.s_type = 'FY'
	AND SES.s_start <= @CurrentDate
	AND SES.s_end >= @CurrentDate