query for ppo/medicaid not seen in over 6 months

For users or potential users.
Post Reply
lethstang
Posts: 11
Joined: Sun Jan 29, 2017 12:49 pm

query for ppo/medicaid not seen in over 6 months

Post by lethstang » Wed Feb 08, 2017 1:27 pm

for our reactivation campaign. We would need to be able to export this list with the patients address to send them a postcard. We only want patients who are on some sort of ppo and medicaid (so not our patients without insurance).

Is this possible?

Tom Zaccaria
Posts: 353
Joined: Mon Feb 25, 2008 3:09 am

Re: query for ppo/medicaid not seen in over 6 months

Post by Tom Zaccaria » Wed Feb 08, 2017 4:47 pm

Looks like you want some kind of recall query.
Does this help? Change the date range in line 5.

/*1026 Recall info for patients due in date range that don't have any future scheduled appointment. Only shows patients with any insurance carrier, showing date of last visit*/
/*Change dates as needed in second line of query */
/*Query code written/modified: 11/17/2014*/
SET @pos=0;
SET @StartDate='2016-07-01' , @EndDate='2017-02-01';
SELECT @pos:=@pos+1 AS 'Count',p.LName, p.FName, r.DateDue, d.ItemName AS 'RecallStatus',rt.Description AS 'Type',
( SELECT CONVERT(DATE_FORMAT(MAX(ProcDate),'%m/%d/%Y'), CHAR)
FROM patient
INNER JOIN procedurelog ON procedurelog.PatNum=patient.PatNum
WHERE procedurelog.ProcStatus=2 AND patient.PatStatus=0 AND p.PatNum=patient.PatNum
GROUP BY procedurelog.PatNum
) AS 'LastVisit(any)', c.CarrierName
FROM patient p
INNER JOIN patplan pp ON pp.PatNum=p.PatNum
INNER JOIN inssub ib ON ib.InsSubNum=pp.InsSubNum
INNER JOIN insplan ip ON ip.PlanNum=ib.PlanNum
INNER JOIN carrier c ON c.CarrierNum=ip.CarrierNum
INNER JOIN recall r ON p.PatNum=r.PatNum
INNER JOIN recalltype rt ON rt.RecallTypeNum=r.RecallTypeNum
LEFT JOIN definition d ON r.RecallStatus=d.DefNum
WHERE (DateDue BETWEEN @StartDate AND @EndDate)
AND NOT EXISTS (
SELECT a.PatNum
FROM appointment a
WHERE DATE(a.AptDateTime)>=CURDATE()
AND a.AptStatus=1
AND a.PatNum=p.PatNum
)
ORDER BY p.LName, p.FName;

drtmz

lethstang
Posts: 11
Joined: Sun Jan 29, 2017 12:49 pm

Re: query for ppo/medicaid not seen in over 6 months

Post by lethstang » Thu Feb 09, 2017 6:18 am

youre a coding genius tom thankyou. Can we have it pull their address too? We are trying to send out postcards

Tom Zaccaria
Posts: 353
Joined: Mon Feb 25, 2008 3:09 am

Re: query for ppo/medicaid not seen in over 6 months

Post by Tom Zaccaria » Thu Feb 09, 2017 6:28 am

Thanks for the superlative. This is a modification of query #1026 from OD's query examples page. Not my code.
Try this:

/*1026 Recall info for patients due in date range that don't have any future scheduled appointment. Only shows patients with any insurance carrier, showing date of last visit*/
/*Change dates as needed in second line of query */
/*Query code written/modified: 11/17/2014*/
SET @pos=0;
SET @StartDate='2016-07-01' , @EndDate='2017-02-01';
SELECT @pos:=@pos+1 AS 'Count',p.LName, p.FName, p.address, p.city ,p.state, p.zip, r.DateDue, d.ItemName AS 'RecallStatus',rt.Description AS 'Type',
( SELECT CONVERT(DATE_FORMAT(MAX(ProcDate),'%m/%d/%Y'), CHAR)
FROM patient
INNER JOIN procedurelog ON procedurelog.PatNum=patient.PatNum
WHERE procedurelog.ProcStatus=2 AND patient.PatStatus=0 AND p.PatNum=patient.PatNum
GROUP BY procedurelog.PatNum
) AS 'LastVisit(any)', c.CarrierName
FROM patient p
INNER JOIN patplan pp ON pp.PatNum=p.PatNum
INNER JOIN inssub ib ON ib.InsSubNum=pp.InsSubNum
INNER JOIN insplan ip ON ip.PlanNum=ib.PlanNum
INNER JOIN carrier c ON c.CarrierNum=ip.CarrierNum
INNER JOIN recall r ON p.PatNum=r.PatNum
INNER JOIN recalltype rt ON rt.RecallTypeNum=r.RecallTypeNum
LEFT JOIN definition d ON r.RecallStatus=d.DefNum
WHERE (DateDue BETWEEN @StartDate AND @EndDate)
AND NOT EXISTS (
SELECT a.PatNum
FROM appointment a
WHERE DATE(a.AptDateTime)>=CURDATE()
AND a.AptStatus=1
AND a.PatNum=p.PatNum
)
ORDER BY p.LName, p.FName;

drtmz

rhaber123
Posts: 415
Joined: Fri Dec 11, 2009 12:09 pm

Re: query for ppo/medicaid not seen in over 6 months

Post by rhaber123 » Fri Feb 10, 2017 9:42 am

SELECT @pos:=@pos+1 AS 'Count',p.LName, p.FName, p.address, p.city ,p.state, p.zip, p.Email, p.WirelessPhone, r.DateDue, d.ItemName AS 'RecallStatus',rt.Description AS 'Type',

=======================================================================================================
replace this line in the above query and you will get the patients emails and cell phone numbers too.
======================================================================================================

Now you have the option to email your patients or text message them for free.

Export your file as an EXCEL file
Delete all the columns , and keep the email addresses only.
Save the file as CSV text file.
in Gmail you can create a Group
Import the emails into Gmail Group
Send an Email to the Group

Gmail contacts link
https://www.google.com/contacts/u/0/?cplus=1#contacts

or you can use many other ways to send emails to a group
*****do not send more than 500 emails per hour.

==============================================================================================

to send text messages to a group, you need to have all the cell numbers separated by a comma.

you can create your own FREE Google Voice account.
and go to this webpage
https://www.google.com/voice/b/0?gsessi ... GdNL#inbox
click on "text"
and enter 5 phone numbers at a time (not more), and your message.
if you send more than 20-30 ?? text messages per minute Google Voice may block you.
so send it slowly. it may take you 15 minutes to send all the text messages.

=================================================================================================
or you can purchase a software that will do that automatically

Post Reply