USE [HL_VA_LOAD]
GO
/****** Object:  StoredProcedure [dbo].[LoadExamFlatIncremental]    Script Date: 09/29/2013 21:14:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[LoadExamFlatIncremental] @fullLoad int, @fileId int
as
Begin
SET NOCOUNT ON;

declare @curdt datetime
set @curdt = GETDATE()

declare @today datetime
set @today = cast(cast(@curdt as date) as datetime)

declare @startedOn datetime
declare @listenerGuid nvarchar (100)
declare @status varchar(20) = 'SUCCESS' 
declare @loadTableName varchar(30)
declare @sql varchar(500)

exec LogMessage @fileId,  'SP_UPDATE_DW','Exam Flat Load Begin',@status,@startedOn
set @startedOn = GETDATE()		

-- this is just for safety check, so people don't accidently run it
if @fullLoad <> 1 and @fullLoad <> 747 
Begin
	exec LogMessage @fileId,  'SP_UPDATE_DW','Wrong Parameters so procedure not executed',@status,@startedOn
	return
End

if @fullLoad = 747
Begin
	set @startedOn = GETDATE()
	truncate table HLDWDataVA.dbo.ExamFlat
	exec LogMessage @fileId, 'SP_UPDATE_DW','Tables truncated','SUCCESS',@startedOn
End

set @startedOn = GETDATE()
Select @listenerGuid = L.Guid,
	@loadTableName  = LoadTableName
from 
	DataLoadListener L
	inner join DataLoadProcess P on L.Id = P.ListenerId
	inner join DataLoadFile F on P.Id = F.ProcessId
WHERE 
	F.Id = @fileId
	
--select distinct AccessionNumber into #DailyDataLoadAccNo from dbo.fnDailyDataLoad(@listenerGUID) where AccessionNumber is not null
if(@fullLoad = 747)
	set @sql = 'Select distinct AccessionNumber from FillerOrder'
else
	set @sql = 'Select distinct AccessionNumber from ' + @loadTableName + ' where FileId = ' + cast(@fileId as varchar)

Create Table #DailyDataLoadAccNo(AccessionNumber nvarchar(100))
insert into #DailyDataLoadAccNo
exec(@sql)

if @@error <> 0 set @status = 'FAILURE' else set @status = 'SUCCESS' 
exec LogMessage @fileId,  'SP_UPDATE_DW','Data from DailyDataLoad Loaded into Temp',@status,@startedOn
set @startedOn = GETDATE()

Merge ExamFlat EF
Using 
(
	SELECT AccessionNumber, AETitle, Age, BilledCharge, BodyPart, CallbackAlertCode, CallbackComments, CallbackSupportStaff, ClinicalKey, Collected, Contrast, 
			Cost, CPTCode, CriticalFindingCode, DiscrepancyCode, ExamBilledCount, ExamCode, ExamCount, ExamInterpretedCount, FeePerStudy, FillerOrderKey, 
			FinancialChargeKey, FollowUp, Gender, LANGUAGE, LOINCCd, MaritalStatus, ModalityKey, ModalityLocation, ModalityName, ModalityType, Nationality, 
			NormalFinding, NumberOfChildren, NumberOfImages, NumberOfSeries, NumberOfStudies, OrderCount, OrderLocation, OrderSpecialty, PatientCount, PatientKey, 
			PatientMRN, PatientType, PeerReviewCode, PlacerOrderNumber, Priority, ProfessionalCharge, ProfessionalRVU, ProfessionalRVUAdjusted, Race, RadiationDose
			, SignedOnTimeliness, StudyId, TechnicalCharge, TechnicalRVU, TotalRVU, CallConnectDuration, PreProcedureDuration, InProcedureDuration, 
			PostProcedureDuration, WaitDuration, BilledChargeMay1, ExamBilledCountMay1, CaseNotesAvailable, CPTCodeShortDescription, CPTCodeMediumDescription, 
			CPTCodeLongDescription, AdjustedRequiredFTE
		FROM vwExam
		where AccessionNumber in (Select AccessionNumber from #DailyDataLoadAccNo)
)Source	
ON EF.AccessionNumber = Source.AccessionNumber
WHEN MATCHED THEN
UPDATE SET
	AETitle = Source.AETitle,
	Age = Source.Age,
	BilledCharge = Source.BilledCharge,
	BodyPart = Source.BodyPart,
	CallbackAlertCode = Source.CallbackAlertCode,
	CallbackComments = Source.CallbackComments,
	CallbackSupportStaff = Source.CallbackSupportStaff,
	ClinicalKey = Source.ClinicalKey,
	Collected = Source.Collected,
	Contrast = Source.Contrast,
	Cost = Source.Cost,
	CPTCode = Source.CPTCode,
	CriticalFindingCode = Source.CriticalFindingCode,
	DiscrepancyCode = Source.DiscrepancyCode,
	ExamBilledCount = Source.ExamBilledCount,
	ExamCode = Source.ExamCode,
	ExamCount = Source.ExamCount,
	ExamInterpretedCount = Source.ExamInterpretedCount,
	FeePerStudy = Source.FeePerStudy,
	FillerOrderKey = Source.FillerOrderKey,
	FinancialChargeKey = Source.FinancialChargeKey,
	FollowUp = Source.FollowUp,
	Gender = Source.Gender,
	LANGUAGE = Source.LANGUAGE,
	LOINCCd = Source.LOINCCd,
	MaritalStatus = Source.MaritalStatus,
	ModalityKey = Source.ModalityKey,
	ModalityLocation = Source.ModalityLocation,
	ModalityName = Source.ModalityName,
	ModalityType = Source.ModalityType,
	Nationality = Source.Nationality,
	NormalFinding = Source.NormalFinding,
	NumberOfChildren = Source.NumberOfChildren,
	NumberOfImages = Source.NumberOfImages,
	NumberOfSeries = Source.NumberOfSeries,
	NumberOfStudies = Source.NumberOfStudies,
	OrderCount = Source.OrderCount,
	OrderLocation = Source.OrderLocation,
	OrderSpecialty = Source.OrderSpecialty,
	PatientCount = Source.PatientCount,
	PatientKey = Source.PatientKey,
	PatientMRN = Source.PatientMRN,
	PatientType = Source.PatientType,
	PeerReviewCode = Source.PeerReviewCode,
	PlacerOrderNumber = Source.PlacerOrderNumber,
	Priority = Source.Priority,
	ProfessionalCharge = Source.ProfessionalCharge,
	ProfessionalRVU = Source.ProfessionalRVU,
	ProfessionalRVUAdjusted = Source.ProfessionalRVUAdjusted,
	Race = Source.Race,
	RadiationDose = Source.RadiationDose,
	SignedOnTimeliness = Source.SignedOnTimeliness,
	StudyId = Source.StudyId,
	TechnicalCharge = Source.TechnicalCharge,
	TechnicalRVU = Source.TechnicalRVU,
	TotalRVU = Source.TotalRVU,
	CallConnectDuration = Source.CallConnectDuration,
	PreProcedureDuration = Source.PreProcedureDuration,
	InProcedureDuration = Source.InProcedureDuration,
	PostProcedureDuration = Source.PostProcedureDuration,
	WaitDuration = Source.WaitDuration,
	BilledChargeMay1 = Source.BilledChargeMay1,
	ExamBilledCountMay1 = Source.ExamBilledCountMay1,
	CaseNotesAvailable = Source.CaseNotesAvailable,
	CPTCodeShortDescription = Source.CPTCodeShortDescription,
	CPTCodeMediumDescription = Source.CPTCodeMediumDescription,
	CPTCodeLongDescription = Source.CPTCodeLongDescription,
	AdjustedRequiredFTE = Source.AdjustedRequiredFTE,
	LastUpdatedOn = @curdt
when NOT MATCHED THEN
	insert 
	(
		AccessionNumber, AETitle, Age, BilledCharge, BodyPart, CallbackAlertCode, CallbackComments, CallbackSupportStaff, ClinicalKey, Collected, Contrast, Cost, 
		CPTCode, CriticalFindingCode, DiscrepancyCode, ExamBilledCount, ExamCode, ExamCount, ExamInterpretedCount, FeePerStudy, FillerOrderKey, 
		FinancialChargeKey, FollowUp, Gender, LANGUAGE, LOINCCd, MaritalStatus, ModalityKey, ModalityLocation, ModalityName, ModalityType, Nationality, 
		NormalFinding, NumberOfChildren, NumberOfImages, NumberOfSeries, NumberOfStudies, OrderCount, OrderLocation, OrderSpecialty, PatientCount, PatientKey, 
		PatientMRN, PatientType, PeerReviewCode, PlacerOrderNumber, Priority, ProfessionalCharge, ProfessionalRVU, ProfessionalRVUAdjusted, Race, RadiationDose
		, SignedOnTimeliness, StudyId, TechnicalCharge, TechnicalRVU, TotalRVU, CallConnectDuration, PreProcedureDuration, InProcedureDuration, 
		PostProcedureDuration, WaitDuration, BilledChargeMay1, ExamBilledCountMay1, CaseNotesAvailable, CPTCodeShortDescription, CPTCodeMediumDescription, 
		CPTCodeLongDescription, AdjustedRequiredFTE, CreatedOn, LastUpdatedOn
	)
	VALUES
	(
		AccessionNumber, AETitle, Age, BilledCharge, BodyPart, CallbackAlertCode, CallbackComments, CallbackSupportStaff, ClinicalKey, Collected, Contrast, Cost, 
		CPTCode, CriticalFindingCode, DiscrepancyCode, ExamBilledCount, ExamCode, ExamCount, ExamInterpretedCount, FeePerStudy, FillerOrderKey, 
		FinancialChargeKey, FollowUp, Gender, LANGUAGE, LOINCCd, MaritalStatus, ModalityKey, ModalityLocation, ModalityName, ModalityType, Nationality, 
		NormalFinding, NumberOfChildren, NumberOfImages, NumberOfSeries, NumberOfStudies, OrderCount, OrderLocation, OrderSpecialty, PatientCount, PatientKey, 
		PatientMRN, PatientType, PeerReviewCode, PlacerOrderNumber, Priority, ProfessionalCharge, ProfessionalRVU, ProfessionalRVUAdjusted, Race, RadiationDose
		, SignedOnTimeliness, StudyId, TechnicalCharge, TechnicalRVU, TotalRVU, CallConnectDuration, PreProcedureDuration, InProcedureDuration, 
		PostProcedureDuration, WaitDuration, BilledChargeMay1, ExamBilledCountMay1, CaseNotesAvailable, CPTCodeShortDescription, CPTCodeMediumDescription, 
		CPTCodeLongDescription, AdjustedRequiredFTE, @curdt, @curdt
	);

	if @@error <> 0 set @status = 'FAILURE' else set @status = 'SUCCESS' 
	exec LogMessage @fileId,  'SP_UPDATE_DW','Exam Flat Merge Done',@status,@startedOn
	set @startedOn = GETDATE();	
	
	WITH tmpEWF
	AS (
		SELECT FO.FillerOrderKey, CalendarDateTime, CalendarDateAlternateKey, Year, Quarter, MonthName, MonthNumber, Day, WeekNumber, DayOfWeek, 
			CalendarTimeAlternateKey, EWF.ProviderKey, FullName, ExamState, ProviderType, DutyLocation, EWF.CallbackKey, ES.Description as ExamStateDesc
		FROM ExamWorkflow EWF
		INNER JOIN FillerOrder FO on EWF.FillerOrderKey = FO.FillerOrderKey
		INNER JOIN ExamState ES on EWF.ExamStateKey = ES.ExamStateKey
		LEFT JOIN CalendarDate DT ON DT.CalendarDateKey = EWF.CalendarDateKey
		LEFT JOIN CalendarTime Tme ON Tme.CalendarTimeKey = EWF.CalendarTimeKey
		LEFT JOIN Provider Pr ON Pr.ProviderKey = EWF.ProviderKey		
		WHERE FO.AccessionNumber in (Select AccessionNumber from #DailyDataLoadAccNo)
		)
	UPDATE EF
	SET OrderDateTime = EWFOR.CalendarDateTime, OrderDate = EWFOR.CalendarDateAlternateKey, OrderDateTimeYear = EWFOR.Year, OrderDateTimeQuarter = EWFOR.Quarter, 
		OrderDateTimeMonthName = EWFOR.MonthName, OrderDateTimeMonthNumber = EWFOR.MonthNumber, OrderDateTimeDay = EWFOR.Day, OrderDateTimeWeekNumber = EWFOR.
		WeekNumber, OrderDateTimeDayOfWeek = EWFOR.DayOfWeek, OrderTime = EWFOR.CalendarTimeAlternateKey, OrderPhysicianKey = EWFOR.ProviderKey, 
		OrderPhysicianName = EWFOR.FullName, ScheduleDateTime = EWFSC.CalendarDateTime, ScheduleDate = EWFSC.CalendarDateAlternateKey, ScheduleDateTimeYear = 
		EWFSC.Year, ScheduleDateTimeQuarter = EWFSC.Quarter, ScheduleDateTimeMonthName = EWFSC.MonthName, ScheduleDateTimeMonthNumber = EWFSC.MonthNumber, 
		ScheduleDateTimeDay = EWFSC.Day, ScheduleDateTimeWeekNumber = EWFSC.WeekNumber, ScheduleDateTimeDayOfWeek = EWFSC.DayOfWeek, ScheduleTime = EWFSC.
		CalendarTimeAlternateKey, FinalizeDateTime = EWFFI.CalendarDateTime, FinalizeDate = EWFFI.CalendarDateAlternateKey, FinalizeDateTimeYear = EWFFI.Year, 
		FinalizeDateTimeQuarter = EWFFI.Quarter, FinalizeDateTimeMonthName = EWFFI.MonthName, FinalizeDateTimeMonthNumber = EWFFI.MonthNumber, 
		FinalizeDateTimeDay = EWFFI.Day, FinalizeDateTimeWeekNumber = EWFFI.WeekNumber, FinalizeDateTimeDayOfWeek = EWFFI.DayOfWeek, FinalizeTime = EWFFI.
		CalendarTimeAlternateKey, SigningRadiologistName = EWFFI.FullName, SigningRadiologistType = EWFFI.ProviderType, SigningRadiologistDutyLocation = EWFFI.
		DutyLocation, CallbackDateTime = EWFCK.CalendarDateTime, CallbackDate = EWFCK.CalendarDateAlternateKey, CallbackDateTimeYear = EWFCK.Year, 
		CallbackDateTimeQuarter = EWFCK.Quarter, CallbackDateTimeMonthName = EWFCK.MonthName, CallbackDateTimeMonthNumber = EWFCK.MonthNumber, 
		CallbackDateTimeDay = EWFCK.Day, CallbackDateTimeWeekNumber = EWFCK.WeekNumber, CallbackDateTimeDayOfWeek = EWFCK.DayOfWeek, CallbackTime = EWFCK.
		CalendarTimeAlternateKey, CallbackRadiologistName = EWFCK.FullName, CallbackRadiologistType = EWFCK.ProviderType, CallbackRadiologistDutyLocation = 
		EWFCK.DutyLocation,
		-- Server Receive Date Time
		ExamEndDateTime = EWFSR.CalendarDateTime, ExamEndDate = EWFSR.CalendarDateAlternateKey, ExamEndDateTimeYear = EWFSR.Year, ExamEndDateTimeQuarter = EWFSR.
		Quarter, ExamEndDateTimeMonthName = EWFSR.MonthName, ExamEndDateTimeMonthNumber = EWFSR.MonthNumber, ExamEndDateTimeDay = EWFSR.Day, 
		ExamEndDateTimeWeekNumber = EWFSR.WeekNumber, ExamEndDateTimeDayOfWeek = EWFSR.DayOfWeek, ExamEndTime = EWFSR.CalendarTimeAlternateKey,
		-- Study Date Time
		ExamReadDateTime = EWFSD.CalendarDateTime, ExamReadDate = EWFSD.CalendarDateAlternateKey, ExamReadDateTimeYear = EWFSD.Year, ExamReadDateTimeQuarter = 
		EWFSD.Quarter, ExamReadDateTimeMonthName = EWFSD.MonthName, ExamReadDateTimeMonthNumber = EWFSD.MonthNumber, ExamReadDateTimeDay = EWFSD.Day, 
		ExamReadDateTimeWeekNumber = EWFSD.WeekNumber, ExamReadDateTimeDayOfWeek = EWFSD.DayOfWeek, ExamReadTime = EWFSD.CalendarTimeAlternateKey, 
		ChangeToStatDateTime = EWFCS.CalendarDateTime, ChangeToStatDate = EWFCS.CalendarDateAlternateKey, ChangeToStatDateTimeYear = EWFCS.Year, 
		ChangeToStatDateTimeQuarter = EWFCS.Quarter, ChangeToStatDateTimeMonthName = EWFCS.MonthName, ChangeToStatDateTimeMonthNumber = EWFCS.MonthNumber, 
		ChangeToStatDateTimeDay = EWFCS.Day, ChangeToStatDateTimeWeekNumber = EWFCS.WeekNumber, ChangeToStatDateTimeDayOfWeek = EWFCS.DayOfWeek, 
		ChangeToStatTime = EWFCS.CalendarTimeAlternateKey, TimelinessTargetDateTime = EWFTC.CalendarDateTime, TimelinessTargetDate = EWFTC.
		CalendarDateAlternateKey, TimelinessTargetDateTimeYear = EWFTC.Year, TimelinessTargetDateTimeQuarter = EWFTC.Quarter, 
		TimelinessTargetDateTimeMonthName = EWFTC.MonthName, TimelinessTargetDateTimeMonthNumber = EWFTC.MonthNumber, TimelinessTargetDateTimeDay = EWFTC.Day, 
		TimelinessTargetDateTimeWeekNumber = EWFTC.WeekNumber, TimelinessTargetDateTimeDayOfWeek = EWFTC.DayOfWeek, TimelinessTargetTime = EWFTC.
		CalendarTimeAlternateKey, LastUpdatedOn = @curdt
	
	FROM
		ExamFlat EF
	INNER JOIN #DailyDataLoadAccNo DL on EF.AccessionNumber = DL.AccessionNumber
	-- 'CK','CS','FI','OR','SD','SR'
	LEFT JOIN (
		SELECT *
		FROM tmpEWF EWF
		WHERE ExamState = 'OR'
		) EWFOR ON EF.FillerOrderKey = EWFOR.FillerOrderKey
	LEFT JOIN (
		SELECT *
		FROM tmpEWF EWF
		WHERE ExamState = 'SC'
		) EWFSC ON EF.FillerOrderKey = EWFSC.FillerOrderKey
	LEFT JOIN (
		SELECT *
		FROM tmpEWF EWF
		WHERE ExamState = 'SD'
		) EWFSD ON EF.FillerOrderKey = EWFSD.FillerOrderKey
	LEFT JOIN (
		SELECT *
		FROM tmpEWF EWF
		WHERE ExamState = 'SR'
		) EWFSR ON EF.FillerOrderKey = EWFSR.FillerOrderKey
	LEFT JOIN (
		SELECT *
		FROM tmpEWF EWF
		WHERE ExamState = 'FI'
		) EWFFI ON EF.FillerOrderKey = EWFFI.FillerOrderKey
	LEFT JOIN (
		SELECT *
		FROM tmpEWF EWF
		WHERE (ExamState in ( 'CS', 'RY'))
		) EWFCS ON EF.FillerOrderKey = EWFCS.FillerOrderKey
	LEFT JOIN (
		SELECT EWF.*
		FROM tmpEWF EWF
		inner join Exam EX on EWF.FillerOrderKey = EX.FillerOrderKey AND EWF.CallbackKey = EX.CallbackKey
		WHERE ExamState = 'CK'		
		) EWFCK ON EF.FillerOrderKey = EWFCK.FillerOrderKey
	LEFT JOIN (
		SELECT *
		FROM tmpEWF EWF
		WHERE ExamState = 'TC'
		) EWFTC ON EF.FillerOrderKey = EWFTC.FillerOrderKey;	
		
	if @@error <> 0 set @status = 'FAILURE' else set @status = 'SUCCESS' 
	exec LogMessage @fileId,  'SP_UPDATE_DW','Exam Flat Load Done',@status,@startedOn
	set @startedOn = GETDATE()
End