TVF_CourseFees

The list of table valued functions used by Guildford College Reports and any anyone else wishes to share
Post Reply
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

TVF_CourseFees

Post by robinwilson16 » Thu Mar 02, 2017 10:55 pm

Returns the fees for the selected course - note that your setup/coding may differ from what is below

Code: Select all

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 numeric(16, 0) = 727000014989419
DECLARE @FundType VARCHAR(10) = 'S'
*/

SELECT
	Tuition = 
		SUM (
			CASE
				WHEN @FundType = '16-18' THEN
					CASE
						WHEN 
							MCP.mc_code1 = 'L'
							AND FEET.vc_ref = 'TU' 
							AND FEE.f_feeband = 'STN' 
							AND FEE.f_type = 'LTUI'
								THEN 0
						ELSE 0
					END

				WHEN @FundType = '19-23' THEN
					CASE
						WHEN PRG.m_concessioncategory = 'FE2' THEN --Level 2
							CASE
								WHEN 
									MCP.mc_code1 = 'L'
									AND FEET.vc_ref = 'TU' 
									AND FEE.f_feeband = 'STN' 
									AND FEE.f_type = 'LTUI'
										THEN FEE.f_amount / 2
								ELSE 0
							END

						WHEN PRG.m_concessioncategory = 'FE' THEN --Level 3
							CASE
								WHEN 
									MCP.mc_code1 = 'L'
									AND FEET.vc_ref = 'TU' 
									AND FEE.f_feeband = 'UNF' 
									AND FEE.f_type = 'LTUI'
										THEN FEE.f_amount
								ELSE 0
							END

						ELSE 0
					END

				WHEN @FundType = '24' THEN
					CASE
						WHEN PRG.m_concessioncategory = 'FE2' THEN --Level 2
							CASE
								WHEN 
									MCP.mc_code1 = 'L'
									AND FEET.vc_ref = 'TU' 
									AND FEE.f_feeband = 'STN' 
									AND FEE.f_type = 'LTUI'
										THEN FEE.f_amount / 2
								ELSE 0
							END

						WHEN PRG.m_concessioncategory = 'FE' THEN --Level 3
							CASE
								WHEN 
									MCP.mc_code1 = 'L'
									AND PRG.m_concessioncategory = 'FE'
									AND FEET.vc_ref = 'TU' 
									AND FEE.f_feeband = 'UNF' 
									AND FEE.f_type = 'LTUI'
										THEN FEE.f_amount
								ELSE 0
							END

						ELSE 0
					END

				WHEN @FundType = 'HE' THEN
					CASE
						WHEN 
							MCP.mc_code1 = 'H'
							AND FEET.vc_ref = 'TU' 
							AND FEE.f_feeband = 'STN' 
							AND FEE.f_type = 'HTUI'
								THEN FEE.f_amount
						ELSE 0
					END

				WHEN @FundType = 'FC' THEN
					CASE
						WHEN 
							MCP.mc_code1 = 'C'
							AND FEET.vc_ref = 'TU' 
							AND FEE.f_feeband = 'STN' 
							AND FEE.f_type = 'CTUI'
								THEN FEE.f_amount
						ELSE 0
					END

				WHEN @FundType = 'SCHOOL' THEN
					CASE
						WHEN 
							MCP.mc_code1 = 'S'
							AND FEET.vc_ref = 'TU' 
							AND FEE.f_feeband = 'STN' 
							AND FEE.f_type = 'STUI'
								THEN FEE.f_amount
						ELSE 0
					END

				WHEN @FundType = 'INT' THEN
					CASE
						WHEN 
							MCP.mc_code1 = 'C'
							AND FEET.vc_ref = 'TU' 
							AND FEE.f_feeband = 'EFL' 
							AND FEE.f_type LIKE 'IEF%'
								THEN FEE.f_amount
						ELSE 0
					END

				ELSE 0
			END
		),


	Exam = 
		SUM (
			CASE
				WHEN @FundType = '16-18' THEN
					CASE
						WHEN 
							MCP.mc_code1 = 'L'
							AND FEET.vc_ref = 'AB' 
							AND FEE.f_feeband = 'STN' 
							AND FEE.f_type = 'LEXM'
								THEN 0
						ELSE 0
					END
				
				WHEN @FundType = '19-23' THEN
					CASE
						WHEN PRG.m_concessioncategory = 'FE2' THEN --Level 2
							CASE
								WHEN 
									MCP.mc_code1 = 'L'
									AND FEET.vc_ref = 'AB' 
									AND FEE.f_feeband = 'STN' 
									AND FEE.f_type = 'LEXM'
										THEN FEE.f_amount
								ELSE 0
							END

						WHEN PRG.m_concessioncategory = 'FE' THEN --Level 3
							CASE
								WHEN 
									MCP.mc_code1 = 'L'
									AND FEET.vc_ref = 'AB' 
									AND FEE.f_feeband = 'STN' 
									AND FEE.f_type = 'LEXM'
										THEN FEE.f_amount
								ELSE 0
							END

						ELSE 0
					END

				WHEN @FundType = '24' THEN
					CASE
						WHEN PRG.m_concessioncategory = 'FE2' THEN --Level 2
							CASE
								WHEN 
									MCP.mc_code1 = 'L'
									AND FEET.vc_ref = 'AB' 
									AND FEE.f_feeband = 'STN' 
									AND FEE.f_type = 'LEXM'
										THEN FEE.f_amount
								ELSE 0
							END

						WHEN PRG.m_concessioncategory = 'FE' THEN --Level 3
							CASE
								WHEN 
									MCP.mc_code1 = 'L'
									AND FEET.vc_ref = 'AB' 
									AND FEE.f_feeband = 'UNF' 
									AND FEE.f_type = 'LEXM'
										THEN FEE.f_amount
								ELSE 0
							END

						ELSE 0
					END

				WHEN @FundType = 'HE' THEN
					CASE
						WHEN 
							MCP.mc_code1 = 'H'
							AND FEET.vc_ref = 'AB' 
							AND FEE.f_feeband = 'STN' 
							AND FEE.f_type = 'HEXM'
								THEN FEE.f_amount
						ELSE 0
					END

				WHEN @FundType = 'FC' THEN
					CASE
						WHEN 
							MCP.mc_code1 = 'C'
							AND FEET.vc_ref = 'AB' 
							AND FEE.f_feeband = 'STN' 
							AND FEE.f_type = 'CEXM'
								THEN FEE.f_amount
						ELSE 0
					END

				WHEN @FundType = 'SCHOOL' THEN
					CASE
						WHEN 
							MCP.mc_code1 = 'S'
							AND FEET.vc_ref = 'AB' 
							--AND FEE.f_feeband = '' 
							--AND FEE.f_type = ''
								THEN FEE.f_amount
						ELSE 0
					END

				WHEN @FundType = 'INT' THEN
					CASE
						WHEN 
							MCP.mc_code1 = 'C'
							AND FEET.vc_ref = 'AB' 
							AND FEE.f_feeband = 'EFL' 
							AND FEE.f_type = 'CEXM'
								THEN FEE.f_amount
						ELSE 0
					END

				ELSE 0
			END
		),


	Mandatory = 
		SUM (
			CASE
				WHEN @FundType = '16-18' THEN
					CASE
						WHEN 
							MCP.mc_code1 = 'L'
							AND FEET.vc_ref = 'MA'
							AND FEE.f_feeband = 'STN' 
							AND FEE.f_type LIKE 'CM%'
								THEN 0
						ELSE 0
					END

				WHEN @FundType = '19-23' THEN
					CASE
						WHEN PRG.m_concessioncategory = 'FE2' THEN --Level 2
							CASE
								WHEN 
									MCP.mc_code1 = 'L'
									AND FEET.vc_ref = 'MA'
									AND FEE.f_feeband = 'STN' 
									AND FEE.f_type LIKE 'CM%'
										THEN FEE.f_amount
								ELSE 0
							END

						WHEN PRG.m_concessioncategory = 'FE' THEN --Level 3
							CASE
								WHEN 
									MCP.mc_code1 = 'L'
									AND FEET.vc_ref = 'MA'
									AND FEE.f_feeband = 'STN' 
									AND FEE.f_type LIKE 'CM%'
										THEN FEE.f_amount
								ELSE 0
							END

						ELSE 0
					END

				WHEN @FundType = '24' THEN
					CASE
						WHEN PRG.m_concessioncategory = 'FE2' THEN --Level 2
							CASE
								WHEN 
									MCP.mc_code1 = 'L'
									AND FEET.vc_ref = 'MA'
									AND FEE.f_feeband = 'STN' 
									AND FEE.f_type LIKE 'CM%'
										THEN FEE.f_amount
								ELSE 0
							END

						WHEN PRG.m_concessioncategory = 'FE' THEN --Level 3
							CASE
								WHEN 
									MCP.mc_code1 = 'L'
									AND FEET.vc_ref = 'MA'
									AND FEE.f_feeband = 'UNF' 
									AND FEE.f_type LIKE 'CM%'
										THEN FEE.f_amount
								ELSE 0
							END

						ELSE 0
					END

				WHEN @FundType = 'HE' THEN
					CASE
						WHEN 
							MCP.mc_code1 = 'H'
							AND FEET.vc_ref = 'MA'
							AND FEE.f_feeband = 'STN' 
							AND FEE.f_type LIKE 'CM%'
								THEN FEE.f_amount
						ELSE 0
					END

				WHEN @FundType = 'FC' THEN
					CASE
						WHEN 
							MCP.mc_code1 = 'C'
							AND FEET.vc_ref = 'MA'
							AND FEE.f_feeband = 'STN' 
							AND FEE.f_type LIKE 'CM%'
								THEN FEE.f_amount
						ELSE 0
					END

				WHEN @FundType = 'SCHOOL' THEN
					CASE
						WHEN 
							MCP.mc_code1 = 'S'
							AND FEET.vc_ref = 'MA'
							--AND FEE.f_feeband = '' 
							--AND FEE.f_type LIKE ''
								THEN FEE.f_amount
						ELSE 0
					END

				WHEN @FundType = 'INT' THEN
					CASE
						WHEN 
							MCP.mc_code1 = 'C'
							AND FEET.vc_ref = 'MA' 
							AND FEE.f_feeband = 'EFL' 
							AND FEE.f_type LIKE 'CM%'
								THEN FEE.f_amount
						ELSE 0
					END

				ELSE 0
			END
		),


	CourseRelated = 
		SUM (
			CASE
				WHEN @FundType = '16-18' THEN
					CASE
						WHEN 
							MCP.mc_code1 = 'L'
							AND FEET.vc_ref = 'MA'
							AND FEE.f_feeband = 'STN' 
							AND FEE.f_type LIKE 'RC%'
								THEN FEE.f_amount
						ELSE 0
					END
				
				WHEN @FundType = '19-23' THEN
					CASE
						WHEN PRG.m_concessioncategory = 'FE2' THEN --Level 2
							CASE
								WHEN 
									MCP.mc_code1 = 'L'
									AND FEET.vc_ref = 'MA'
									AND FEE.f_feeband = 'STN' 
									AND FEE.f_type LIKE 'RC%'
										THEN FEE.f_amount
								ELSE 0
							END

						WHEN PRG.m_concessioncategory = 'FE' THEN --Level 3
							CASE
								WHEN 
									MCP.mc_code1 = 'L'
									AND FEET.vc_ref = 'MA'
									AND FEE.f_feeband = 'STN' 
									AND FEE.f_type LIKE 'RC%'
										THEN FEE.f_amount
								ELSE 0
							END

						ELSE 0
					END

				WHEN @FundType = '24' THEN
					CASE
						WHEN PRG.m_concessioncategory = 'FE2' THEN --Level 2
							CASE
								WHEN 
									MCP.mc_code1 = 'L'
									AND FEET.vc_ref = 'MA'
									AND FEE.f_feeband = 'STN' 
									AND FEE.f_type LIKE 'RC%'
										THEN FEE.f_amount
								ELSE 0
							END

						WHEN PRG.m_concessioncategory = 'FE' THEN --Level 3
							CASE
								WHEN 
									MCP.mc_code1 = 'L'
									AND FEET.vc_ref = 'MA'
									AND FEE.f_feeband = 'UNF' 
									AND FEE.f_type LIKE 'RC%'
										THEN FEE.f_amount
								ELSE 0
							END

						ELSE 0
					END

				WHEN @FundType = 'HE' THEN
					CASE
						WHEN 
							MCP.mc_code1 = 'H'
							AND FEET.vc_ref = 'MA'
							AND FEE.f_feeband = 'STN' 
							AND FEE.f_type LIKE 'RC%'
								THEN FEE.f_amount
						ELSE 0
					END

				WHEN @FundType = 'FC' THEN
					CASE
						WHEN 
							MCP.mc_code1 = 'C'
							AND FEET.vc_ref = 'MA'
							AND FEE.f_feeband = 'STN' 
							AND FEE.f_type LIKE 'RC%'
								THEN FEE.f_amount
						ELSE 0
					END

				WHEN @FundType = 'SCHOOL' THEN
					CASE
						WHEN 
							MCP.mc_code1 = 'S'
							AND FEET.vc_ref = 'MA'
							--AND FEE.f_feeband = '' 
							--AND FEE.f_type LIKE ''
								THEN FEE.f_amount
						ELSE 0
					END

				WHEN @FundType = 'INT' THEN
					CASE
						WHEN 
							MCP.mc_code1 = 'C'
							AND FEET.vc_ref = 'MA' 
							AND FEE.f_feeband = 'EFL' 
							AND FEE.f_type LIKE 'RC%'
								THEN FEE.f_amount
						ELSE 0
					END

				ELSE 0
			END
		),


	Advisory = 
		SUM (
			CASE
				WHEN @FundType = '16-18' THEN
					CASE
						WHEN 
							MCP.mc_code1 = 'L'
							AND FEET.vc_ref = 'MA'
							AND FEE.f_feeband = 'STN' 
							AND FEE.f_type LIKE 'VF%'
								THEN FEE.f_amount
						ELSE 0
					END
				
				WHEN @FundType = '19-23' THEN
					CASE
						WHEN PRG.m_concessioncategory = 'FE2' THEN --Level 2
							CASE
								WHEN 
									MCP.mc_code1 = 'L'
									AND FEET.vc_ref = 'MA'
									AND FEE.f_feeband = 'STN' 
									AND FEE.f_type LIKE 'VF%'
										THEN FEE.f_amount
								ELSE 0
							END

						WHEN PRG.m_concessioncategory = 'FE' THEN --Level 3
							CASE
								WHEN 
									MCP.mc_code1 = 'L'
									AND FEET.vc_ref = 'MA'
									AND FEE.f_feeband = 'STN' 
									AND FEE.f_type LIKE 'VF%'
										THEN FEE.f_amount
								ELSE 0
							END

						ELSE 0
					END

				WHEN @FundType = '24' THEN
					CASE
						WHEN PRG.m_concessioncategory = 'FE2' THEN --Level 2
							CASE
								WHEN 
									MCP.mc_code1 = 'L'
									AND FEET.vc_ref = 'MA'
									AND FEE.f_feeband = 'STN' 
									AND FEE.f_type LIKE 'VF%'
										THEN FEE.f_amount
								ELSE 0
							END

						WHEN PRG.m_concessioncategory = 'FE' THEN --Level 3
							CASE
								WHEN 
									MCP.mc_code1 = 'L'
									AND FEET.vc_ref = 'MA'
									AND FEE.f_feeband = 'UNF' 
									AND FEE.f_type LIKE 'VF%'
										THEN FEE.f_amount
								ELSE 0
							END

						ELSE 0
					END

				WHEN @FundType = 'HE' THEN
					CASE
						WHEN 
							MCP.mc_code1 = 'H'
							AND FEET.vc_ref = 'MA'
							AND FEE.f_feeband = 'STN' 
							AND FEE.f_type LIKE 'VF%'
								THEN FEE.f_amount
						ELSE 0
					END

				WHEN @FundType = 'FC' THEN
					CASE
						WHEN 
							MCP.mc_code1 = 'C'
							AND FEET.vc_ref = 'MA'
							AND FEE.f_feeband = 'STN' 
							AND FEE.f_type LIKE 'VF%'
								THEN FEE.f_amount
						ELSE 0
					END

				WHEN @FundType = 'SCHOOL' THEN
					CASE
						WHEN 
							MCP.mc_code1 = 'S'
							AND FEET.vc_ref = 'MA'
							--AND FEE.f_feeband = '' 
							--AND FEE.f_type LIKE ''
								THEN FEE.f_amount
						ELSE 0
					END

				WHEN @FundType = 'INT' THEN
					CASE
						WHEN 
							MCP.mc_code1 = 'C'
							AND FEET.vc_ref = 'MA' 
							AND FEE.f_feeband = 'EFL' 
							AND FEE.f_type LIKE 'VF%'
								THEN FEE.f_amount
						ELSE 0
					END

				ELSE 0
			END
		)
FROM ulive.dbo.capd_module PRG 
INNER JOIN ulive.dbo.capd_modulecustom MCP 
	ON MCP.mc_custommodule = PRG.m_id
INNER JOIN ulive.dbo.capd_offering LNKPQ 
	ON LNKPQ.o_source = PRG.m_id
INNER JOIN ulive.dbo.capd_offering LNKQY 
	ON LNKQY.o_source = LNKPQ.o_destination
INNER JOIN ulive.dbo.capd_module YR 
	ON YR.m_id = LNKQY.o_destination
INNER JOIN ulive.dbo.capd_modulefee MFEE 
	ON MFEE.mf_feemodule = YR.m_id
INNER JOIN ulive.dbo.capd_fee FEE 
	ON FEE.f_id = MFEE.mf_id
INNER JOIN ulive.dbo.caps_valid_codes FEET 
	ON FEET.vc_code = FEE.f_type
	AND FEET.vc_domain = 'feetype'
WHERE
	PRG.m_type = 'P'
	AND PRG.m_start <= LEFT ( @Session, 2 ) + RIGHT ( @Session, 2 ) + '-07-31'
	AND PRG.m_end >= LEFT ( @Session, 4 ) + '-08-01'​​
	AND YR.m_start <= LEFT ( @Session, 2 ) + RIGHT ( @Session, 2 ) + '-07-31'
	AND YR.m_end >= LEFT ( @Session, 4 ) + '-08-01'
	AND YR.m_id = @YearID

Post Reply

Who is online

Users browsing this forum: No registered users and 0 guests