Hi All.
Hope someone can be of some help.
Not sure if some of the below queries are just old and the tables have changed with versions but some are just not working. Is this expected?
Some queries from http://opendentalsoft.com:1942/ODQueryL ... yList.aspx are not working for me.
Here are a couple of examples. Just thought id ask before editing and looking for more queries about the topic im looking to dive deeper on.
/*90 Sum of payments made by carrier for procedures in a date range*/
/*Query code written/modified: 10/12/2015*/
SET @FromDate='2017-01-01', @ToDate='2017-02-21' ;
SELECT carrier.CarrierName, patient.Guarantor,SUM(claimproc.InsPayAmt) AS '$InsPayAmt_'
FROM patient
INNER JOIN claimproc ON claimproc.PatNum=patient.PatNum
INNER JOIN insplan ON insplan.PlanNum=claimproc.PlanNum
INNER JOIN carrier ON carrier.CarrierNum=insplan.CarrierNum
WHERE patient.PatStatus=0
AND claimproc.ProcDate BETWEEN @FromDate AND @ToDate
AND (claimproc.Status=1 OR claimproc.Status=4)
GROUP BY patient.Guarantor, carrier.CarrierName
ORDER BY carrier.CarrierName;
/*69 Count of patients by Carrier with procedures
completed in date range*/
SET @FromDate='2017-01-01', @Todate='2017-02-21';
SELECT carrier.CarrierName, COUNT(DISTINCT claimproc.PatNum) AS 'Patients', SUM(claimproc.InsPayAmt) as 'InsPaidTotal'
FROM carrier
INNER JOIN insplan ON carrier.CarrierNum=insplan.CarrierNum
INNER JOIN claim ON insplan.PlanNum=claim.PlanNum
INNER JOIN claimproc ON claim.ClaimNum=claimproc.ClaimNum
INNER JOIN procedurelog ON claimproc.ProcNum=procedurelog.ProcNum
INNER JOIN procedurecode ON procedurelog.CodeNum=procedurecode.CodeNum
AND procedurelog.ProcDate >= @FromDate
AND procedurelog.ProcDate < @ToDate
AND ProcStatus=2
GROUP BY CarrierName
ORDER BY CarrierName;
Some queries found on opendentalsoft.com not working
Re: Some queries found on opendentalsoft.com not working
In what way are these queries not working? I ran them against the most recent version of Open Dental and they seem to be giving the correct results?
Re: Some queries found on opendentalsoft.com not working
Thanks for confirming. I am not getting any results from the queries i posted. I will call support to get more info then if you are able to get it working.