BIvwAuditMDTData

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

SUB_ID

int

√

null

CARE_ID

int

√

null

Date/time moved from MDT

datetime

16,3

null

Date of MDT moved to

smalldatetime

√

null

MDT type moved to

varchar

156

√

null

MDT type moved from

varchar

156

√

null

Username

nvarchar

512

null

Reason moved

varchar

√

null

Date record removed from MDT

smalldatetime

√

null

View Definition

CREATE VIEW [dbo].[BIvwAuditMDTData]
		AS
			SELECT
			subSiteTo.SUB_ID,
			mdt.CARE_ID,
			moves.MoveDateTime AS [Date/time moved from MDT],
			meetingTo.MDT_DATE AS [Date of MDT moved to],
			typeFrom.MEETING_TYPE_DESC 
			+ CASE WHEN ISNULL(subSiteTo.SUB_ID,-1) <> -1 AND ISNULL(subSiteTo.SUB_ID,-1) > 13 THEN ' - ' + subSiteTo.SUB_DESC ELSE '' END +
			+ CASE WHEN ISNULL(meetTo.[LOCATION],'') <> '' THEN ' - ' + meetTo.[LOCATION] ELSE '' END
			AS [MDT type moved to],
			typeTo.MEETING_TYPE_DESC
			+ CASE WHEN ISNULL(subSiteFrom.SUB_ID,-1) <> -1 AND ISNULL(subSiteFrom.SUB_ID,-1) > 13 THEN ' - ' + subSiteFrom.SUB_DESC ELSE '' END +
			+ CASE WHEN ISNULL(meetFrom.[LOCATION],'') <> '' THEN ' - ' + meetFrom.[LOCATION] ELSE '' END
			AS [MDT type moved from],
			moves.UserName AS [Username],
			CASE
				WHEN reason.MDTMoveReasonDesc = 'Other' AND RTRIM(LTRIM(moves.MDTMoveReasonOther)) IS NOT NULL
				THEN reason.MDTMoveReasonDesc + ' - ' + moves.MDTMoveReasonOther
				ELSE reason.MDTMoveReasonDesc
			END AS [Reason moved],
			aud.ACTION_DATE AS [Date record removed from MDT]
			FROM tblMDTMoves moves
			INNER JOIN tblMDT_LIST mdt ON moves.MDT_ID = mdt.MDT_ID
			--from
			INNER JOIN tblMDT_LIST meetingFrom ON moves.MeetingIDFrom = meetingFrom.MDT_ID
			INNER JOIN tblMDT_MEETINGS meetFrom ON meetingFrom.MEETING_ID = meetFrom.MEETING_ID
			INNER JOIN ltblMDT_MEETING_TYPE typeFrom ON meetFrom.MEETING_TYPE_ID = typeFrom.MEETING_TYPE_ID
			LEFT JOIN ltblCANCER_SUB_SITE subSiteFrom ON meetFrom.SUB_SITE = subSiteFrom.SUB_ID
			--to
			INNER JOIN tblMDT_LIST meetingTo ON moves.MeetingIDTo = meetingTo.MDT_ID
			INNER JOIN tblMDT_MEETINGS meetTo ON meetingTo.MEETING_ID = meetTo.MEETING_ID
			INNER JOIN ltblMDT_MEETING_TYPE typeTo ON meetTo.MEETING_TYPE_ID = typeTo.MEETING_TYPE_ID
			LEFT JOIN ltblCANCER_SUB_SITE subSiteTo ON meetTo.SUB_SITE = subSiteTo.SUB_ID
			--
			INNER JOIN ltblMDTMoveReason reason ON moves.MDTMoveReasonID = reason.MDTMoveReasonID
			LEFT JOIN tblAUDIT aud ON mdt.CARE_ID = aud.CARE_ID AND 
			moves.MeetingIDTo = aud.RECORD_ID AND aud.ACTION_TYPE = 'REMOVE'

Columns

View Definition

Possibly Referenced Tables/Views