TVF_AddressDetails
Posted: 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