secondary claims not sent report?

[babysilvertooth]

What is the report or query to help find secondary claims that are 'on hold until Primary is returned' or secondary not sent? The outstanding claims report only shows claims sent but not returned....but how to track secondary claims that are not sent.

[Dwayne]

Here is a query from our website for secondary claims:

/* 158 Outstanding insurance claims by Date of Service, secondary claims ONLY*/
SELECT cl.PatNum,cl.DateService,cl.DateSent, ca.CarrierName, ca.Phone FROM claim cl
INNER JOIN patient p ON p.PatNum=cl.PatNum
INNER JOIN insplan i ON i.PlanNum=cl.PlanNum
INNER JOIN carrier ca ON ca.CarrierNum=i.CarrierNum
WHERE cl.ClaimStatus='S' AND
DateService<(CURDATE()-INTERVAL 30 DAY) AND
ClaimType='S'
ORDER BY ca.CarrierName,p.LName;

You can copy this and paste it into the User Query area in the Reports screen. If you need further assistance with the query or have more questions, please email service@opendental.com.

Here is the link for more queries should you need them: http://opendentalsoft.com:1942/ODQueryL ... yList.aspx

[atd]

Here's the query we use. It looks for secondary claims with a status of "Hold until Primary received" AND checks to make sure the primary has been received since you want to wait for that.

DROP TABLE IF EXISTS tmp1;

CREATE TABLE tmp1
(SELECT ClaimProc.ProcNum, claimproc.PatNum, claimproc.ProcDate, insplan.GroupName from claimproc
LEFT JOIN claim on claimproc.ClaimNum=claim.ClaimNum
LEFT JOIN insplan on claim.PlanNum=insplan.PlanNum
WHERE claim.claimStatus='H' and claim.ClaimType='S');

SELECT claimproc.PatNum, claimproc.ProcDate, tmp1.GroupName as Insurance, guarantor.FamFinUrgNote as PreviewNote from claimproc
LEFT JOIN claim on claimproc.ClaimNum=claim.ClaimNum
LEFT JOIN tmp1 on tmp1.ProcNum=claimproc.ProcNum
LEFT JOIN patient on claimproc.PatNum=patient.PatNum
LEFT JOIN patient as guarantor on patient.Guarantor=guarantor.PatNum
WHERE claim.ClaimStatus='R' and claim.ClaimType='P' AND tmp1.ProcNum IS NOT NULL
GROUP BY claimproc.PatNum, claimproc.ProcDate
ORDER BY claimproc.PatNum, claimproc.ProcDate;

DROP TABLE tmp1;