BIvwPatientDemographics

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

Patient ID

int

null

NHS Number

varchar

null

Hospital Number

varchar

√

null

NHS Number Status

varchar

√

null

Title

varchar

√

null

Forename

varchar

√

null

Surname

varchar

√

null

Full Name

varchar

111

√

null

Date of Birth

datetime

16,3

√

null

Age

int

√

null

Gender

varchar

null

Sexual Orientation

varchar

100

√

null

Surname at Birth

varchar

√

null

Address Line 1

varchar

150

√

null

Address Line 2

varchar

150

√

null

Address Line 3

varchar

150

√

null

Address Line 4

varchar

150

√

null

Address Line 5

varchar

150

√

null

Post Code

varchar

√

null

Day Time Phone No

varchar

√

null

Evening Phone No

varchar

√

null

Ethnicity

varchar

√

null

GP Code

varchar

√

null

GP Name

varchar

100

√

null

GP Address Line 1

varchar

250

√

null

GP Address Line 2

varchar

250

√

null

GP Address Line 3

varchar

250

√

null

GP Address Line 4

varchar

250

√

null

GP Post Code

varchar

√

null

Practice Code

varchar

√

null

CCG

varchar

100

√

null

CCGCode

varchar

√

null

Preferred Name

varchar

√

null

Preferred Language

varchar

√

null

Preferred Contact Method

int

√

null

Religion

varchar

√

null

Method

varchar

√

null

Occupation

varchar

√

null

Marital Status

varchar

100

√

null

Lives Alone

bit

null

Patient At Risk

bit

null

At Risk Details

varchar

2147483647

√

null

NOK Name

varchar

100

√

null

NOK Address Line 1

varchar

150

√

null

NOK Address Line 2

varchar

150

√

null

NOK Address Line 3

varchar

150

√

null

NOK Address Line 4

varchar

150

√

null

NOK Address Line 5

varchar

150

√

null

NOK Post Code

varchar

√

null

NOK Contact

varchar

255

√

null

NOK Relationship

varchar

√

null

Dependants

varchar

2147483647

√

null

Carer 1 Type

varchar

√

null

Carer 1 Name

varchar

100

√

null

Carer 1 Address Line 1

varchar

150

√

null

Carer 1 Address Line 2

varchar

150

√

null

Carer 1 Address Line 3

varchar

150

√

null

Carer 1 Address Line 4

varchar

150

√

null

Carer 1 Address Line 5

varchar

150

√

null

Carer 1 Post Code

varchar

√

null

Carer 1 Contact Details

varchar

255

√

null

Carer 1 Relationship

varchar

√

null

Carer 2 Type

varchar

√

null

Carer 2 Name

varchar

100

√

null

Carer 2 Address Line 1

varchar

150

√

null

Carer 2 Address Line 2

varchar

150

√

null

Carer 2 Address Line 3

varchar

150

√

null

Carer 2 Address Line 4

varchar

150

√

null

Carer 2 Address Line 5

varchar

150

√

null

Carer 2 Post Code

varchar

√

null

Carer 2 Contact Details

varchar

255

√

null

Carer 2 Relationship

varchar

√

null

Patient Status

varchar

√

null

Date of Death

date

√

null

Place of Death

varchar

255

√

null

Cause of Death Identification Method

varchar

√

null

varchar

100

√

null

Cause of Death - Immediate

varchar

201

√

null

Cause of Death - Condition

varchar

201

√

null

Cause of Death - Underlying

varchar

201

√

null

Cause of Death - Significant

varchar

201

√

null

varchar

√

null

Iatrogenic Death

varchar

√

null

Death due to Infection

varchar

√

null

Comments

varchar

255

√

null

Post Mortem Undertaken

varchar

√

null

Post Mortem Details

varchar

255

√

null

View Definition

CREATE VIEW [dbo].[BIvwPatientDemographics]
AS

		WITH NumberedAddresses AS (
		SELECT PatientID,
		ROW_NUMBER() OVER (PARTITION BY PatientID ORDER BY DateOfChange ASC) AS rn,
		Address1,
		Address2,
		Address3,
		Address4,
		Address5,
		Postcode
		FROM dbo.lnkPatientAddress
		join tblAddress a on
			a.ID = lnkPatientAddress.AddressID
		WHERE TypeID = 1 AND DateOfChange <= GETDATE()
		UNION
		SELECT 
		PATIENT_ID as PatientID,
		0 as rn,
		N1_7_ADDRESS_1 as Address1,
		N1_7_ADDRESS_2 as Address2,
		N1_7_ADDRESS_3 as Address3,
		N1_7_ADDRESS_4 as Address4,
		N1_7_ADDRESS_5 as Address5,
		N1_8_POSTCODE as Postcode		
		FROM tblDEMOGRAPHICS
		WHERE coalesce(NULLIF(LTRIM(RTRIM(N1_7_ADDRESS_1)),''),
					   NULLIF(LTRIM(RTRIM(N1_7_ADDRESS_2)),''),
					   NULLIF(LTRIM(RTRIM(N1_7_ADDRESS_3)),''),
					   NULLIF(LTRIM(RTRIM(N1_7_ADDRESS_4)),''),
					   NULLIF(LTRIM(RTRIM(N1_7_ADDRESS_5)),''),
					   NULLIF(LTRIM(RTRIM(N1_8_POSTCODE)),'')) IS NOT NULL
	), OrderedAddresses as(
		SELECT 
		PatientID,
		Address1,
		Address2,
		Address3,
		Address4,
		Address5,
		Postcode,
		ROW_NUMBER() OVER (PARTITION BY PatientID ORDER BY rn DESC) as rn
		FROM NumberedAddresses
	)
	SELECT	dbo.tblDEMOGRAPHICS.PATIENT_ID AS [Patient ID],
			dbo.tblDEMOGRAPHICS.N1_1_NHS_NUMBER AS [NHS Number],
			dbo.tblDEMOGRAPHICS.N1_2_HOSPITAL_NUMBER AS [Hospital Number], 
			dbo.ltblNHS_NUMBER_STATUS.NATIONAL_DESC AS [NHS Number Status],
			dbo.ltblTITLE.TITLE_DESC AS Title, 
			dbo.tblDEMOGRAPHICS.N1_6_FORENAME AS Forename,
			dbo.tblDEMOGRAPHICS.N1_5_SURNAME AS Surname, 
			dbo.tblDEMOGRAPHICS.N1_6_FORENAME + ' ' + dbo.tblDEMOGRAPHICS.N1_5_SURNAME AS [Full Name],
			CAST(N1_10_DATE_BIRTH as datetime) as [Date of Birth],
			DATEDIFF(YY, dbo.tblDEMOGRAPHICS.N1_10_DATE_BIRTH, GETDATE()) AS Age, 
			CASE tblDEMOGRAPHICS.N1_9_SEX WHEN '1' THEN 'Male' WHEN '2' THEN 'Female' WHEN '9' THEN 'Not Specified' ELSE 'Unknown' END AS Gender, 
			pso.PersonSexualOrientationDesc AS [Sexual Orientation],
			dbo.tblDEMOGRAPHICS.N1_14_SURNAME_BIRTH AS [Surname at Birth],
			OrderedAddresses.Address1 AS [Address Line 1],
			OrderedAddresses.Address2 AS [Address Line 2],
			OrderedAddresses.Address3 AS [Address Line 3], 
			OrderedAddresses.Address4 AS [Address Line 4],
			OrderedAddresses.Address5 AS [Address Line 5], 
			OrderedAddresses.Postcode AS [Post Code],
			dbo.tblDEMOGRAPHICS.R_DAY_PHONE AS [Day Time Phone No], 
			dbo.tblDEMOGRAPHICS.R_EVE_PHONE AS [Evening Phone No],
			dbo.ltblETHNIC_CAT.ETHNIC_DESC AS Ethnicity, 
			dbo.GP.Code AS [GP Code],
			dbo.GP.Name AS [GP Name],
			dbo.Practice.AddressLine1 AS [GP Address Line 1], 
			dbo.Practice.AddressLine2 AS [GP Address Line 2],
			dbo.Practice.AddressLine3 AS [GP Address Line 3],
			dbo.Practice.AddressLine4 AS [GP Address Line 4], 
			dbo.Practice.Postcode AS [GP Post Code], 
			dbo.Practice.Code AS [Practice Code],
			ltblNATIONAL_CCG_INFO.[Name] as CCG,
			ltblNATIONAL_CCG_INFO.CCG as CCGCode, 
			dbo.tblDEMOGRAPHICS.PAT_PREF_NAME AS [Preferred Name],
			dbo.tblDEMOGRAPHICS.PAT_PREF_LANGUAGE AS [Preferred Language], 
			dbo.tblDEMOGRAPHICS.PAT_PREF_CONTACT AS [Preferred Contact Method],
			dbo.ltblRELIGION.RELIGION_DESC AS Religion, 
			dbo.ltblPREF_CONTACT.PREFCONTACT_DESC AS Method,
			dbo.tblDEMOGRAPHICS.PAT_OCCUPATION AS Occupation, 
			dbo.ltblMARITAL_STATUS.MARITALSTATUS_DESC AS [Marital Status], 
			dbo.tblDEMOGRAPHICS.PAT_LIVES_ALONE AS [Lives Alone],
			dbo.tblDEMOGRAPHICS.PT_AT_RISK AS [Patient At Risk], 
			dbo.tblDEMOGRAPHICS.REASON_RISK AS [At Risk Details], 
			dbo.tblDEMOGRAPHICS.NOK_NAME AS [NOK Name],
			dbo.tblDEMOGRAPHICS.NOK_ADDRESS_1 AS [NOK Address Line 1], 
			dbo.tblDEMOGRAPHICS.NOK_ADDRESS_2 AS [NOK Address Line 2],
			dbo.tblDEMOGRAPHICS.NOK_ADDRESS_3 AS [NOK Address Line 3], 
			dbo.tblDEMOGRAPHICS.NOK_ADDRESS_4 AS [NOK Address Line 4],
			dbo.tblDEMOGRAPHICS.NOK_ADDRESS_5 AS [NOK Address Line 5], 
			dbo.tblDEMOGRAPHICS.NOK_POSTCODE AS [NOK Post Code],
			dbo.tblDEMOGRAPHICS.NOK_CONTACT AS [NOK Contact], 
			dbo.ltblRELATIONSHIP.RELATION_DESC AS [NOK Relationship],
			dbo.tblDEMOGRAPHICS.PAT_DEPENDANTS AS Dependants, 
			dbo.ltblCARER_TYPE.CARER_DESC AS [Carer 1 Type],
			dbo.tblDEMOGRAPHICS.CARER_NAME AS [Carer 1 Name], 
			dbo.tblDEMOGRAPHICS.CARER_ADDRESS_1 AS [Carer 1 Address Line 1],
			dbo.tblDEMOGRAPHICS.CARER_ADDRESS_2 AS [Carer 1 Address Line 2], 
			dbo.tblDEMOGRAPHICS.CARER_ADDRESS_3 AS [Carer 1 Address Line 3],
			dbo.tblDEMOGRAPHICS.CARER_ADDRESS_4 AS [Carer 1 Address Line 4], 
			dbo.tblDEMOGRAPHICS.CARER_ADDRESS_5 AS [Carer 1 Address Line 5],
			dbo.tblDEMOGRAPHICS.CARER_POSTCODE AS [Carer 1 Post Code], 
			dbo.tblDEMOGRAPHICS.CARER_CONTACT AS [Carer 1 Contact Details],
			ltblRELATIONSHIP2.RELATION_DESC AS [Carer 1 Relationship], 
			tblCARER_TYPE2.CARER_DESC AS [Carer 2 Type],
			dbo.tblDEMOGRAPHICS.CARER2_NAME AS [Carer 2 Name], 
			dbo.tblDEMOGRAPHICS.CARER2_ADDRESS_1 AS [Carer 2 Address Line 1],
			dbo.tblDEMOGRAPHICS.CARER2_ADDRESS_2 AS [Carer 2 Address Line 2], 
			dbo.tblDEMOGRAPHICS.CARER2_ADDRESS_3 AS [Carer 2 Address Line 3],
			dbo.tblDEMOGRAPHICS.CARER2_ADDRESS_4 AS [Carer 2 Address Line 4], 
			dbo.tblDEMOGRAPHICS.CARER2_ADDRESS_5 AS [Carer 2 Address Line 5],
			dbo.tblDEMOGRAPHICS.CARER2_POSTCODE AS [Carer 2 Post Code], 
			dbo.tblDEMOGRAPHICS.CARER2_CONTACT AS [Carer 2 Contact Details],
			ltblRELATIONSHIP3.RELATION_DESC AS [Carer 2 Relationship], 
			CASE tblDEMOGRAPHICS.L_DEATH_STATUS
				WHEN '0' THEN 'Alive'
				WHEN '1' THEN 'Dead'
			END AS [Patient Status],
			dbo.tblDEMOGRAPHICS.N15_1_DATE_DEATH AS [Date of Death],
			dbo.ltblDEATH_LOC.LOCATION_DESC AS [Place of Death], 
			dbo.ltblDEATH_METHOD.METHOD_DESC AS [Cause of Death Identification Method],
			dbo.ltblDEATH_CAUSE.CAUSE_DESC AS [Cancer Related Death], 
			dbo.ltblDIAGNOSIS.DIAG_CODE + '-' + dbo.ltblDIAGNOSIS.DIAG_DESC AS [Cause of Death - Immediate], 
			ltblDIAGNOSIS2.DIAG_CODE + '-' + ltblDIAGNOSIS2.DIAG_DESC AS [Cause of Death - Condition], 
			ltblDIAGNOSIS3.DIAG_CODE + '-' + ltblDIAGNOSIS3.DIAG_DESC AS [Cause of Death - Underlying], 
			ltblDIAGNOSIS4.DIAG_CODE + '-' + ltblDIAGNOSIS4.DIAG_DESC AS [Cause of Death - Significant], 
			CASE tblDEMOGRAPHICS.R_DEATH_TREATMENT
				WHEN 'N' THEN 'No'
				WHEN 'Y' THEN 'Yes'
				WHEN '9' THEN 'Not Known'
			END AS [Was Death Related to Treatment],
			CASE tblDEMOGRAPHICS.L_IATROGENIC_DEATH
				WHEN '1' THEN 'Yes'
			END AS [Iatrogenic Death], 
			CASE tblDEMOGRAPHICS.L_INFECTION_DEATH
				WHEN '1' THEN 'Yes'
			END AS [Death due to Infection], 
			dbo.tblDEMOGRAPHICS.L_DEATH_COMMENTS AS Comments,
			CASE tblDEMOGRAPHICS.R_POST_MORTEM
				WHEN '1' THEN 'Yes'
			END AS [Post Mortem Undertaken],
			dbo.tblDEMOGRAPHICS.R_PM_DETAILS AS [Post Mortem Details]
	FROM	dbo.tblDEMOGRAPHICS
	LEFT JOIN OrderedAddresses
		ON OrderedAddresses.PatientID = tblDEMOGRAPHICS.PATIENT_ID AND rn = 1
	LEFT JOIN dbo.ltblNHS_NUMBER_STATUS
		ON dbo.tblDEMOGRAPHICS.NHS_NUMBER_STATUS = dbo.ltblNHS_NUMBER_STATUS.NATIONAL_CODE
	LEFT JOIN dbo.ltblTITLE
		ON dbo.tblDEMOGRAPHICS.L_TITLE = dbo.ltblTITLE.TITLE_CODE
	LEFT JOIN dbo.ltblETHNIC_CAT
		ON dbo.tblDEMOGRAPHICS.N1_15_ETHNICITY = dbo.ltblETHNIC_CAT.ETHNIC_CODE
	LEFT JOIN dbo.GP
		ON tblDEMOGRAPHICS.RegisteredGP = GP.ID
	LEFT JOIN dbo.Practice
		ON tblDEMOGRAPHICS.RegisteredPractice = Practice.ID
	LEFT JOIN ltblNATIONAL_CCG_PracticeCode
		ON ltblNATIONAL_CCG_PracticeCode.PracticeCode = Practice.Code
	LEFT JOIN ltblNATIONAL_CCG_INFO
		ON ltblNATIONAL_CCG_INFO.CCG = ltblNATIONAL_CCG_PracticeCode.CCG
	LEFT JOIN dbo.ltblRELIGION
		ON dbo.tblDEMOGRAPHICS.RELIGION = dbo.ltblRELIGION.RELIGION_ID
	LEFT JOIN dbo.ltblPREF_CONTACT
		ON dbo.tblDEMOGRAPHICS.PAT_PREF_CONTACT = dbo.ltblPREF_CONTACT.PREFCONTACT_ID
	LEFT JOIN dbo.ltblMARITAL_STATUS
		ON dbo.tblDEMOGRAPHICS.MARITAL_STATUS = dbo.ltblMARITAL_STATUS.MARITALSTATUS_ID
	LEFT JOIN dbo.ltblRELATIONSHIP
		ON dbo.tblDEMOGRAPHICS.NOK_RELATIONSHIP = dbo.ltblRELATIONSHIP.RELATION_ID
	LEFT JOIN dbo.ltblCARER_TYPE
		ON dbo.tblDEMOGRAPHICS.CARER1_TYPE = dbo.ltblCARER_TYPE.CARER_ID
	LEFT JOIN dbo.ltblRELATIONSHIP AS ltblRELATIONSHIP2
		ON dbo.tblDEMOGRAPHICS.CARER_RELATIONSHIP = ltblRELATIONSHIP2.RELATION_ID
	LEFT JOIN dbo.ltblCARER_TYPE AS tblCARER_TYPE2
		ON dbo.tblDEMOGRAPHICS.CARER2_TYPE = tblCARER_TYPE2.CARER_ID
	LEFT JOIN dbo.ltblRELATIONSHIP AS ltblRELATIONSHIP3
		ON dbo.tblDEMOGRAPHICS.CARER2_RELATIONSHIP = ltblRELATIONSHIP3.RELATION_ID
	LEFT JOIN dbo.ltblDEATH_LOC
		ON dbo.tblDEMOGRAPHICS.N15_2_DEATH_LOCATION = dbo.ltblDEATH_LOC.LOCATION_CODE
	LEFT JOIN dbo.ltblDEATH_METHOD
		ON dbo.tblDEMOGRAPHICS.N15_3_DEATH_CAUSE = dbo.ltblDEATH_METHOD.METHOD_CODE
	LEFT JOIN dbo.ltblDEATH_CAUSE
		ON dbo.tblDEMOGRAPHICS.N15_3_DEATH_CAUSE = dbo.ltblDEATH_CAUSE.CAUSE_CODE
	LEFT JOIN dbo.ltblDIAGNOSIS
		ON dbo.tblDEMOGRAPHICS.N15_5_DEATH_CODE_1 = dbo.ltblDIAGNOSIS.DIAG_CODE
	LEFT JOIN dbo.ltblDIAGNOSIS AS ltblDIAGNOSIS2
		ON dbo.tblDEMOGRAPHICS.N15_6_DEATH_CODE_2 = ltblDIAGNOSIS2.DIAG_CODE
	LEFT JOIN dbo.ltblDIAGNOSIS AS ltblDIAGNOSIS3
		ON dbo.tblDEMOGRAPHICS.N15_7_DEATH_CODE_3 = ltblDIAGNOSIS3.DIAG_CODE
	LEFT JOIN dbo.ltblDIAGNOSIS AS ltblDIAGNOSIS4
		ON dbo.tblDEMOGRAPHICS.N15_8_DEATH_CODE_4 = ltblDIAGNOSIS4.DIAG_CODE
	LEFT JOIN ltblPersonSexualOrientation pso ON
		pso.PersonSexualOrientationID = tblDEMOGRAPHICS.PersonSexualOrientation

Columns

View Definition

Possibly Referenced Tables/Views