DateOfCases_vw

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

PatientID

int

4

null

Patient reference - Links to Patient table

AdmitID

int

4

null

Admission reference - Links to PatientAdministration table

DateType

nvarchar

1073741823

√

null

Date type - Type or category of the date

DateValue

datetime

16,3

√

null

Date value - Actual date value for this type

Relationships

Close relationships within degrees of separation

One

View Definition

CREATE VIEW DateOfCases_vw AS

SELECT p.PatientID, pa.AdmitID, DateType = 'Date Of Birth', DateValue = p.DateOfBirth
FROM dbo.Patient p INNER JOIN dbo.PatientAdministration pa ON p.PatientID = pa.PatientID 
UNION
SELECT p.PatientID, pa.AdmitID, DateType = 'Date Aging Out', DateValue = pa.DateAgingOut
FROM dbo.Patient p INNER JOIN dbo.PatientAdministration pa ON p.PatientID = pa.PatientID 
UNION
SELECT p.PatientID, pa.AdmitID, DateType = 'Referral Date', DateValue = (SELECT MAX(ReferralDate) FROM dbo.PatientAdministrationCustom WHERE fkAdmitID = pa.AdmitID)
FROM dbo.Patient p INNER JOIN dbo.PatientAdministration pa ON p.PatientID = pa.PatientID INNER JOIN dbo.PatientAdministrationCustom pc ON pa.AdmitID = pc.fkAdmitID WHERE pc.ReferralDate IS NOT null
UNION
SELECT p.PatientID, pa.AdmitID, DateType = pc.DateType, DateValue = pc.DateValue
FROM dbo.Patient p INNER JOIN dbo.PatientAdministration pa ON p.PatientID = pa.PatientID 
LEFT JOIN dbo.PatientAdministrationCustom pc ON pa.AdmitID = pc.fkAdmitID WHERE pc.DateValue IS NOT NULL

Possibly Referenced Tables/Views

DateOfCases_vw

Description

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views