TVF_AddressDetails

The list of table valued functions used by Guildford College Reports and any anyone else wishes to share
Post Reply [phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable
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
[phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable

TVF_AddressDetails

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

Splits the learner address into separate fields using the TVF_SplitStrings split strings function

Code: Select all

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.RowNumber = 1 THEN ADS.Value 
					ELSE NULL 
				END
			),
		AddressLine2 = 
			MIN ( 
				CASE
					WHEN ADS.RowNumber = 2 THEN ADS.Value 
					ELSE NULL 
				END
			),
		AddressLine3 = 
			MIN ( 
				CASE
					WHEN ADS.RowNumber = 3 THEN ADS.Value 
					ELSE NULL 
				END
			),
		AddressLine4 = 
			MIN ( 
				CASE
					WHEN ADS.RowNumber = 4 THEN ADS.Value 
					ELSE NULL 
				END
			),
		AddressLine5 = 
			MIN ( 
				CASE
					WHEN ADS.RowNumber = 5 THEN ADS.Value 
					ELSE NULL 
				END
			),
		PostCode = AD.a_reference
	FROM ulive.dbo.capd_address AD
	CROSS APPLY GC.dbo.TVF_SplitStrings ( AD.a_name, CHAR(10) ) ADS
	WHERE
		AD.a_id = @AddressID
	GROUP BY
		AD.a_reference

Post Reply [phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable
[phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable
[phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable

Who is online

Users browsing this forum: No registered users and 1 guest