USE [HL_VA_LOAD]
GO

/****** Object:  View [dbo].[vwDataLoadDaily]    Script Date: 09/23/2013 17:38:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER View [dbo].[vwDataLoadDaily] as

Select 
	AccessionNumber, StudySourceLocation, OrderNumber, ReadingPhysician, ExamType, BodyPart, Modality, PatientID, ReferringPhysician, ProcCode, 
	Priority, Source, 
	ServerReceivedDateTime, StudyDateTime, FinalizeDateTime, 
	ORU, ReportEmailed, ReportFaxed, 
	SignedOnTimeliness,
	0 as TotalCharge,
	OrderCount,
	ExamBilledCount = dbo.fnGetExamBilledCount(SignedOnTimeliness, OrderCount, InterpretedCount, ProcCode, ServerReceivedDateTime),	
	BilledCharge = dbo.fnGetExamBilledCount(SignedOnTimeliness, OrderCount, InterpretedCount, ProcCode, ServerReceivedDateTime) * 
						dbo.fnGetFeePerExam(Priority, SignedOnTimeliness, Modality, ServerReceivedDateTime),					
	-- force it to be after may 2012 for testing
	ExamBilledCountMay1 = dbo.fnGetExamBilledCountMay1(SignedOnTimeliness, OrderCount, InterpretedCount, ProcCode, '05/1/2012'),	
	BilledChargeMay1 = dbo.fnGetExamBilledCount(SignedOnTimeliness, OrderCount, InterpretedCount, ProcCode, '5/1/2012') * 
						dbo.fnGetFeePerExam(Priority, SignedOnTimeliness, Modality, '5/1/2012'),
	InterpretedCount,				
	FollowUp,
	PatientName, PatientBirthDateTime, PatientSex, OrderStatus, 
	ProfessionalRVU, TechnicalRVU, TotalRVU, null as NumberOfImages
from
(
	Select 
		RP.AccessionNumber, StudySourceLocation, OrderNumber, ReadingPhysician, ExamType, BodyPart, Modality, PatientID, ReferringPhysician, 
		ProcCode, Priority, Source, ServerReceivedDateTime, StudyDateTime, FinalizeDateTime, 
		NULL ORU, NULL as ReportEmailed, NULL as ReportFaxed, 
		SignedOnTimeliness = dbo.fnGetSignedOnTimeliness(Priority, PostProcedureDuration),
		isnull(dbo.fnGetCharCount(OrderNumber,'|'),1) as OrderCount,
		InterpretedCount = case when FinalizeDateTime is NULL then 0 else dbo.fnGetInterpretedCount(Followup) end,
		FollowUp,		
		PatientName, PatientBirthDateTime, PatientSex, OrderStatus,
		PostProcedureDuration, ProfessionalRVU, TechnicalRVU, TotalRVU
	from
	(
		Select AccessionNumber, StudySourceLocation, OrderNumber, ReadingPhysician, ExamType, BodyPart, 
			Modality = case when T1.Modality in ('SC','OT') and CPT.Modality is not NULL then CPT.Modality else T1.Modality end, 
			PatientID, ReferringPhysician, ProcCode, Priority, 
			 Source, ServerReceivedDateTime, StudyDateTime , FinalizeDateTime, PostProcedureDuration = datediff(SS,ServerReceivedDateTime,FinalizeDateTime), 
			 PatientName, PatientBirthDateTime, PatientSex, FollowUp, OrderStatus, CPT.ProfessionalRVU, CPT.TechnicalRVU, CPT.TotalRVU
		from
		(	 
			Select distinct AccessionNumber, left(Accessionnumber,3) as StudySourceLocation, OrderNumber, ReadingPhysician, 
				StudyDesc as ExamType, BodyPart, ModalityType as Modality, MRN as PatientID, ReferringPhysician, 
				CPTCode as ProcCode, Priority, 
				 ExamOriginatingSite as Source, ServerDatetime as ServerReceivedDateTime, StudyDateTime, 
				 FinalizeDateTime = case when OrderStatus in ('Report Delivered','Report Signed') then DeliveredDateTime else	NULL end,
				 NULL as PatientName, NULL as PatientBirthDateTime, NULL as PatientSex, 
				 FollowUp = case when FollowUp is NULL or FollowUp = '' or FollowUp = 'None' then '1 - None or Empty' else FollowUp end,
				 OrderStatus
			from 
				DataLoadDaily D				
		)T1
		left join CalendarDate CD on CalendarDateAlterNateKey = cast(ServerReceivedDateTime as date)
		left join HLDWRef.dbo.vwCPTRVU CPT on T1.ProcCode = CPT.CPTCode and CPT.CPTYear = isnull(Year(T1.ServerReceivedDateTime),2012) 
	)RP
)T2





GO