A query/report

For users or potential users.
Post Reply
sbrugg23
Posts: 6
Joined: Thu Dec 17, 2020 10:04 am

A query/report

Post by sbrugg23 » Thu Oct 07, 2021 7:44 am

Is there a query or report that will show the number of patients for each employer and the insurance carrier?

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

Re: A query/report

Post by Tom Zaccaria » Thu Oct 07, 2021 8:37 am

Does this help? Just change the recall type.
You can reorganize the list by columns by clicking on the column heading.

drtmz


/*807 List of patients who have a specific insurance or specific employer, and their interval for recall.*/
SET @InsCarrier='%%'; /*Set to '%%' to include all carriers*/
SET @Employer='%%'; /*Set to '%%' to include all employers*/
SET @RecallType='Perio'; /*Change recall type here.*/
SELECT A.Patient,
A.Age,
A.InsuranceCarrier AS 'Insurance Carrier',
A.Employer AS 'Employer',
CONCAT((CASE WHEN A.IntYear=0 THEN '' ELSE CONCAT(A.IntYear,'y ') END),
(CASE WHEN A.IntMonth=0 THEN '' ELSE CONCAT(A.IntMonth,'m ') END),
(CASE WHEN A.IntWeek=0 THEN '' ELSE CONCAT(A.IntWeek,'w ') END),
(CASE WHEN A.IntDay=0 THEN '' ELSE CONCAT(A.IntDay,'d') END)
) AS 'Recall Interval'
FROM (
SELECT CONCAT(p.LName,', ',p.FName) AS 'Patient',
(CASE WHEN (YEAR(CURDATE())-YEAR(Birthdate)) - (RIGHT(CURDATE(),5)<RIGHT(Birthdate,5))<120
THEN (YEAR(CURDATE())-YEAR(Birthdate)) - (RIGHT(CURDATE(),5)<RIGHT(Birthdate,5)) ELSE 'Not Entered' END) AS 'Age',
c.CarrierName AS 'InsuranceCarrier',
e.EmpName AS 'Employer',
CONV(SUBSTRING(LPAD(CONV(r.RecallInterval,10,2),31,0),1,7),2,10) AS 'IntYear',
CONV(SUBSTRING(LPAD(CONV(r.RecallInterval,10,2),31,0),8,8),2,10) AS 'IntMonth',
CONV(SUBSTRING(LPAD(CONV(r.RecallInterval,10,2),31,0),16,8),2,10) AS 'IntWeek',
CONV(SUBSTRING(LPAD(CONV(r.RecallInterval,10,2),31,0),24,8),2,10) AS 'IntDay'
FROM patient p
INNER JOIN patplan pp ON pp.PatNum=p.PatNum
INNER JOIN inssub ss ON pp.InsSubNum=ss.InsSubNum
INNER JOIN insplan ip ON ip.PlanNum=ss.PlanNum
INNER JOIN employer e ON e.EmployerNum=ip.EmployerNum
INNER JOIN carrier c ON ip.CarrierNum=c.CarrierNum
INNER JOIN recall r ON r.PatNum=p.PatNum
INNER JOIN recalltype rc ON r.RecallTypeNum=rc.RecallTypeNum
AND rc.Description LIKE @RecallType
WHERE e.EmpName LIKE @Employer
AND c.CarrierName LIKE @InsCarrier
GROUP BY r.RecallNum
ORDER BY p.LName, p.FName
) A

sbrugg23
Posts: 6
Joined: Thu Dec 17, 2020 10:04 am

Re: A query/report

Post by sbrugg23 » Thu Oct 07, 2021 8:40 am

Thanks for that information but what I am looking for is how many patients come from each employer and what that employer carries for insurance.

joes
Posts: 239
Joined: Tue Aug 13, 2019 12:41 pm

Re: A query/report

Post by joes » Thu Oct 07, 2021 8:55 am

The closest query on our Query Examples page is #67. It shows a count of subscribers for each employer, but it doesn't show carrier. You can find that here, https://opendentalsoft.com:1943/ODQuery ... yList.aspx. Our Query Team may be able to modify this query or write a different one for you. Here is a link to our query request form, https://opendentalsoft.com:1943/ODQuery ... tForm.aspx.
Joe Sullivan
Open Dental Software
http://www.opendental.com

Post Reply