Cognos2 matcher query

Cognos2_matcher query populates Cognos2 Service Starter.

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 etc. Since MW also strips them, open this in edit mode for the clean version.

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.