SELECT an.Assmt_ID, an.F25, an.F26, pa.M0030_START_CARE_DT, an.F177, an.F178,p.Patient_ID, an.F25 AS assStatus, p.Pt_Last, p.Pt_First, p.Pt_Middle,p.Pt_Street,an.F300 AS MCARE, r.Referral_Name, p.Pt_City, p.Pt_State, p.Pt_Zip, p.Pt_DOB, p.Pt_Phone, p.Pt_Agy_ID, p.Status as PSTATUS, y.Pay_Name from #Request.prefix_db_agency#.pAssessments an LEFT OUTER JOIN #Request.prefix_db_agency#.pPatients p on an.Patient_Id = p.Patient_Id LEFT OUTER JOIN #Request.prefix_db_agency#.pAdmit pa on an.Admit_ID = pa.Admit_ID AND pa.Status = 0 LEFT OUTER JOIN #Request.prefix_db_agency#.pPtPayer pt on p.Patient_Id = pt.Patient_ID AND pt.Status = 0 AND pt.Payer_Responsibility = 'P - Primary' LEFT OUTER JOIN #Request.prefix_db_agency#.pPayer y on y.Pay_ID = pt.Payer_ID AND y.Status = 0 LEFT OUTER JOIN #Request.prefix_db_agency#.pReferral r on r.Referral_ID = pa.Referral_ID WHERE p.Status IN (0,1) AND an.Status = 0 AND an.F25 = 1 AND an.F26 NOT IN ('H&P Review') AND an.F176 >= #CreateODBCDate(Form.From_Date)# AND an.F176 <= #CreateODBCDate(Form.To_Date)# AND pa.Admit_Type = '#Form.Admit_Type#' Group by an.Assmt_ID ORDER BY an.F26 SELECT an.Assmt_ID, an.F25, an.F26, pa.M0030_START_CARE_DT, an.F177, an.F178,p.Patient_ID, an.F25 AS assStatus, p.Pt_Last, p.Pt_First, p.Pt_Middle,p.Pt_Street,an.F300 AS MCARE, r.Referral_Name, p.Pt_City, p.Pt_State, p.Pt_Zip, p.Pt_DOB, p.Pt_Phone, p.Pt_Agy_ID, p.Status AS PSTATUS, y.Pay_Name, (SELECT count(*) FROM #Request.prefix_db_agency#.Communication, #Request.prefix_db_agency#.CommunicationSubjects WHERE Communication.Patient_ID = p.Patient_ID AND CommunicationSubjects.CommSub_ID = Communication.CommSub_ID AND (CommunicationSubjects.Subject like "Discharge Summary - SN%" OR CommunicationSubjects.Subject like 'Agency Transfer/Discharge Summary') AND Communication.Comm_record_deleted <> 1 AND an.F27 = 'SN' AND Communication.Status = 0 ) AS comm_SNdischarge, (SELECT count(*) FROM #Request.prefix_db_agency#.Communication, #Request.prefix_db_agency#.CommunicationSubjects WHERE Communication.Patient_ID = p.Patient_ID AND CommunicationSubjects.CommSub_ID = Communication.CommSub_ID AND (CommunicationSubjects.Subject like "Discharge Summary - PT%" ) AND Communication.Comm_record_deleted <> 1 AND an.F27 = 'PT' AND Communication.Status = 0 ) AS comm_PTdischarge, (SELECT count(*) FROM #Request.prefix_db_agency#.Communication, #Request.prefix_db_agency#.CommunicationSubjects WHERE Communication.Patient_ID = p.Patient_ID AND CommunicationSubjects.CommSub_ID = Communication.CommSub_ID AND (CommunicationSubjects.Subject like "Discharge Summary - OT%" ) AND Communication.Comm_record_deleted <> 1 AND an.F27 = 'OT' AND Communication.Status = 0 ) AS comm_OTdischarge, (SELECT count(*) FROM #Request.prefix_db_agency#.Communication, #Request.prefix_db_agency#.CommunicationSubjects WHERE Communication.Patient_ID = p.Patient_ID AND CommunicationSubjects.CommSub_ID = Communication.CommSub_ID AND (CommunicationSubjects.Subject like "Discharge Summary - ST%") AND Communication.Comm_record_deleted <> 1 AND an.F27 = 'ST' AND Communication.Status = 0 ) AS comm_STdischarge , (SELECT count(*) FROM #Request.prefix_db_agency#.Communication, #Request.prefix_db_agency#.CommunicationSubjects WHERE Communication.Patient_ID = p.Patient_ID AND CommunicationSubjects.CommSub_ID = Communication.CommSub_ID AND (CommunicationSubjects.Subject like "Discharge Summary - MSW%" ) AND Communication.Comm_record_deleted <> 1 AND an.F27 = 'MSW' AND Communication.Status = 0 ) AS comm_MSWdischarge , (SELECT count(*) FROM #Request.prefix_db_agency#.pAssessments pass WHERE (pass.F26 like '9%' OR pass.F26 like '7%' OR pass.F26 like '8%') AND pass.Status =0 AND pass.F176 < now() AND pass.Admit_ID = pa.Admit_ID) AS Discharge_count , (SELECT count(*) FROM #Request.prefix_db_agency#.pAssessments pass WHERE pass.F26 like 'SN Adult%' AND pass.Status =0 AND pass.F176 = pa.M0030_START_CARE_DT AND pass.Admit_ID = pa.Admit_ID) AS SN_AdultCount, (SELECT count(*) FROM #Request.prefix_db_agency#.pAssessments pass WHERE pass.F26 like 'PT Adult%' AND pass.Status =0 AND pass.F176 = pa.M0030_START_CARE_DT AND pass.Admit_ID = pa.Admit_ID) AS PT_AdultCount, (SELECT count(*) FROM #Request.prefix_db_agency#.pAssessments pass WHERE pass.F26 like 'OT Adult%' AND pass.Status =0 AND pass.F176 = pa.M0030_START_CARE_DT AND pass.Admit_ID = pa.Admit_ID) AS OT_AdultCount, (SELECT count(*) FROM #Request.prefix_db_agency#.pAssessments pass WHERE pass.F26 like 'ST Adult%' AND pass.Status =0 AND pass.F176 = pa.M0030_START_CARE_DT AND pass.Admit_ID = pa.Admit_ID) AS ST_AdultCount, (SELECT count(*) FROM #Request.prefix_db_agency#.pAssessments pass WHERE pass.F26 like 'MSW Adult%' AND pass.Status =0 AND pass.F176 = pa.M0030_START_CARE_DT AND pass.Admit_ID = pa.Admit_ID) AS MSW_AdultCount from #Request.prefix_db_agency#.pAssessments an JOIN #Request.prefix_db_agency#.pPatients p on an.Patient_Id = p.Patient_Id JOIN #Request.prefix_db_agency#.pAdmit pa on an.Admit_ID = pa.Admit_ID LEFT OUTER JOIN #Request.prefix_db_agency#.pPtPayer pt on p.Patient_Id = pt.Patient_ID AND pt.Status = 0 AND pt.Payer_Responsibility = 'P - Primary' LEFT OUTER JOIN #Request.prefix_db_agency#.pPayer y on y.Pay_ID = pt.Payer_ID AND y.Status = 0 LEFT OUTER JOIN #Request.prefix_db_agency#.pReferral r on r.Referral_ID = pa.Referral_ID WHERE p.Status IN (0,1) AND an.Status = 0 AND pa.Status = 0 AND an.F25 = 1 AND (an.F26 like '%Adult%' OR an.F26 like 'MSW Evaluation%') AND an.Admit_ID NOT IN (SELECT pAssessments.Admit_ID FROM #Request.prefix_db_agency#.pAssessments WHERE (pAssessments.F26 like '9%' OR pAssessments.F26 like '7%' OR pAssessments.F26 like '8%') AND pAssessments.Status =0 AND pAssessments.F176 < now()) AND an.F176 >= #CreateODBCDate(Form.From_Date)# AND an.F176 <= #CreateODBCDate(Form.To_Date)# AND y.Biller_type="Home Health" AND y.Biller_type="Hospice" Group by an.Assmt_ID ORDER BY an.F26 SELECT Assmt_ID, F25, F26, M0030_START_CARE_DT, F177, F178,Patient_ID, assStatus, F26 AS Activity, Pt_Last, Pt_First, Pt_Middle,Pt_Street, MCARE, Referral_Name, Pt_City, Pt_State, Pt_Zip, Pt_DOB, Pt_Phone, Pt_Agy_ID, 0 AS comm_SNdischarge,0 AS comm_PTdischarge,0 AS comm_OTdischarge,0 AS comm_STdischarge,0 AS comm_MSWdischarge , Pay_Name FROM GetsPatientsstatus UNION SELECT Assmt_ID, F25, F26, M0030_START_CARE_DT, F177, F178,Patient_ID, assStatus,'DC' AS Activity, Pt_Last, Pt_First, Pt_Middle,Pt_Street, MCARE, Referral_Name, Pt_City, Pt_State, Pt_Zip, Pt_DOB, Pt_Phone, Pt_Agy_ID, comm_SNdischarge,comm_PTdischarge,comm_OTdischarge,comm_STdischarge,comm_MSWdischarge , Pay_Name FROM GetadultPatients WHERE (comm_SNdischarge = 1 OR comm_PTdischarge = 1 OR comm_OTdischarge = 1 OR comm_STdischarge = 1 OR comm_MSWdischarge = 1) AND Discharge_count = 0 ORDER BY F26, Patient_ID SELECT * FROM GetadultPatients WHERE (SN_AdultCount > 0 OR PT_AdultCount > 0 OR OT_AdultCount > 0 OR ST_AdultCount > 0 OR MSW_AdultCount > 0) ORDER BY F26 SELECT p.*,y.Pay_Name, r.Referral_Name FROM #Request.prefix_db_agency#.pPatients p JOIN #Request.prefix_db_agency#.pAdmit pa on pa.Patient_ID = p.Patient_ID AND pa.Status = 0 LEFT OUTER JOIN #Request.prefix_db_agency#.pPtPayer pt on p.Patient_Id = pt.Patient_ID AND pt.Status = 0 LEFT OUTER JOIN #Request.prefix_db_agency#.pReferral r on r.Referral_ID = pa.Referral_ID LEFT OUTER JOIN #Request.prefix_db_agency#.pPayer y on y.Pay_ID = pt.Payer_ID AND y.Status = 0 AND pt.Payer_Responsibility = 'P - Primary' JOIN #Request.prefix_db_agency#.pAuths pu on pu.Patient_ID = p.Patient_ID AND pu.Status = 0 and pu.Assmt_ID = 0 JOIN #Request.prefix_db_agency#.pSchedules ps on ps.Auth_ID = pu.Auth_ID AND ps.Status = 0 and ps.Assmt_ID = 0 WHERE p.Status IN (0,1) AND ps.Visit_Date >= #CreateODBCDate(Form.From_Date)# AND ps.Visit_Date <= #CreateODBCDate(Form.To_Date)# Group by p.Patient_ID ORDER BY p.Patient_ID

Total Assessment/Evaluation Activity

&df=#Form.From_Date#&dt=#Form.To_Date# &payType=#Replace(Form.PayerType, ",", ", ", "ALL")#">

Assessment/Evaluation Activity (#Form.From_Date# to #Form.To_Date#)

Payer Type:

#Replace(Form.PayerType, ",", ", ", "ALL")#

#F26# - #admit_status#

No Assessment - #Get_noassessmentpatients.recordcount#

> > F26 EQ 'SN Adult Evaluation' > F26 EQ 'PT Adult Evaluation'> F26 EQ 'OT Adult Evaluation'> F26 EQ 'ST Adult Evaluation'> F26 EQ 'MSW Evaluation'> --->

SN Discharge - #SNdischarge#

PT Discharge - #PTdischarge#

OT Discharge - #OTdischarge#

ST Discharge - #STdischarge#

MSW Discharge - #MSWdischarge#

Patient Name	Referral Source	Payer	Assessment/Evaluation
#Pt_Last#, #Pt_First# #Pt_Middle#	#Referral_Name#	#Pay_Name#	SN Discharge PT Discharge OT Discharge ST Discharge MSW Discharge #Activity#
#Pt_Last#, #Pt_First# #Pt_Middle#	#Referral_Name#	#Pay_Name#	No Assessment