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);
|