Cognos2 matcher query: Difference between revisions

From CCMDB Wiki
Jump to navigation Jump to search
← Older edit
No edit summary
No edit summary
 
Line 64: Line 64:


[[Category:Cognos2]]
[[Category:Cognos2]]
[[Category:PatientFollow Project]]

Latest revision as of 13:28, 24 December 2025

Cognos2_matcher query populates Cognos2 Service Starter. Among other things it implements workload sharing as per PatientFollow Project.

readable version

See CCMDB.accdb for actual master, but here is a readable version since this query is too complex for the Access visual editor and Access strips all line breaks.    
SELECT DISTINCT Cognos2_OneTime.ClientGUID, Cognos2_OneTime.ClientVisitGUID, Cognos2_OneTime.FirstName, Cognos2_OneTime.LastName, Cognos2_OneTime.HospitalMRN, Cognos2_OneTime.Chart, Cognos2_OneTime.Visit_Admit_DtTm, L_Log.Pat_ID AS Match_ChartPat_ID, ISNULL([Cognos2_Services_w_o_matches].[HospitalMRN]) AS MatchServiceTime_Pat_ID, L_Log.RecordStatus, Cognos2_Services.Service, Cognos2_Services.Service_StartDtTm, Cognos2_Services.Service_EndDtTm, s_Cognos_Services.exclude, Cognos2_OneTime.Gender, Cognos2_OneTime.PHIN, Cognos2_OneTime.DateOfBirth, Cognos2_OneTime.Prov, Cognos2_OneTime.PostalCode, [s_PatientFollow_distribution].[StartDtTm] <=[Cognos2_Services].[Service_StartDtTm] AND [Cognos2_Services].[Service_StartDtTm]<[s_PatientFollow_distribution].[EndDtTm] AND     [s_laptops].[laptop]=Laptop_identifier() AS followed, L_Log.Accept_DtTm, L_Log.Arrive_DtTm, L_Log.Dispo_DtTm, nz(L_Log.Accept_DtTm, L_Log.Arrive_DtTm) AS Profile_Start_DtTm
FROM ((((((Cognos2_OneTime
	INNER JOIN Cognos2_Services ON Cognos2_OneTime.ClientVisitGUID = Cognos2_Services.ClientVisitGUID)
	LEFT JOIN s_Cognos_Services ON Cognos2_Services.Service = s_Cognos_Services.[Service])
	LEFT JOIN L_Log ON Cognos2_OneTime.Chart = L_Log.Chart)
	INNER JOIN s_laptops ON Cognos2_OneTime.hosp = s_laptops.hosp)
	INNER JOIN s_PatientFollow_distribution 
	ON (
	s_laptops.Laptop_ID = s_PatientFollow_distribution.Laptop) AND (
	Cognos2_OneTime.LastTwo = s_PatientFollow_distribution.LastTwoDigits))
	LEFT JOIN sL_Cognos2_Service_exclusions 
	ON (
	Cognos2_Services.HospitalMRN = sL_Cognos2_Service_exclusions.HospitalMRN) 
		AND (Cognos2_Services.Service = sL_Cognos2_Service_exclusions.Service) 
		AND (Cognos2_Services.[Service_StartDtTm] = sL_Cognos2_Service_exclusions.Service_StartDtTm)) 
	LEFT JOIN 
	
		(SELECT NewService.HospitalMRN, NewService.Chart, NewService.Service, NewService.Service_StartDtTm, NewService.Service_EndDtTm, L_TmpV2_Service.Pat_ID 
		FROM (
			SELECT DISTINCT Cognos2_Services.HospitalMRN, Cognos2_OneTime.Chart, Cognos2_Services.Service, Cognos2_Services.Service_StartDtTm, Cognos2_Services.Service_EndDtTm FROM Cognos2_OneTime 
			INNER JOIN Cognos2_Services ON Cognos2_OneTime.HospitalMRN = Cognos2_Services.HospitalMRN)  AS NewService 
			LEFT JOIN (
			
				SELECT L_TmpV2.Pat_ID, L_Log.Chart, L_TmpV2.Item, [time_var]+[date_var] AS DtTm_var FROM L_TmpV2 
				INNER JOIN L_Log ON L_TmpV2.Pat_ID = L_Log.Pat_ID 
				WHERE ( ((L_TmpV2.Project)="Service"))) AS L_TmpV2_Service 
				
			ON (
			NewService.Chart = L_TmpV2_Service.Chart) 
				AND (NewService.Service = L_TmpV2_Service.Item) 
				AND (NewService.Service_StartDtTm = L_TmpV2_Service.DtTm_var) 
		WHERE ( ((L_TmpV2_Service.Pat_ID) IS NULL)))  AS Cognos2_Services_w_o_matches 
	ON (Cognos2_Services.HospitalMRN = Cognos2_Services_w_o_matches.HospitalMRN) 
		AND (Cognos2_Services.Service = Cognos2_Services_w_o_matches.Service) 
		AND (Cognos2_Services.Service_StartDtTm = Cognos2_Services_w_o_matches.Service_StartDtTm) 
		AND (Cognos2_Services.Service_EndDtTm = Cognos2_Services_w_o_matches.Service_EndDtTm)
WHERE (((s_Cognos_Services.exclude)=False)         
		AND ( ([StartDtTm]<=[Cognos2_Services].[Service_StartDtTm]
			AND [Cognos2_Services].[Service_StartDtTm]<[EndDtTm]
			AND ([s_laptops].[laptop] =Laptop_identifier())=True
			OR [Cognos2_Services].[Service]="HSC Critical Care / Intermediate" ) )
		AND ( (Nz([program]))="" OR (Nz([program]))="CC" OR (Nz([program]))="Med" )
		AND ( (Cognos2_OneTime.hosp)=hosp())
		AND ( (sL_Cognos2_Service_exclusions.HospitalMRN) IS NULL)) 
	AND (NOT (Cognos2_Services_w_o_matches.HospitalMRN) IS NULL);

Log

  • 2023-12-31 - re-did the change since the previous change broke things. Also added readable version above to make future edits of this more efficient
  • 2023-12-27 - removed filter for program as part of Change to having each collector collect both programs on the same laptop
  • 2022-06-30 - confirmed that this uses S PatientFollow distribution table including start and end dates; made this more explicit in query by using query name explicitly in field name references
  • 2021-01-27 - changed so match on end date is not required; necessary since the end date of services where the patient still is always list as today, ie always keep changing.

Related articles

Related articles:
Retrieved from "https://ccmdb.kuality.ca/index.php?title=Cognos2_matcher_query&oldid=168237"