Adult Prophy Query by date

[Tom Zaccaria]

Drawing a blank and need some help. Tries unsuccessfully to cobble a couple of examples together but no luck.

I need a report that will return the following:
Pt Lname, Pt Fname, Pt address, PtEmail address,
date of last adult prophy (D1110),
current insurance plan,
for a given date range

Any help or direction will be appreciated.

drtmz

[Tom Zaccaria]

Was finally able to re-engineer query #264 to do what I wanted.
Now just need to split PatNum into First Name and Last Name

drtmz

[Tom Zaccaria]

Smokin Hot Sunday.

I'm gonna quit while I'm ahead or at least before one of you show me this query done already somewhere else.
But here it is;

/*264 List of procedures for a date range with patient name, service date, procedure code,
primary insurance carrier, fee, sum of ins est, sum ins paid*/
/*Modified to give the date of the patient's last prophy and insurance plan*/
/*Also eliminates managed care from report and gives patient first name and patient last name*/

SET @FromDate='2009-01-01' , @ToDate='2009-1-31'; /* change date here, change procedure codes below*/
SELECT p.PatNum, p.LName,p.FName, pl.ProcDate, pc.ProcCode,CarrierName AS 'PriInsName',pl.ProcFee,
(SELECT SUM(cp.InsPayEst) FROM claimproc cp WHERE cp.ProcNum=pl.ProcNum AND cp.Status IN (0,1,4)) AS '$InsPayEst',
(SELECT SUM(cp.InsPayAmt) FROM claimproc cp WHERE cp.ProcNum=pl.ProcNum AND cp.Status IN (1,4)) AS '$InsPaidAmt'
FROM procedurelog pl
INNER JOIN patient p ON p.PatNum=pl.PatNum /*just in case we need field from patient table*/
INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
LEFT JOIN patplan pp ON pp.PatNum=p.PatNum AND ORDINAL=1/*by current primary insurance*/
LEFT JOIN inssub iss ON pp.InsSubNum=iss.InsSubNum
LEFT JOIN insplan ip ON ip.PlanNum=iss.PlanNum
LEFT JOIN carrier ON carrier.CarrierNum=ip.CarrierNum
WHERE pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus=2 AND pc.ProcCode IN('D1110')
AND CarrierName <> 'Aetna DMO'
AND CarrierName <> 'Cigna DHMO'
AND CarrierName <> 'UCCI DHMO'
GROUP BY pl.ProcNum
ORDER BY pl.ProcDate, p.LName,p.FName;

drtmz

[Hersheydmd]

Good Job!!!

[Tom Zaccaria]

Next step in to incorporate your date interval code.
Should be simple enough.
The idea is to get a report that gives a list of patients who are 6, 9, 12 and 18 months over due for their prophies
Run it the first of the month and you have a list to contact by post card, letter email or whatever.

drtmz

[Tom Zaccaria]

Latest revision using the interval command.

/*264 List of procedures for a date range with patient name, service date, procedure code,
primary insurance carrier, fee, sum of ins est, sum ins paid*/
/* Revised to seek date of last prophy*/
/*Just change the interval values, months, below to get the proper month*/

SET @FromDate=Date_Sub(CurDate(),Interval 8 month),
@ToDate=Date_Sub(CurDate(),Interval 7 month);
SELECT p.PatNum, p.LName,p.FName, pl.ProcDate, pc.ProcCode,CarrierName AS 'PriInsName',pl.ProcFee,
(SELECT SUM(cp.InsPayEst) FROM claimproc cp WHERE cp.ProcNum=pl.ProcNum AND cp.Status IN (0,1,4)) AS '$InsPayEst',
(SELECT SUM(cp.InsPayAmt) FROM claimproc cp WHERE cp.ProcNum=pl.ProcNum AND cp.Status IN (1,4)) AS '$InsPaidAmt'
FROM procedurelog pl
INNER JOIN patient p ON p.PatNum=pl.PatNum /*just in case we need field from patient table*/
INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
LEFT JOIN patplan pp ON pp.PatNum=p.PatNum AND ORDINAL=1/*by current primary insurance*/
LEFT JOIN inssub iss ON pp.InsSubNum=iss.InsSubNum
LEFT JOIN insplan ip ON ip.PlanNum=iss.PlanNum
LEFT JOIN carrier ON carrier.CarrierNum=ip.CarrierNum
WHERE pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus=2 AND pc.ProcCode IN('D1110')
AND CarrierName <> 'Aetna DMO'
AND CarrierName <> 'Cigna DHMO'
AND CarrierName <> 'UCCI DHMO'
GROUP BY pl.ProcNum
ORDER BY pl.ProcDate, p.LName,p.FName;

drtmz

[Hersheydmd]

Very nice! The next step is to export the result to a file that can be imported into a GoogleDoc mail merge e-mail. I'll try to post instructions by next week.
I've been frustrated by OD's ability to give us only one confirmation e-mail, when I would like to send out at least three.

• One, a "hold the date reminder", at least a month before the appointment for patients on recall who scheduled the appt. at their last recall, which could be anywhere from 3-12 months ago.

• Two, the regular confirmation e-mail a few days before the appt. which we do from within OD.

• And three, a last minute, SMS text reminder the night before or morning of the appt. for patients who are not that careful and tend to forget even though they confirmed.

I have also set up a query and GoogleDoc to give me all the patients with emails or cells that were in during the previous week, so we can send them a follow-up "Thank You".
And to think, looking at queries was like looking at a foreign language just a few months ago.

[Tom Zaccaria]

This works better for eliminating managed care from the report

/*264 List of procedures for a date range with patient name, service date, procedure code,
primary insurance carrier, fee, sum of ins est, sum ins paid*/

/*Just change the interval values below to get the proper month*/
SET @FromDate=Date_Sub(CurDate(),Interval 8 month),
@ToDate=Date_Sub(CurDate(),Interval 7 month);

SELECT p.PatNum, p.LName,p.FName, pl.ProcDate, pc.ProcCode,CarrierName AS 'PriInsName',pl.ProcFee,
(SELECT SUM(cp.InsPayEst) FROM claimproc cp WHERE cp.ProcNum=pl.ProcNum AND cp.Status IN (0,1,4)) AS '$InsPayEst',
(SELECT SUM(cp.InsPayAmt) FROM claimproc cp WHERE cp.ProcNum=pl.ProcNum AND cp.Status IN (1,4)) AS '$InsPaidAmt'
FROM procedurelog pl
INNER JOIN patient p ON p.PatNum=pl.PatNum /*just in case we need field from patient table*/
INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
LEFT JOIN patplan pp ON pp.PatNum=p.PatNum AND ORDINAL=1/*by current primary insurance*/
LEFT JOIN inssub iss ON pp.InsSubNum=iss.InsSubNum
LEFT JOIN insplan ip ON ip.PlanNum=iss.PlanNum
LEFT JOIN carrier ON carrier.CarrierNum=ip.CarrierNum
WHERE pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus=2 AND pc.ProcCode IN('D1110')
AND CarrierName not like '%DMO%'
AND CarrierName not like '%DHMO%'
GROUP BY pl.ProcNum
ORDER BY pl.ProcDate, p.LName,p.FName;

[Tom Zaccaria]

Just one problem remains.
This does correctly show who had an adult prophy 7 to 8 months ago or whatever interval you put in. But it does not take into account if they have had one since then and therefore not be due again or for those patients that have been in in six months and one day. Been fiddling with it but no luck. Any help would be appreciated because I'm drawing a blank.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/*264 List of procedures for a date range with patient name, service date, procedure code,
primary insurance carrier, fee, sum of ins est, sum ins paid*/

/*Just change the interval values below to get the proper month*/
SET @FromDate=Date_Sub(CurDate(),Interval 8 month),
@ToDate=Date_Sub(CurDate(),Interval 7 month);

SELECT p.PatNum, p.LName,p.FName, pl.ProcDate, pc.ProcCode,CarrierName AS 'PriInsName',pl.ProcFee,
(SELECT SUM(cp.InsPayEst) FROM claimproc cp WHERE cp.ProcNum=pl.ProcNum AND cp.Status IN (0,1,4)) AS '$InsPayEst',
(SELECT SUM(cp.InsPayAmt) FROM claimproc cp WHERE cp.ProcNum=pl.ProcNum AND cp.Status IN (1,4)) AS '$InsPaidAmt'

FROM procedurelog pl
INNER JOIN patient p ON p.PatNum=pl.PatNum /*just in case we need field from patient table*/
INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
LEFT JOIN patplan pp ON pp.PatNum=p.PatNum AND ORDINAL=1/*by current primary insurance*/
LEFT JOIN inssub iss ON pp.InsSubNum=iss.InsSubNum
LEFT JOIN insplan ip ON ip.PlanNum=iss.PlanNum
LEFT JOIN carrier ON carrier.CarrierNum=ip.CarrierNum
WHERE pl.ProcDate
BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus=2 AND pc.ProcCode IN('D1110')
AND CarrierName not like '%DMO%'
AND CarrierName not like '%DHMO%'
GROUP BY pl.ProcNum
ORDER BY pl.ProcDate, p.LName,p.FName;

[Tom Zaccaria]

Update. This query correctly shows who had an adult prophy 7 to 8 months ago or whatever interval you put in. And now takes into account the fact that may have been in since that time or may have one scheduled already.
Couldn't get it on my own so went to support, paid a few bucks and here it is. If you think it is useful, give it a try.

/*264 List of procedures for a date range with patient name, service date, procedure code,
primary insurance carrier, fee, sum of ins est, sum ins paid*/
/*Just change the interval values below to get the proper month*/
SET @FromDate=Date_Sub(CurDate(),Interval 8 month),@ToDate=Date_Sub(CurDate(),Interval 7 month);
SELECT p.LName,p.FName, p.email,pl.ProcDate, pc.ProcCode,CarrierName AS 'PriInsName',
(SELECT SUM(cp.InsPayAmt) FROM claimproc cp WHERE cp.ProcNum=pl.ProcNum AND cp.Status IN (1,4)) AS '$InsPaidAmt'
FROM procedurelog pl
INNER JOIN patient p ON p.PatNum=pl.PatNum /*just in case we need field from patient table*/
INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
LEFT JOIN patplan pp ON pp.PatNum=p.PatNum AND ORDINAL=1/*by current primary insurance*/
LEFT JOIN inssub iss ON pp.InsSubNum=iss.InsSubNum
LEFT JOIN insplan ip ON ip.PlanNum=iss.PlanNum
LEFT JOIN carrier ON carrier.CarrierNum=ip.CarrierNum
WHERE pl.ProcDate BETWEEN @FromDate AND @ToDate
AND pl.ProcStatus=2 AND pc.ProcCode IN('D1110')
AND CarrierName not like '%DMO%'
AND CarrierName not like '%DHMO%'
AND CarrierName not like '%Delta Care%'
AND CarrierName not like '%Aetna Medicare%'
AND p.PatNum NOT IN(SELECT DISTINCT(pl.PatNum) FROM procedurelog pl
INNER JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum AND pc.ProcCode IN('D1110')
LEFT JOIN appointment a ON a.AptNum=pl.AptNum
WHERE ((pl.ProcDate>@ToDate AND pl.ProcStatus=2)
OR (a.AptStatus=1 AND a.AptDateTime>=CURDATE())))
GROUP BY pl.ProcNum
ORDER BY pl.ProcDate, p.LName,p.FName;


drtmz