vwMDTs

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

ParentMeetingID

int

null

ChildMeetingID

int

√

null

MeetingTypeID

int

√

null

BaseMeetingTypeID

int

null

MDTDate

smalldatetime

√

null

CareID

int

√

null

MeetingHasBeenFinalised

int

√

null

MeetingHasPatients

int

null

ProFormaDiscussedOrFinalised

int

√

null

View Definition

CREATE VIEW [dbo].[vwMDTs]
WITH SCHEMABINDING
AS

WITH MainMDTData AS
(SELECT MDT_ID AS MDTID, MEETING_ID AS MeetingID, MDT_DATE As MDTDate, CARE_ID AS CareID, DONE AS Done, OTHER_SITE AS OtherSite FROM [dbo].[tblMDT_LIST]),
ParentMDTMeetings AS
(SELECT MDTID AS ParentMeetingID, MeetingID AS MeetingTypeID, MDTDate FROM MainMDTData WHERE CareID IS NULL),
ChildMDTMeetings AS
(SELECT MDTID AS ChildMeetingID, MeetingID, CareID, CASE WHEN ISNULL(Done, 0) = 2 THEN 1 ELSE 0 END AS Finalised, OtherSite FROM MainMDTData WHERE CareID IS NOT NULL),
MDTMeetings AS
(SELECT pd.ParentMeetingID, cd.ChildMeetingID, pd.MeetingTypeID, pd.MDTDate, cd.CareID, cd.Finalised, cd.OtherSite FROM ParentMDTMeetings pd LEFT JOIN ChildMDTMeetings cd ON pd.ParentMeetingID = cd.MeetingID),
UnfinalisedMDTMeetings AS
(SELECT DISTINCT ParentMeetingID, ISNULL(MIN(Finalised), 0) AS Finalised FROM MDTMeetings GROUP BY ParentMeetingID),
MDTMeetingsWithAttendees AS
(SELECT ParentMeetingID, COUNT(ChildMeetingID) AS AttendeeCount FROM MDTMeetings GROUP BY ParentMeetingID)

SELECT DISTINCT m.ParentMeetingID, m.ChildMeetingID, m.MeetingTypeID, mm.MEETING_TYPE_ID AS BaseMeetingTypeID, m.MDTDate, m.CareID, 
u.Finalised AS MeetingHasBeenFinalised,
CASE WHEN w.AttendeeCount = 0 THEN 0 ELSE 1 END AS MeetingHasPatients
,CASE 
	WHEN m.Finalised = 1 
	THEN m.Finalised 
	WHEN m.Finalised = 0
	THEN
		CASE 
			WHEN m.OtherSite = 'Brain' 
			THEN CASE WHEN brain.UPDATED = 'Yes' THEN 1 ELSE 0 END
			WHEN m.OtherSite = 'Breast'
			THEN CASE WHEN breast.UPDATED = 'Yes' THEN 1 ELSE 0 END
			WHEN m.OtherSite = 'Colorectal'
			THEN CASE WHEN colo.UPDATED = 'Yes' THEN 1 ELSE 0 END
			WHEN m.OtherSite = 'CUP'
			THEN CASE WHEN cup.UPDATED = 'Yes' THEN 1 ELSE 0 END
			WHEN m.OtherSite = 'Gynaecology'
			THEN CASE WHEN gynae.UPDATED = 'Yes' THEN 1 ELSE 0 END
			WHEN m.OtherSite = 'Haematology'
			THEN CASE WHEN haem.UPDATED = 'Yes' THEN 1 ELSE 0 END
			WHEN m.OtherSite = 'Head and Neck'
			THEN CASE WHEN hn.UPDATED = 'Yes' THEN 1 ELSE 0 END
			WHEN m.OtherSite = 'Lung'
			THEN CASE WHEN lung.UPDATED = 'Yes' THEN 1 ELSE 0 END
			WHEN m.OtherSite = 'Other'
			THEN CASE WHEN other.UPDATED = 'Yes' THEN 1 ELSE 0 END
			WHEN m.OtherSite = 'Paediatric'
			THEN CASE WHEN paed.UPDATED = 'Yes' THEN 1 ELSE 0 END
			WHEN m.OtherSite = 'Sarcoma'
			THEN CASE WHEN sarc.UPDATED = 'Yes' THEN 1 ELSE 0 END
			WHEN m.OtherSite = 'Skin'
			THEN CASE WHEN skin.UPDATED = 'Yes' THEN 1 ELSE 0 END
			WHEN m.OtherSite = 'Upper GI'
			THEN CASE WHEN ugi.UPDATED = 'Yes' THEN 1 ELSE 0 END
			WHEN m.OtherSite = 'Urology'
			THEN CASE WHEN uro.UPDATED = 'Yes' THEN 1 ELSE 0 END
			ELSE 0
		END
	ELSE 0
END AS ProFormaDiscussedOrFinalised
FROM MDTMeetings m 
LEFT JOIN UnfinalisedMDTMeetings u ON m.ParentMeetingID = u.ParentMeetingID
LEFT JOIN MDTMeetingsWithAttendees w ON m.ParentMeetingID = w.ParentMeetingID
JOIN [dbo].[tblMDT_MEETINGS] mm ON m.MeetingTypeID = mm.MEETING_ID
LEFT JOIN [dbo].[tblBRAIN_MDT] brain ON m.OtherSite = 'Brain' AND CAST(m.ChildMeetingID AS varchar(30)) = brain.MDT_ID
LEFT JOIN [dbo].[tblBREAST_MDT] breast ON m.OtherSite = 'Breast' AND CAST(m.ChildMeetingID AS varchar(30)) = breast.MDT_ID
LEFT JOIN [dbo].[tblCOLORECTAL_MDT] colo ON m.OtherSite = 'Colorectal' AND CAST(m.ChildMeetingID AS varchar(30)) = colo.MDT_ID
LEFT JOIN [dbo].[tblCUP_MDT] cup ON m.OtherSite = 'CUP' AND CAST(m.ChildMeetingID AS varchar(30)) = cup.MDT_ID
LEFT JOIN [dbo].[tblGYNAECOLOGY_MDT] gynae ON m.OtherSite = 'Gynaecology' AND CAST(m.ChildMeetingID AS varchar(30)) = gynae.MDT_ID
LEFT JOIN [dbo].[tblHAEMATOLOGY_MDT] haem ON m.OtherSite = 'Haematology' AND CAST(m.ChildMeetingID AS varchar(30)) = haem.MDT_ID
LEFT JOIN [dbo].[tblHEAD_NECK_MDT] hn ON m.OtherSite = 'Head and Neck' AND CAST(m.ChildMeetingID AS varchar(30)) = hn.MDT_ID
LEFT JOIN [dbo].[tblLUNG_MDT] lung ON m.OtherSite = 'Lung' AND CAST(m.ChildMeetingID AS varchar(30)) = lung.MDT_ID
LEFT JOIN [dbo].[tblOTHER_MDT] other ON m.OtherSite = 'Other' AND CAST(m.ChildMeetingID AS varchar(30)) = other.MDT_ID
LEFT JOIN [dbo].[tblPAEDIATRIC_MDT] paed ON m.OtherSite = 'Paediatric' AND CAST(m.ChildMeetingID AS varchar(30)) = paed.MDT_ID
LEFT JOIN [dbo].[tblSARCOMA_MDT] sarc ON m.OtherSite = 'Sarcoma' AND CAST(m.ChildMeetingID AS varchar(30)) = sarc.MDT_ID
LEFT JOIN [dbo].[tblSKIN_MDT] skin ON m.OtherSite = 'Skin' AND CAST(m.ChildMeetingID AS varchar(30)) = skin.MDT_ID
LEFT JOIN [dbo].[tblUGI_MDT] ugi ON m.OtherSite = 'Upper GI' AND CAST(m.ChildMeetingID AS varchar(30)) = ugi.MDT_ID
LEFT JOIN [dbo].[tblUROLOGY_MDT] uro ON m.OtherSite = 'Urology' AND CAST(m.ChildMeetingID AS varchar(30)) = uro.MDT_ID

Columns

View Definition

Possibly Referenced Tables/Views