Search found 177 matches

by robinwilson16
Thu Mar 02, 2017 11:13 pm
Forum: Table Valued Functions
Topic: TVF_LearnerMainAim_PRG
Replies: 0
Views: 175

TVF_LearnerMainAim_PRG

Returns a learner's main aim at the programme level Will always return a single line for each learner, useful for learner lists including main course information CREATE FUNCTION [dbo].[TVF_LearnerMainAim_PRG] (@Session VARCHAR(10), @StudentRef VARCHAR(20)) RETURNS TABLE AS RETURN SELECT TOP 1 Value ...
by robinwilson16
Thu Mar 02, 2017 11:12 pm
Forum: Table Valued Functions
Topic: TVF_LearnerMainAim_GRP
Replies: 0
Views: 180

TVF_LearnerMainAim_GRP

Returns a learner's main aim at the group level Will always return a single line for each learner, useful for learner lists including main course information CREATE FUNCTION [dbo].[TVF_LearnerMainAim_GRP] (@Session VARCHAR(10), @StudentRef VARCHAR(20)) RETURNS TABLE AS RETURN SELECT TOP 1 Value = ME...
by robinwilson16
Thu Mar 02, 2017 11:09 pm
Forum: Table Valued Functions
Topic: TVF_LearnerLatestEmpStat
Replies: 0
Views: 183

TVF_LearnerLatestEmpStat

Returns the latest learner employment status for the learner - this is stored in two places so these have been merged when trying to find the most recent CREATE FUNCTION [dbo].[TVF_LearnerLatestEmpStat] (@Session VARCHAR(10), @StudentRef VARCHAR(20)) RETURNS TABLE AS RETURN SELECT TOP 1 StudentRef, ...
by robinwilson16
Thu Mar 02, 2017 11:03 pm
Forum: Table Valued Functions
Topic: TVF_LearnerEarliestAttendDate_Q
Replies: 0
Views: 880

TVF_LearnerEarliestAttendDate_Q

Returns the earliest date a learner attended the qualification CREATE FUNCTION [dbo].[TVF_LearnerEarliestAttendDate_Q] ( @Session VARCHAR(10), @StudentRef VARCHAR(20), @QualID numeric(16, 0) ) RETURNS TABLE AS RETURN SELECT Value = MIN ( RM.rm_date ) FROM ulive.dbo.capd_module Q INNER JOIN ulive.dbo...
by robinwilson16
Thu Mar 02, 2017 11:01 pm
Forum: Table Valued Functions
Topic: TVF_LearnerLatestAttendDate_Q
Replies: 0
Views: 972

TVF_LearnerLatestAttendDate_Q

Returns the last date a learner attended the qualification CREATE FUNCTION [dbo].[TVF_LearnerLatestAttendDate_Q] ( @Session VARCHAR(10), @StudentRef VARCHAR(20), @QualID numeric(16, 0) ) RETURNS TABLE AS RETURN SELECT Value = MAX ( RM.rm_date ) FROM ulive.dbo.capd_module Q INNER JOIN ulive.dbo.capd_...
by robinwilson16
Thu Mar 02, 2017 10:59 pm
Forum: Table Valued Functions
Topic: TVF_GetCodeRef
Replies: 0
Views: 199

TVF_GetCodeRef

Returns the code reference for the selected lookup

Code: Select all

CREATE FUNCTION [dbo].[TVF_GetCodeRef](@Code VARCHAR(10),@Domain VARCHAR(25))
RETURNS TABLE

AS

RETURN 
	SELECT
		Value = VC.vc_ref
	FROM 
		ulive.dbo.caps_valid_codes VC
	WHERE     
		VC.vc_domain = @Domain 
		AND vc_code = @Code
by robinwilson16
Thu Mar 02, 2017 10:58 pm
Forum: Table Valued Functions
Topic: TVF_FundingModel
Replies: 0
Views: 176

TVF_FundingModel

Returns the funding model of the selected course - note that your setup may differ so you may need to adapt CREATE FUNCTION [dbo].[TVF_FundingModel] ( @Session VARCHAR(7), @EnrolID numeric(16,0) ) RETURNS TABLE AS RETURN /* DECLARE @Session VARCHAR(7) = '2016-17' DECLARE @EnrolID numeric(16, 0) = 72...
by robinwilson16
Thu Mar 02, 2017 10:55 pm
Forum: Table Valued Functions
Topic: TVF_CourseFees
Replies: 0
Views: 195

TVF_CourseFees

Returns the fees for the selected course - note that your setup/coding may differ from what is below CREATE FUNCTION [dbo].[TVF_CourseFees] ( @Session VARCHAR(10), @YearID numeric(16, 0), @FundType VARCHAR(10) ) RETURNS TABLE AS RETURN /* DECLARE @Session VARCHAR(7) = '2015-16' DECLARE @YearID numer...
by robinwilson16
Thu Mar 02, 2017 10:53 pm
Forum: Table Valued Functions
Topic: TVF_CountAsStart
Replies: 0
Views: 1025

TVF_CountAsStart

Tells you whether the enrolment has met the eligibility period and counts as a start CREATE FUNCTION [dbo].[TVF_CountAsStart] (@Session VARCHAR(10), @EnrolID numeric(16, 0)) RETURNS TABLE AS RETURN SELECT Value = CASE WHEN MEQ.e_status IN ( '0', '4', '6', '9' ) THEN 'N' WHEN EIQ.ei_enrolmentisr <> -...
by robinwilson16
Thu Mar 02, 2017 10:50 pm
Forum: Table Valued Functions
Topic: TVF_WeekNumbers
Replies: 0
Views: 1085

TVF_WeekNumbers

Lists all the week numbers and dates within the selected year as defined in UNIT-e Settings CREATE FUNCTION [dbo].[TVF_WeekNumbers] ( @Year Varchar(4) ) RETURNS TABLE AS RETURN (-- declare @Year varchar(4) = '2016'; WITH cte as ( SELECT WeekNum = 1, StartDate = DATEADD(wk, DATEDIFF(wk, 6, @Year + '-...