AGE =

-- Find difference in years and subtract 1 if date is before this years birthday

datediff(yy,@date_of_birth, a.date) -

case when datepart(dy, @date_of_birth) > datepart(dy, a.DATE)

then 1 else 0 end,

SELECT dbo.[CORE_CO-PERSON].[PERSON-REF]AS Person_Reference,

dbo.[CORE_CO-PERSON].[PERSON-TITLE] AS Person_Title,

dbo.[CORE_CO-PERSON].[INITIALS]AS Initials,

dbo.[CORE_CO-PERSON].[FORENAMES] AS Forenames,

dbo.[CORE_CO-PERSON].[SURNAME]AS Surname,

dbo.[IH_RE-TENANCY].[corr-name1]AS Name_On_Tenancy1,

dbo.[IH_RE-TENANCY].[corr-name2]AS Name_On_Tenancy2,

dbo.[CORE_CO-PERSON].[D-O-B] AS DateOfBirth,

DATEDIFF (year,dbo.[CORE_CO-PERSON].[D-O-B],GETDATE())AS Age,

dbo.[CORE_CO-PERSON].[NHI-NO] AS NationalInsurranceNumber,

dbo.[CORE_CO-PERSON].[ORIGIN-CODE]AS EthnicOrigin,

dbo.[CORE_CO-PERSON].[GENDER]AS Gender,

dbo.[IH_RE-TENANCY].[tenancy-ref]AS Tenancy_Referance,

dbo.[IH_RE-TENANCY].[tncy-start]AS Tenancy_StartDate,

dbo.[IH_RE-TNCY-PERSON].[ON-TNCY] AS On_The_Tenancy

FROM dbo.[IH_RE-TENANCY]

INNER JOIN dbo.[IH_RE-TNCY-PERSON]

ON dbo.[IH_RE-TENANCY].[TNCY-SYS-REF] = dbo.[IH_RE-TNCY-PERSON].[TNCY-SYS-REF]

INNER JOIN dbo.[CORE_CO-PERSON]

ON dbo.[IH_RE-TNCY-PERSON].[PERSON-REF] = dbo.[CORE_CO-PERSON].[PERSON-REF]

WHERE dbo.[IH_RE-TENANCY].[tncy-status] = 'CUR'

AND dbo.[IH_RE-TENANCY].[tenancy-ref] NOT LIKE 'FS%'

AND dbo.[IH_RE-TNCY-PERSON].[END-DATE]IS NULL

--Need to only include NULL records for National Insurrance.

--Only need to pull back those that are 16 and over.