USE [HL_VA_LOAD] GO /****** Object: StoredProcedure [dbo].[UpdateDWExamDaily] Script Date: 09/29/2013 21:17:08 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[UpdateDWExamDaily] @fullLoad int, @fileId int AS BEGIN SET NOCOUNT ON; -- this is just for safety check, so people don't accidently run it if @fullLoad <> 1 and @fullLoad <> 747 return declare @curdt datetime set @curdt = GETDATE() print @curdt declare @today datetime set @today = cast(cast(@curdt as date) as datetime) declare @startedOn datetime declare @listenerGuid nvarchar (100) declare @status varchar(20) if @fullLoad = 747 Begin set @startedOn = GETDATE() truncate table HLDWDataVATest.dbo.Exam delete from Clinical delete from FillerOrder delete from FinancialCharge delete from Modality delete from Patient delete from Provider delete from FinancialTransaction -- cannot do reseed using synonyms DBCC CHECKIDENT ('HLDWDataVA.dbo.Patient',reseed, 1) DBCC CHECKIDENT ('HLDWDataVA.dbo.FillerOrder',reseed, 1) DBCC CHECKIDENT ('HLDWDataVA.dbo.Clinical',reseed, 1) DBCC CHECKIDENT ('HLDWDataVA.dbo.Modality',reseed, 1) DBCC CHECKIDENT ('HLDWDataVA.dbo.Provider',reseed, 1) DBCC CHECKIDENT ('HLDWDataVA.dbo.FinancialCharge',reseed, 1) DBCC CHECKIDENT ('HLDWDataVA.dbo.FinancialTransaction',reseed, 1) exec LogMessage @fileId, 'SP_UPDATE_DW','Tables truncated','SUCCESS',@startedOn End set @startedOn = GETDATE() Select @listenerGuid = L.Guid 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 * into #DailyDataLoad from dbo.fnDailyDataLoad(@listenerGUID) where AccessionNumber is not null 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() OPEN SYMMETRIC KEY MRNKeyAES256 DECRYPTION BY CERTIFICATE PatientMRNCert; -- Get Patient data into temp table for faster loading declare @patient table (PatientKey int PRIMARY KEY, PatientMRN varchar(50)) insert into @patient(PatientKey, PatientMRN) Select PatientKey, CONVERT(VARCHAR(50),DecryptByKey(PatientMRN)) from Patient if @@error <> 0 set @status = 'FAILURE' else set @status = 'SUCCESS' exec LogMessage @fileId, 'SP_UPDATE_DW','Patient Table Decrypted',@status,@startedOn set @startedOn = GETDATE() --Patient Data Upload Merge Patient P Using ( Select PatientKey, MRN, PatientBirthDateTime as DOB, isnull(PatientSex,'U') as Gender, NULL as Age, 'U' as MaritalStatus,NULL as MaritalStatusQualifier, NULL as NoOfChildren, NULL as Nationality, NULL as Language,NULL as Race,NULL as Residence, EncryptByKey(Key_GUID('MRNKeyAES256'), PatientName) as PatientName from ( Select distinct PatientId as MRN, PatientName,PatientBirthDateTime, PatientSex from #DailyDataLoad )T left join @Patient TPT on T.MRN = TPT.PatientMRN )Source --ON CONVERT(VARCHAR(50),DecryptByKey(P.PatientMRN)) = Source.MRN ON P.PatientKey = Source.PatientKey WHEN MATCHED THEN UPDATE SET FullName = isnull(Source.PatientName,P.FullName), Gender = case when Source.Gender is NULL or Source.Gender = 'U' then P.Gender else Source.Gender end, DateOfBirth = isnull(Source.DOB,P.DateOfBirth), Age = isnull(Source.Age,P.Age), MaritalStatus = case when Source.MaritalStatus is NULL or Source.MaritalStatus = 'U' then P.MaritalStatus else Source.MaritalStatus end, Race = isnull(Source.Race,P.Race), Nationality = isnull(Source.Nationality,P.Nationality), Language = isnull(Source.Language,P.Language), NumberOfChildren = isnull(Source.NoOfChildren,P.NumberOfChildren), LastUpdatedOn = @curdt WHEN NOT MATCHED THEN INSERT ( PatientMRN, FullName, Gender, DateOfBirth, Age, MaritalStatus, Race, Nationality, [Language], NumberOfChildren, CreatedOn, LastUpdatedOn ) VALUES ( EncryptByKey(Key_GUID('MRNKeyAES256'), source.MRN),source.PatientName,source.Gender,source.DOB, source.Age, source.MaritalStatus, source.Race,source.Nationality, source.Language,source.NoOfChildren,@curdt,@curdt); if @@error <> 0 set @status = 'FAILURE' else set @status = 'SUCCESS' exec LogMessage @fileId, 'SP_UPDATE_DW','Patient table updated',@status,@startedOn set @startedOn = GETDATE() -- Replace Last , character eg: Parker, Ellen, Update #DailyDataLoad set ReferringPhysician = substring(ReferringPhysician,1,len(ReferringPhysician) - 1) where charindex(',',REVERSE(ReferringPhysician)) = 1 or charindex('.',REVERSE(ReferringPhysician)) = 1 Update #DailyDataLoad set ReadingPhysician = substring(ReadingPhysician,1,len(ReadingPhysician) - 1) where charindex(',',REVERSE(ReadingPhysician)) = 1 or charindex('.',REVERSE(ReadingPhysician)) = 1 --Provider Upload Merge Provider Pr Using ( Select NULL as ProviderId, NULL as FirstName, NULL as LastName, NULL as MiddleName, Role, NULL as Title, FullName as FullName from ( Select distinct ReferringPhysician as FullName, 'ORP' as [Role] from #DailyDataLoad where ReferringPhysician is not NULL UNION ALL Select distinct ReadingPhysician as FullName, 'RAD' as [Role] from #DailyDataLoad where ReadingPhysician is not NULL )T ) Source on Pr.FullName = Source.FullName and Pr.[Role] = Source.[Role] when matched then update set ProviderId = isnull(Source.ProviderId, Pr.ProviderId), FirstName = isnull(Source.FirstName, Pr.FirstName), LastName = isnull(Source.LastName, Pr.LastName), Role = isnull(Source.Role, Pr.Role), MiddleName = isnull(Source.MiddleName, Pr.MiddleName), FullName = isnull(Source.FullName, Pr.FullName), Title = isnull(Source.Title,Pr.Title), LastUpdatedOn = @curdt when not matched then insert ( ProviderID, FirstName, LastName, [Role], MiddleName, Title, FullName, CreatedOn, LastUpdatedOn ) values ( Source.ProviderID, Source.FirstName, Source.LastName, Source.Role, Source.MiddleName, Source.Title, Source.FullName, @curdt, @curdt); update Provider set Provider.FirstName = T2.FirstName, Provider.LastName = T2.LastName, Provider.MiddleName = T2.MiddleName --Select Pr.FullName, T2.FullName, T2.FirstName,T2.LastName,T2.MiddleName from Provider Pr inner join ( Select ProviderKey, FullName, coalesce(LastName,FirstName,MiddleName) as LastName, FirstName = case when LastName is NULL and FirstName is NULL then NULL when LastName is NULL then MiddleName else FirstName end, MiddleName = case when LastName is NULL then NULL else MiddleName end from ( -- look help for Parsename Select providerKey, FullName,PARSENAME(FullName,1) as MiddleName, PARSENAME(FullName,2) as FirstName, PARSENAME(FullName,3) as LastName from ( Select ProviderKey, Replace(REPLACE(Replace(FullName,'.',''),', ','.'),' ','.') as FullName from provider )T )T1 )T2 on Pr.ProviderKey = T2.ProviderKey -- Set the default as 0 for all the NULLs . Helps in binning update Provider set NTPRadiologist = 0 where NTPRadiologist is NULL if @@error <> 0 set @status = 'FAILURE' else set @status = 'SUCCESS' exec LogMessage @fileId, 'SP_UPDATE_DW','Provider table updated',@status,@startedOn set @startedOn = GETDATE() -- Modality Data Upload Merge Modality M Using ( Select Modality as ModalityName, NULL as Manufacturer, NULL as Description, NULL as AETitle, NULL as IPAddress, 0 as isDeleted, Modality as modalityTypeCd, PerfLocation as PerformingLocation from ( Select distinct Modality, StudySourceLocation as PerfLocation from #DailyDataLoad where Modality is not null and StudySourceLocation is not null )T ) Source on M.ModalityName = Source.ModalityName and M.Location = Source.PerformingLocation when matched then update set ModalityName = isnull(Source.ModalityName, M.ModalityName), Manufacturer = isnull(Source.Manufacturer,M.Manufacturer), Description = isnull(Source.description,M.description), AETitle = isnull(Source.AETitle,M.AETitle), IpAddress = isnull(Source.ipAddress,M.ipAddress), isDeleted = isnull(Source.isDeleted,M.isDeleted), modalityTypeCd = isnull(Source.ModalityTypeCd,M.ModalityTypeCd), Location = isnull(Source.PerformingLocation,M.Location), LastUpdatedOn = @curdt when not matched then insert (ModalityName, Manufacturer, Description, AETitle, IPAddress, isDeleted, ModalityTypeCd,Location, CreatedOn, LastUpdatedOn ) Values(Source.ModalityName, Source.Manufacturer, Source.description, Source.AETitle, Source.ipAddress, Source.isDeleted, Source.ModalityTypeCd, Source.PerformingLocation, @curdt, @curdt); if @@error <> 0 set @status = 'FAILURE' else set @status = 'SUCCESS' exec LogMessage @fileId, 'SP_UPDATE_DW','Modality table updated',@status,@startedOn set @startedOn = GETDATE() -- Filler Order Upload Merge FillerOrder FO Using ( Select AccessionNumber as FillerOrderNumber, AccessionNumber, OrderNumber as PlacerOrderNumber, NULL as PatientType, Priority as ExamPriority, NULL as CriticalFindingCode, NULL as DiscrepancyCode, -- need to fill this from the table NULL as PeerReviewCode, NULL as NormalFinding, NULL as OrderSpecialty, Source as OrderLocation, 'Unassigned' as SignedOnTimeliness, -- Recalculated Later FollowUp, OrderStatus as ReportStatus from ( Select distinct AccessionNumber, OrderNumber, Priority, SignedOnTimeliness, Source = Upper(REPLACE((case when SUBSTRING(Source,4,1) = '_' then SUBSTRING(Source,5,len(Source)) + ' (Code: ' + SUBSTRING(Source,1,3) + ')' else Source END),'_',' ')), FollowUp, OrderStatus from #DailyDataLoad )T -- multiple records for the same acc. use NULL for ordernumber ) Source on --FO.FillerOrderNumber = Source.FillerOrderNumber --and Fo.Priority = Source.ExamPriority FO.AccessionNumber = Source.AccessionNumber when matched then update set FillerOrderNumber = isnull(Source.FillerOrderNumber,FO.FillerOrderNumber), AccessionNumber = isnull(Source.AccessionNumber,FO.AccessionNumber), PlacerOrderNumber = isnull(Source.PlacerOrderNumber,FO.PlacerOrderNumber), Priority = isnull(Source.ExamPriority,FO.Priority), PatientType = isnull(Source.PatientType,FO.PatientType), OrderSpecialty = isnull(Source.OrderSpecialty,FO.OrderSpecialty), CriticalFindingCode = isnull(Source.CriticalFindingCode,FO.CriticalFindingCode), PeerReviewCode = isnull(Source.PeerReviewCode,FO.PeerReviewCode), DiscrepancyCode = isnull(Source.DiscrepancyCode,FO.DiscrepancyCode), OrderLocation = isnull(Source.OrderLocation,FO.OrderLocation), NormalFinding = isnull(Source.NormalFinding,FO.NormalFinding), LastUpdatedOn = @curdt, -- SignedOnTimeliness = case when Source.SignedOnTimeliness is NULL or Source.SignedOnTimeliness = 'Error' then FO.SignedOnTimeliness else Source.SignedOnTimeliness end, SignedOnTimeliness = Source.SignedOnTimeliness, FollowUp = isnull(Source.FollowUp,FO.FollowUp), ReportStatus = isnull(Source.ReportStatus,FO.ReportStatus) when not matched then insert (FillerOrderNumber, AccessionNumber, PlacerOrderNumber, Priority, OrderSpecialty,CriticalFindingCode, PatientType,PeerReviewCode, DiscrepancyCode,NormalFinding, OrderLocation, CreatedOn, LastUpdatedOn, SignedOnTimeliness, FollowUp, ReportStatus ) Values(Source.FillerOrderNumber, Source.AccessionNumber, PlacerOrderNumber, Source.ExamPriority, Source.OrderSpecialty, Source.CriticalFindingCode, Source.PatientType, Source.PeerReviewCode, Source.DiscrepancyCode, Source.NormalFinding, Source.OrderLocation, @curdt, @curdt, Source.SignedOnTimeliness, Source.FollowUp, Source.ReportStatus); if @@error <> 0 set @status = 'FAILURE' else set @status = 'SUCCESS' exec LogMessage @fileId, 'SP_UPDATE_DW','Filler order table updated',@status,@startedOn set @startedOn = GETDATE() -- Clinical Upload Merge Clinical C Using ( Select NULL as ExamType, T.CPTCode, left(T.CPTCode,5) as ExamCode, NULL as LOINCCd, BodyPart, CPTRVU.ShortDescription, CPTRVU.MediumDescription, CPTRVU.LongDescription from ( -- we have null values and proper values for the same CPT code, so use max -- we also have cpt codes with no body parts at all so use max Select ProcCode as CPTCode, max(BodyPart) as BodyPart from #DailyDataLoad group by ProcCode )T -- ExamType has issues left join HLDWRef.dbo.vwCPTRVUWithDescription CPTRVU on T.CPTCode = CPTRVU.CPTCODE ) Source on C.CPTCode = Source.CPTCode --and C.BodyPart = Source.BodyPart -- need to clean this. Body part should be taken from CPT Code. Not from original source when matched then update set CPTCode = isnull(Source.CPTCode,C.CPTCode), LOINCCd = isnull(Source.LOINCCd,C.LOINCCd), ExamCode = isnull(Source.ExamCode,C.ExamCode), BodyPart = isnull(Source.BodyPart,C.BodyPart), ProcedureDescription = isnull(Source.ExamType,C.ProcedureDescription), CPTCodeShortDescription = isnull(Source.ShortDescription,C.CPTCodeShortDescription), CPTCodeMediumDescription = isnull(Source.MediumDescription,C.CPTCodeMediumDescription), CPTCodeLongDescription = isnull(Source.LongDescription,C.CPTCodeLongDescription), LastUpdatedOn = @curdt when not matched then insert (CPTCode, LOINCCd, ExamCode, BodyPart, ProcedureDescription,CPTCodeShortDescription,CPTCodeMediumDescription,CPTCodeLongDescription,CreatedOn, LastUpdatedOn ) Values(Source.CPTCode, Source.LOINCCd, Source.ExamCode, Source.BodyPart, Source.ExamType, Source.ShortDescription, Source.MediumDescription, Source.LongDescription, @curdt, @curdt); if @@error <> 0 set @status = 'FAILURE' else set @status = 'SUCCESS' exec LogMessage @fileId, 'SP_UPDATE_DW','Clinical table updated',@status,@startedOn set @startedOn = GETDATE() --Source - originating sige --TimeToServer - Exam End Date --StudyRead - Exam Read DateTime --SignedOn - FinalizedDateTime --DeliveredDateTime - add new column --TimeTaken - xxxxx --Exam -- Financial Charge /* Merge FinancialCharge FC Using (Select PayerName from tmpPayers) Source on FC.InsuranceProviderName = Source.PayerName when matched then update set InsuranceProviderName = Source.PayerName, LastUpdatedOn = @curdt when not matched then insert (InsuranceProviderName, CreatedOn, LastUpdatedOn ) Values(Source.PayerName, @curdt, @curdt); */ -- Do this again as there will be new patients --delete from @patient declare @patient2 table (PatientKey int PRIMARY KEY, PatientMRN varchar(50)) insert into @patient2(PatientKey, PatientMRN) Select PatientKey, CONVERT(VARCHAR(50),DecryptByKey(PatientMRN)) from Patient if @@error <> 0 set @status = 'FAILURE' else set @status = 'SUCCESS' exec LogMessage @fileId, 'SP_UPDATE_DW','Patient Table Decrypted',@status,@startedOn set @startedOn = GETDATE() -- Insert Exam Merge Exam E Using ( Select NULL as CalendarDateKey, FillerOrderKey, ModalityKey, PrREF.ProviderKey as OrderPhysicianKey, PatientKey, NULL as TechnologistKey, NULL as RadiologistKey, NULL as TranscriptionistKey, NULL as ReadingPhysicianKey, NULL as PeerRadiologistKey, NULL as PerformingRadiologistKey, NULL as DiscrepancyRadiologistKey, NULL as ReportingPhysicianKey, PrRAD.ProviderKey as SigningRadiologistKey, NULL as ProcedureKey, ClinicalKey, NULL as FinancialChargeKey, VAD.AccessionNumber, VAD.InterpretedCount as ExamInterpretedCount, NULL as ExamDateTime, NULL as ExamDateKey, NULL as ExamTimeKey, NULL as OrderDateTime, NULL as OrderDateKey, NULL as OrderTimeKey, NULL as ScheduleDateTime, NULL as ScheduleDateKey, NULL as ScheduleTimeKey, NULL as PatientCheckinDateTime, NULL as PatientCheckinDateKey, NULL as PatientCheckinTimeKey, NULL as ExamStartDateTime, NULL as ExamStartDateKey, NULL as ExamStartTimeKey, VAD.ServerReceivedDateTime as ExamEndDateTime, dbo.fnGetDateKey(VAD.ServerReceivedDateTime) as ExamEndDateKey, dbo.fnGetTimeKey(VAD.ServerReceivedDateTime) as ExamEndTimeKey, VAD.StudyDateTime as ExamReadDateTime, dbo.fnGetDateKey(VAD.StudyDateTime) as ExamReadDateKey, dbo.fnGetTimeKey(VAD.StudyDateTime) as ExamReadTimeKey, NULL as TranscribeDateTime, NULL as TranscribeDateKey, NULL as TranscribeTimeKey, VAD.FinalizeDateTime, dbo.fnGetDateKey(VAD.FinalizeDateTime) as FinalizeDateKey, dbo.fnGetTimeKey(VAD.FinalizeDateTime) as FinalizeTimeKey, TechnicalRVU, ProfessionalRVU, TotalRVU, NULL as TechnicalCharge, TotalCharge as ProfessionalCharge, NULL as Cost, NULL as Collected, getdate() as CreatedOn, getdate() as LastUpdatedOn, NULL as RadiationDose, NULL as Contrast, NULL as StudyId, NULL as NumberOfSeries, VAD.NumberOfImages as NumberOfImages, NULL as ChargesBilledDate, NULL as ChargesBilledDateKey, NULL as ChargesBilledTimeKey, NULL as ChargesCollectedDate, NULL as ChargesCollectedDateKey, NULL as ChargesCollectedTimeKey, NULL as AccountClosedDate, NULL as AccountClosedDateKey, NULL as AccountClosedTimeKey, VAD.OrderCount as OrderCount, VAD.BilledCharge as BilledCharge, VAD.ExamBilledCount, VAD.ExamBilledCountMay1, VAD.BilledChargeMay1 -- --Select VAD.AccessionNumber from #DailyDataLoad VAD inner join FillerOrder FO on VAD.AccessionNumber = FO.AccessionNumber inner join Modality M on VAD.Modality = M.ModalityTypeCd and VAD.StudySourceLocation = M.Location inner join @Patient2 P on VAD.PatientId = PatientMRN left join Clinical C on VAD.ProcCode = C.CPTCode LEFT join Provider PrRef on VAD.ReferringPhysician = PrRef.FullName and PrRef.[Role] = 'ORP' LEFT join Provider PrRad on VAD.ReadingPhysician = PrRad.FullName and PrRad.[Role] = 'RAD' ) Source ON E.FillerOrderKey = Source.FillerOrderKey WHEN MATCHED THEN UPDATE SET CalendarDateKey = isnull( Source.CalendarDateKey,E.CalendarDateKey ), FillerOrderKey = isnull( Source.FillerOrderKey,E.FillerOrderKey ), ModalityKey = isnull( Source.ModalityKey,E.ModalityKey ), OrderPhysicianKey = isnull( Source.OrderPhysicianKey,E.OrderPhysicianKey ), PatientKey = Source.PatientKey,--isnull( Source.PatientKey,E.PatientKey ),--always update patient key TechnologistKey = isnull( Source.TechnologistKey,E.TechnologistKey ), RadiologistKey = isnull( Source.PerformingRadiologistKey,E.RadiologistKey ), TranscriptionistKey = isnull( Source.TranscriptionistKey,E.TranscriptionistKey ), ReadingPhysicianKey = isnull( Source.ReadingPhysicianKey,E.ReadingPhysicianKey ), PeerRadiologistKey = isnull( Source.PeerRadiologistKey,E.PeerRadiologistKey ), --DiscrepancyRadiologistKey = isnull( Source.DiscrepancyRadiologistKey,E.DiscrepancyRadiologistKey ), ReportingPhysicianKey = isnull( Source.ReportingPhysicianKey,E.ReportingPhysicianKey ), SigningRadiologistKey = isnull( Source.SigningRadiologistKey,E.SigningRadiologistKey ), --ProcedureKey = isnull( Source.ProcedureKey,E.ProcedureKey ), ClinicalKey = isnull( Source.ClinicalKey,E.ClinicalKey ), FinancialChargeKey = isnull( Source.FinancialChargeKey,E.FinancialChargeKey ), --PayerKey = isnull( Source.PayerKey,E.PayerKey ), --CategoryKey = isnull( Source.CategoryKey,E.CategoryKey ), AccessionNumber = isnull( Source.AccessionNumber,E.AccessionNumber ), ExamInterpretedCount = isnull( Source.ExamInterpretedCount,E.ExamInterpretedCount ), ExamDateTime = isnull( Source.ExamDateTime,E.ExamDateTime ), ExamDateKey = isnull( Source.ExamDateKey,E.ExamDateKey ), ExamTimeKey = isnull( Source.ExamTimeKey,E.ExamTimeKey ), OrderDateTime = isnull( Source.OrderDateTime,E.OrderDateTime ), OrderDateKey = isnull( Source.OrderDateKey,E.OrderDateKey ), OrderTimeKey = isnull( Source.OrderTimeKey,E.OrderTimeKey ), ScheduleDateTime = isnull( Source.ScheduleDateTime,E.ScheduleDateTime ), ScheduleDateKey = isnull( Source.ScheduleDateKey,E.ScheduleDateKey ), ScheduleTimeKey = isnull( Source.ScheduleTimeKey,E.ScheduleTimeKey ), PatientCheckinDateTime = isnull( Source.PatientCheckinDateTime,E.PatientCheckinDateTime ), PatientCheckinDateKey = isnull( Source.PatientCheckinDateKey,E.PatientCheckinDateKey ), PatientCheckinTimeKey = isnull( Source.PatientCheckinTimeKey,E.PatientCheckinTimeKey ), ExamStartDateTime = isnull( Source.ExamStartDateTime,E.ExamStartDateTime ), ExamStartDateKey = isnull( Source.ExamStartDateKey,E.ExamStartDateKey ), ExamStartTimeKey = isnull( Source.ExamStartTimeKey,E.ExamStartTimeKey ), ExamEndDateTime = isnull( Source.ExamEndDateTime,E.ExamEndDateTime ), ExamEndDateKey = isnull( Source.ExamEndDateKey,E.ExamEndDateKey ), ExamEndTimeKey = isnull( Source.ExamEndTimeKey,E.ExamEndTimeKey ), ExamReadDateTime = isnull( Source.ExamReadDateTime,E.ExamReadDateTime ), ExamReadDateKey = isnull( Source.ExamReadDateKey,E.ExamReadDateKey ), ExamReadTimeKey = isnull( Source.ExamReadTimeKey,E.ExamReadTimeKey ), --TranscribeDateTime = isnull( Source.TranscribeDateTime,E.TranscribeDateTime ), --TranscribeDateKey = isnull( Source.TranscribeDateKey,E.TranscribeDateKey ), --TranscribeTimeKey = isnull( Source.TranscribeTimeKey,E.TranscribeTimeKey ), FinalizeDateTime = isnull( Source.FinalizeDateTime,E.FinalizeDateTime ), FinalizeDateKey = isnull( Source.FinalizeDateKey,E.FinalizeDateKey ), FinalizeTimeKey = isnull( Source.FinalizeTimeKey,E.FinalizeTimeKey ), TechnicalRVU = isnull( Source.TechnicalRVU,E.TechnicalRVU ), ProfessionalRVU = isnull( Source.ProfessionalRVU,E.ProfessionalRVU ), TotalRVU = isnull( Source.TotalRVU,E.TotalRVU ), TechnicalCharge = isnull( Source.TechnicalCharge,E.TechnicalCharge ), ProfessionalCharge = isnull( Source.ProfessionalCharge,E.ProfessionalCharge ), Cost = isnull( Source.Cost,E.Cost ), Collected = isnull( Source.Collected,E.Collected ), LastUpdatedOn = isnull( @curdt,E.LastUpdatedOn ), RadiationDose = isnull( Source.RadiationDose,E.RadiationDose ), Contrast = isnull( Source.Contrast,E.Contrast ), StudyId = isnull( Source.StudyId,E.StudyId ), NumberOfSeries = isnull( Source.NumberOfSeries,E.NumberOfSeries ), NumberOfImages = isnull( Source.NumberOfImages,E.NumberOfImages ), ChargesBilledDate = isnull( Source.ChargesBilledDate,E.ChargesBilledDate ), ChargesBilledDateKey = isnull( Source.ChargesBilledDateKey,E.ChargesBilledDateKey ), ChargesBilledTimeKey = isnull( Source.ChargesBilledTimeKey,E.ChargesBilledTimeKey ), ChargesCollectedDate = isnull( Source.ChargesCollectedDate,E.ChargesCollectedDate ), ChargesCollectedDateKey = isnull( Source.ChargesCollectedDateKey,E.ChargesCollectedDateKey ), ChargesCollectedTimeKey = isnull( Source.ChargesCollectedTimeKey,E.ChargesCollectedTimeKey ), AccountClosedDate = isnull( Source.AccountClosedDate,E.AccountClosedDate ), AccountClosedDateKey = isnull( Source.AccountClosedDateKey,E.AccountClosedDateKey ), AccountClosedTimeKey = isnull( Source.AccountClosedTimeKey,E.AccountClosedTimeKey ), OrderCount = isnull( Source.OrderCount,E.OrderCount ), BilledCharge = isnull( Source.BilledCharge,E.BilledCharge ), ExamBilledCount= isnull( Source.ExamBilledCount,E.ExamBilledCount ), BilledChargeMay1 = isnull( Source.BilledChargeMay1,E.BilledChargeMay1 ), ExamBilledCountMay1= isnull( Source.ExamBilledCountMay1,E.ExamBilledCountMay1 ) when NOT MATCHED THEN insert ( CalendarDateKey, FillerOrderKey, ModalityKey, OrderPhysicianKey, PatientKey, TechnologistKey, RadiologistKey, TranscriptionistKey, ReadingPhysicianKey, PeerRadiologistKey, -- DiscrepancyRadiologistKey, ReportingPhysicianKey, SigningRadiologistKey, -- ProcedureKey, ClinicalKey, FinancialChargeKey, -- PayerKey, -- CategoryKey, AccessionNumber, ExamInterpretedCount, ExamDateTime, ExamDateKey, ExamTimeKey, OrderDateTime, OrderDateKey, OrderTimeKey, ScheduleDateTime, ScheduleDateKey, ScheduleTimeKey, PatientCheckinDateTime, PatientCheckinDateKey, PatientCheckinTimeKey, ExamStartDateTime, ExamStartDateKey, ExamStartTimeKey, ExamEndDateTime, ExamEndDateKey, ExamEndTimeKey, ExamReadDateTime, ExamReadDateKey, ExamReadTimeKey, -- TranscribeDateTime, -- TranscribeDateKey, -- TranscribeTimeKey, FinalizeDateTime, FinalizeDateKey, FinalizeTimeKey, TechnicalRVU, ProfessionalRVU, TotalRVU, TechnicalCharge, ProfessionalCharge, Cost, Collected, CreatedOn, LastUpdatedOn, RadiationDose, Contrast, StudyId, NumberOfSeries, NumberOfImages, ChargesBilledDate, ChargesBilledDateKey, ChargesBilledTimeKey, ChargesCollectedDate, ChargesCollectedDateKey, ChargesCollectedTimeKey, AccountClosedDate, AccountClosedDateKey, AccountClosedTimeKey, OrderCount, BilledCharge, ExamBilledCount, BilledChargeMay1, ExamBilledCountMay1 ) VALUES ( CalendarDateKey, FillerOrderKey, ModalityKey, OrderPhysicianKey, PatientKey, TechnologistKey, PerformingRadiologistKey, TranscriptionistKey, ReadingPhysicianKey, PeerRadiologistKey, -- DiscrepancyRadiologistKey, ReportingPhysicianKey, SigningRadiologistKey, -- ProcedureKey, ClinicalKey, FinancialChargeKey, -- PayerKey, -- CategoryKey, AccessionNumber, ISNULL(ExamInterpretedCount,0), ExamDateTime, ExamDateKey, ExamTimeKey, OrderDateTime, OrderDateKey, OrderTimeKey, ScheduleDateTime, ScheduleDateKey, ScheduleTimeKey, PatientCheckinDateTime, PatientCheckinDateKey, PatientCheckinTimeKey, ExamStartDateTime, ExamStartDateKey, ExamStartTimeKey, ExamEndDateTime, ExamEndDateKey, ExamEndTimeKey, ExamReadDateTime, ExamReadDateKey, ExamReadTimeKey, --- TranscribeDateTime, --- TranscribeDateKey, --- TranscribeTimeKey, FinalizeDateTime, FinalizeDateKey, FinalizeTimeKey, TechnicalRVU, ProfessionalRVU, TotalRVU, TechnicalCharge, ProfessionalCharge, Cost, Collected, @curdt, @curdt, RadiationDose, Contrast, StudyId, NumberOfSeries, NumberOfImages, ChargesBilledDate, ChargesBilledDateKey, ChargesBilledTimeKey, ChargesCollectedDate, ChargesCollectedDateKey, ChargesCollectedTimeKey, AccountClosedDate, AccountClosedDateKey, AccountClosedTimeKey, ISNULL(OrderCount,0), ISNULL(BilledCharge,0), ISNULL(ExamBilledCount,0), ISNULL(BilledChargeMay1,0), ISNULL(ExamBilledCountMay1,0) ); if @@error <> 0 set @status = 'FAILURE' else set @status = 'SUCCESS' exec LogMessage @fileId, 'SP_UPDATE_DW','Exam table updated',@status,@startedOn set @startedOn = GETDATE() Update EX Set OrderDateKey = dbo.fnConvertToDateKey(EX.OrderDateTime), ScheduleDateKey = dbo.fnConvertToDateKey(EX.ScheduleDateTime), PatientCheckInDateKey = dbo.fnConvertToDateKey(EX.PatientCheckInDateTime), ExamStartDateKey = dbo.fnConvertToDateKey(EX.ExamStartDateTime), ExamEndDateKey = dbo.fnConvertToDateKey(EX.ExamEndDateTime), ExamReadDateKey = dbo.fnConvertToDateKey(EX.ExamReadDateTime), TranscribeDateKey = dbo.fnConvertToDateKey(EX.TranscribeDateTime), FinalizeDateKey = dbo.fnConvertToDateKey(EX.FinalizeDateTime), ExamDateTime = Coalesce(EX.FinalizeDateTime,EX.ExamEndDateTime, EX.ExamReadDateTime), ExamDateKey = dbo.fnConvertToDateKey(Coalesce(EX.FinalizeDateTime,EX.ExamEndDateTime, EX.ExamReadDateTime)), CalendarDateKey = dbo.fnConvertToDateKey(Coalesce(EX.FinalizeDateTime,EX.ExamEndDateTime, EX.ExamReadDateTime)) from Exam EX inner join FillerOrder FO on EX.FillerOrderKey = FO.FillerOrderKey inner join #DailyDataLoad VAD on VAD.AccessionNumber = FO.AccessionNumber print 'Update Exam Date Key Done' exec LogMessage @fileId, 'SP_UPDATE_DW','Exam date time Keys updated',@status,@startedOn set @startedOn = GETDATE() -- Required FTE update Ex set RequiredFTE = dbo.fnGetRequiredFTE(MO.ModalityTypeCd) from Exam EX inner join Modality MO on Ex.ModalityKey = MO.ModalityKey inner join FillerOrder FO on EX.FillerOrderKey = FO.FillerOrderKey inner join #DailyDataLoad VAD on VAD.AccessionNumber = FO.AccessionNumber print 'Update Required FTE Done' exec LogMessage @fileId, 'SP_UPDATE_DW','Required FTE updated',@status,@startedOn set @startedOn = GETDATE() -- Insert ExamWorkflow Merge ExamWorkflow EWF Using ( Select FO.FillerOrderKey, dbo.fnGetDateKey(CalendarDateTime) as CalendarDateKey, dbo.fnGetTimeKey(CalendarDateTime) as CalendarTimeKey, CalendarDateTime, ProviderKey, ExamStateKey, ExamStateName from ( Select VAD.AccessionNumber,NULL as CalendarDateTime, PrRef.ProviderKey, PrRef.FullName as FullName, 'OR' as ExamStateName from #DailyDataLoad VAD inner join Provider PrRef on VAD.ReferringPhysician = PrRef.FullName and PrRef.[Role] = 'ORP' UNION Select VAD.AccessionNumber,ServerReceivedDateTime, NULL as ProviderKey, NULL as FullName, 'SR' as ExamStateName from #DailyDataLoad VAD union Select VAD.AccessionNumber,StudyDateTime, NULL as ProviderKey, NULL as FullName, 'SD' as ExamStateName from #DailyDataLoad VAD UNION Select VAD.AccessionNumber,FinalizeDatetime, PrRad.ProviderKey, PrRad.FullName as FullName, 'FI' as ExamStateName from #DailyDataLoad VAD left join Provider PrRad on VAD.ReadingPhysician = PrRad.FullName and PrRad.[Role] = 'RAD' where OrderStatus in ('Report Delivered','Report Signed') and (VAD.ReadingPhysician is not null or VAD.FinalizeDatetime is not null) UNION Select VAD.AccessionNumber, TimelinessCutOfDateTime = case when Priority = 'STAT' then DateAdd(HH, 1, ServerReceivedDateTime) else DateAdd(HH, 48, ServerReceivedDateTime) end, NULL as ProviderKey, NULL as FullName, 'TC' as ExamStateName from #DailyDataLoad VAD )VAD inner join FillerOrder FO on VAD.AccessionNumber = FO.AccessionNumber inner join ExamState ES on VAD.ExamStateName = ES.Name ) Source ON EWF.FillerOrderKey = Source.FillerOrderKey and EWF.ExamStateKey = Source.ExamStateKey WHEN MATCHED THEN UPDATE SET CalendarDateKey = isnull( Source.CalendarDateKey,EWF.CalendarDateKey ), CalendarTimeKey = isnull( Source.CalendarTimeKey,EWF.CalendarTimeKey ), CalendarDateTime = isnull( Source.CalendarDateTime,EWF.CalendarDateTime ), FillerOrderKey = isnull( Source.FillerOrderKey,EWF.FillerOrderKey ), ProviderKey = isnull( Source.ProviderKey,EWF.ProviderKey ), ExamStateKey = isnull( Source.ExamStateKey,EWF.ExamStateKey ), ExamState = isnull( Source.ExamStateName,EWF.ExamState ), LastUpdatedOn = isnull( @curdt,EWF.LastUpdatedOn ) when NOT MATCHED THEN insert ( FillerOrderKey, CalendarDateKey, CalendarTimeKey, CalendarDateTime, ProviderKey, ExamStateKey, ExamState, CreatedOn, LastUpdatedOn ) VALUES ( FillerOrderKey, CalendarDateKey, CalendarTimeKey, CalendarDateTime, ProviderKey, ExamStateKey, ExamStateName, @curdt, @curdt ); print 'Update Exam Workflow Done' exec LogMessage @fileId, 'SP_UPDATE_DW','Exam workflow updated',@status,@startedOn set @startedOn = GETDATE() print @startedON -- Recalculate Billing Data declare @tbl typDailyExam insert into @tbl Select accessionNumber from #DailyDataLoad exec dbo.RecalculateBillingData @tbl print 'Recalculate Billing Data Done' exec LogMessage @fileId, 'SP_UPDATE_DW','Recalculate Billing Data Done',@status,@startedOn set @startedOn = GETDATE() print @startedON --special decryptor for traansition --delete from HLDWDataVA.dbo.patientcc --insert into HLDWDataVA.dbo.patientcc(PatientKey2, PatientMRN2, PatientFullName2) --Select PatientKey, CONVERT(VARCHAR(50),DecryptByKey(PatientMRN)), CONVERT(VARCHAR(50),DecryptByKey(FullName)) --from HLDWDataVA.dbo.Patient if @@error <> 0 set @status = 'FAILURE' else set @status = 'SUCCESS' exec LogMessage @fileId, 'SP_UPDATE_DW','DW daily load done',@status,@startedOn CLOSE SYMMETRIC KEY MRNKeyAES256 print 'Load Done' SET NOCOUNT OFF END