About

This SQL will show all originating phone messages and who wrote them between the two dates you specify. It also includes:

• The message subject and text
• The duration (if completed)
• The patient’s current primary and secondary insurance codes

“Responses” shows the number of distinct responses to the original message on that thread.

A sample image of this SQL report run in the Database Viewer is shown below:

Report Usage Ideas

• Phone messages created by providers might be internal (i.e. setting up referral), but they might be phone calls with families. Some of these might be billable with 99441-3 or G2012.
• Phone messages by nurses might reflect 99490 (chronic care management) if >=20 minutes is spent in a calendar month. 
• Phone messages sent from families through the portal will show as being from “Web Portal User.”  If answered by the provider and time thresholds are met, then this might be billable with 9942x.

SQL Code: Firebird

To highlight and copy the code below to your clipboard, simply click the Copy button.

Copy

select msg_id, register.ins_carrier_Code, register.ins2_carrier_code, staffname as msg_author, patno, fname, lname, subject, cast(text as char(30000)) as txt, call_time, duration, responses from			
esmg_org_msg e			
inner join register on e.pat_id =register.patno			
inner join staff1 on staff1.staffid = e.addr_id			
left outer join (select count(msg_id) as responses, parent_id from esmg_org_msg where parent_id >0 and date1>= :Start_date group by parent_id) f on f.parent_id = e.msg_id			
where e.date1 between :Start_date and :end_date and (e.parent_id is null or e.parent_id <1)

SQL Code: MySQL

To highlight and copy the code below to your clipboard, simply click the Copy button.

Copy

select msg_id, register.ins_carrier_Code, register.ins2_carrier_code, staffname as msg_author, patno, fname, lname, subject, text, call_time, duration, responses from
 esmg_org_msg e
inner join register on e.pat_id =register.patno
inner join staff1 on staff1.staffid = e.addr_id  
left outer join (select count(msg_id) as responses, parent_id from esmg_org_msg where parent_id >0 and date1>= :start_date group by parent_id) f on f.parent_id = e.msg_id
where e.date1 between :start_date and :end_date and (e.parent_id is null or e.parent_id <1)