SELECT * FROM information_schema.views WHERE information_schema.views.table_name LIKE '%#totl_visits#%'; SELECT * FROM information_schema.views WHERE information_schema.views.table_name LIKE '%#Inncomplete_visits#%'; CREATE VIEW #session.AgencyId#_TotalVisits AS SELECT py.Payer_ID,py.PayName, si.Output_Type,COUNT(si.ScheduleItems_Id) as unbilled_TotalVisits FROM #Request.prefix_db_agency#.ScheduleItems si, #Request.prefix_db_agency#.Assessment_New an, #Request.prefix_db_agency#.patient p, #Request.prefix_db_lookup#.Agency a,#Request.prefix_db_agency#.Payer py WHERE p.Patient_ID = si.Patient_ID AND a.Agency_ID = #session.AgencyId# and py.Payer_Id = si.Payer_Id AND p.Status <> 'deleted' AND an.Assessment_Tracker_ID = si.Assessment_Tracker_ID AND si.Visit_Date < Now() AND si.Deleted <> 1 And si.MR_690 <= 0 AND si.Invoice_ID = 0 AND an.Deleted = 0 GROUP BY si.Payer_Id, si.Output_Type ORDER BY py.PayName, si.Output_Type; CREATE VIEW #session.AgencyId#_IncompleteVisits AS SELECT py.Payer_ID, si.Output_Type,COUNT(si.ScheduleItems_Id) as Incomplete_TotalNotes FROM #Request.prefix_db_agency#.ScheduleItems si, #Request.prefix_db_agency#.Assessment_New an, #Request.prefix_db_agency#.patient p, #Request.prefix_db_lookup#.Agency a,#Request.prefix_db_agency#.Payer py WHERE p.Patient_ID = si.Patient_ID AND a.Agency_ID = #session.AgencyId# AND p.Status <> 'deleted' and py.Payer_Id = si.Payer_Id AND an.Assessment_Tracker_ID = si.Assessment_Tracker_ID AND si.Visit_Date < Now() AND si.Deleted <> 1 AND si.Nonbillable = 0 AND si.Missed <> 'Yes' AND si.Output_Type != "Final" AND si.EClaimed = 0 AND si.Invoice_ID = 0 AND an.Deleted = 0 GROUP BY si.Payer_Id, si.Output_Type ORDER BY py.PayName, si.Output_Type (SELECT distinct * FROM #session.AgencyId#_IncompleteVisits LEFT OUTER JOIN #session.AgencyId#_TotalVisits on #session.AgencyId#_IncompleteVisits.Payer_ID = #session.AgencyId#_TotalVisits.Payer_ID AND #session.AgencyId#_IncompleteVisits.Output_Type = #session.AgencyId#_TotalVisits.Output_Type Group by #session.AgencyId#_TotalVisits.Output_Type,#session.AgencyId#_TotalVisits.Payer_ID) UNION (SELECT distinct * FROM #session.AgencyId#_IncompleteVisits RIGHT OUTER JOIN #session.AgencyId#_TotalVisits on #session.AgencyId#_IncompleteVisits.Payer_ID = #session.AgencyId#_TotalVisits.Payer_ID AND #session.AgencyId#_IncompleteVisits.Output_Type = #session.AgencyId#_TotalVisits.Output_Type Group by #session.AgencyId#_IncompleteVisits.Output_Type,#session.AgencyId#_IncompleteVisits.Payer_ID) select c.Patient_ID,count(c.Comm_ID) as total_communication ,p.Last_Name_M0040,p.First_Name_M0040 from #Request.prefix_db_agency#.Communication c join #Request.prefix_db_agency#.patient p on c.Patient_ID = p.Patient_ID GROUP BY p.Patient_ID asc
Total Unbilled Visits & Incomplete Notes
Payer Output_Type Total Unbilled Visits Total Incomplete Progress Notes
#PayName# #Output_Type# #unbilled_TotalVisits# #Incomplete_TotalNotes#
Total Communications
Patient Total Communications
#Last_Name_M0040#, #First_Name_M0040# #total_communication#