Open Dental Forum

Is there a queary or report to break down new patients per month (or another timeframe) and label beside them what insurance they have? It could be per patient or a summary of x patients with Delta insurance, Y patients with medicaid, Z patients with bluecross blue shield. I've used queary 1245 I think but it's not quite what I was looking for. I have 0 skills in writing my own quearies for what it's worth. I can change dates but that's about it.

I think this is what you are after. It is currently set for 2020. We use it all the time.
drtmz

/*Annual new patient count by Carrier broken down by month */
/*Query code written/modified: 08/11/2016*/
SET @Year='2020'; /*choose year, use 4 digits - i.e. 'yyyy' */
SELECT IFNULL(ca.CarrierName,'NONE') AS CarrierName,
SUM(CASE WHEN MONTH(a.ProcDate)=1 THEN 1 ELSE 0 END) AS 'JAN',
SUM(CASE WHEN MONTH(a.ProcDate)=2 THEN 1 ELSE 0 END) AS 'FEB',
SUM(CASE WHEN MONTH(a.ProcDate)=3 THEN 1 ELSE 0 END) AS 'MAR',
SUM(CASE WHEN MONTH(a.ProcDate)=4 THEN 1 ELSE 0 END) AS 'APR',
SUM(CASE WHEN MONTH(a.ProcDate)=5 THEN 1 ELSE 0 END) AS 'MAY',
SUM(CASE WHEN MONTH(a.ProcDate)=6 THEN 1 ELSE 0 END) AS 'JUN',
SUM(CASE WHEN MONTH(a.ProcDate)=7 THEN 1 ELSE 0 END) AS 'JUL',
SUM(CASE WHEN MONTH(a.ProcDate)=8 THEN 1 ELSE 0 END) AS 'AUG',
SUM(CASE WHEN MONTH(a.ProcDate)=9 THEN 1 ELSE 0 END) AS 'SEP',
SUM(CASE WHEN MONTH(a.ProcDate)=10 THEN 1 ELSE 0 END) AS 'OCT',
SUM(CASE WHEN MONTH(a.ProcDate)=11 THEN 1 ELSE 0 END) AS 'NOV',
SUM(CASE WHEN MONTH(a.ProcDate)=12 THEN 1 ELSE 0 END) AS 'DEC',
Concat ('Tot :',Sum(Case WHEN YEAR (a.ProcDate)=@Year Then 1 Else 0 END)) as 'Total'
FROM patient p
LEFT JOIN patplan pp ON pp.PatNum=p.PatNum
AND pp.Ordinal=1
LEFT JOIN inssub ins ON ins.InsSubNum=pp.InsSubNum
LEFT JOIN insplan ip ON ip.PlanNum=ins.PlanNum
LEFT JOIN carrier ca ON ca.CarrierNum=ip.CarrierNum
LEFT JOIN (
SELECT p.PatNum, MIN(pl.ProcDate) AS ProcDate
FROM patient p
INNER JOIN procedurelog pl ON p.PatNum=pl.PatNum
WHERE pl.ProcStatus=2/*Complete*/
GROUP BY p.PatNum
HAVING YEAR(MIN(pl.ProcDate))=@Year
)a ON p.PatNum=a.PatNum
GROUP BY CarrierName
ORDER BY NOT ISNULL(ca.CarrierName),ca.CarrierName

Hi dpat08, you mentioned that the report could be per patient or a summary. Dr. Zaccaria provided a summary style query above, but if you are also looking for a per patient report, query #1337 from our query examples page, https://opendentalsoft.com:1943/ODQuery ... yList.aspx, might be useful to you as well. It has variables at the top for filtering by date or provider, and a third variable for excluding patients with no production.



/*1337 New Patients Report including information from first visit and more*/
/*Like internal report version 17.4*/
/*Query code written/modified: 04/17/2018*/

/*Change dates between the ' ' in format 'YYYY-mm-dd'*/
SET @FromDate='2020-05-01', @ToDate='2020-05-31';
SET @Provs = ''; -- Add Provider Abbreviations separated by a | to filter by, or leave '' to run for all
SET @ExcludeNoProd = 'NO'; -- Change to YES to Exclude patients with no production, NO to include


/*---------------------DO NOT MODIFY BELOW THIS LINE---------------------*/
SET @Provs=(CASE WHEN @Provs="" THEN ".*" ELSE CONCAT('^',REPLACE(@Provs,"|","$|^"),"$") END);
SET @pos = 0;
SELECT @pos:=@pos+1 AS '#',
result.PatNum,
DATE_FORMAT(result.Date,'%m/%d/%Y') AS 'First Visit',
result.Procedures,
result.FirstVisitFee AS '$FirstVisitFee__',
result.$HowMuch__ AS '$Production__',
result.Providers,
result.Carriers AS Carriers,
result.HmPhone AS 'Home Phone',
result.WirelessPhone AS 'Wireless Phone',
result.Referral AS 'Referred From',
result.NextScheduledAppt AS 'Next Scheduled Appt'
FROM (
SELECT dateFirstProc AS 'Date',
patient.PatNum,
patient.LName,
patient.FName,
patient.Preferred,
patient.HmPhone,
patient.WirelessPhone,
patient.Address,
patient.Address2,
patient.City,
patient.State,
patient.Zip,
nextvisit.ApptDate,
GROUP_CONCAT(DISTINCT IF(procedurelog.ProcDate = table1.dateFirstProc,pc.ProcCode,NULL)ORDER BY pc.ProcCode SEPARATOR ', ') AS Procedures,
GROUP_CONCAT(DISTINCT IF(procedurelog.ProcDate = table1.dateFirstProc,(SELECT Abbr FROM provider WHERE ProvNum = procedurelog.ProvNum),NULL)ORDER BY procedurelog.ProvNum SEPARATOR ', ') AS Providers,
SUM(IF(procedurelog.ProcDate = table1.dateFirstProc,(procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits)),0)) AS FirstVisitFee,
car.Carriers AS Carriers,
CONCAT(referral.LName,IF(referral.FName='','',', '),referral.FName) Referral,
COALESCE(DATE_FORMAT(nextvisit.ApptDate,'%m/%d/%Y'),'None') AS NextScheduledAppt,
SUM(procedurelog.ProcFee*(procedurelog.UnitQty+procedurelog.BaseUnits)) $HowMuch__
FROM (
SELECT PatNum,
MIN(ProcDate) dateFirstProc
FROM procedurelog
INNER JOIN procedurecode ON procedurecode.CodeNum=procedurelog.CodeNum
AND ProcCode NOT IN ('D9986','D9987') /*Do not count missed or canceled appointments*/
WHERE ProcStatus = 2 /*Complete*/
GROUP BY PatNum
HAVING dateFirstProc BETWEEN @FromDate AND @ToDate
)table1
INNER JOIN patient ON table1.PatNum=patient.PatNum
LEFT JOIN procedurelog ON patient.PatNum=procedurelog.PatNum
AND procedurelog.ProcStatus = 2 /*Complete*/
LEFT JOIN procedurecode pc ON procedurelog.CodeNum = pc.CodeNum
AND pc.ProcCode NOT IN ('D9986','D9987')
LEFT JOIN refattach ON patient.PatNum=refattach.PatNum
AND refattach.RefType = 1 /*RefFrom*/
AND refattach.ItemOrder=(
SELECT MIN(ra.ItemOrder)
FROM refattach ra
WHERE ra.PatNum=refattach.PatNum
AND ra.RefType = 1 /*RefFrom*/
)
LEFT JOIN referral ON referral.ReferralNum=refattach.ReferralNum
LEFT JOIN (
SELECT ap.PatNum,MIN(DATE(ap.AptDateTime)) AS ApptDate
FROM appointment ap
WHERE ap.AptStatus = 1 /*Scheduled*/
AND DATE(ap.AptDateTime) >= CURDATE()
GROUP BY ap.PatNum
) nextvisit
ON nextvisit.PatNum=table1.PatNum
INNER JOIN provider pv ON patient.PriProv = pv.ProvNum
LEFT JOIN (
SELECT GROUP_CONCAT(DISTINCT ca.CarrierName SEPARATOR ' | ') Carriers,
pp.PatNum
FROM patplan pp
LEFT JOIN inssub iss ON iss.InsSubNum = pp.InsSubNum
LEFT JOIN insplan ip ON ip.PlanNum = iss.PlanNum
LEFT JOIN carrier ca ON ip.CarrierNum = ca.CarrierNum
GROUP BY pp.PatNum
) car ON car.PatNum = patient.PatNum
WHERE pv.Abbr REGEXP @Provs
GROUP BY patient.PatNum
ORDER BY dateFirstProc,patient.LName,patient.FName
) result
WHERE (CASE WHEN @ExcludeNoProd = 'YES' THEN $HowMuch__ > 0 ELSE TRUE END)

Both of those are phenominal. Thanks a ton!