query help

For users or potential users.
Post Reply
sam-I-am
Posts: 85
Joined: Thu Aug 16, 2012 7:58 pm

query help

Post by sam-I-am » Wed Nov 09, 2016 6:12 am

Is there a query out there for number of each procedure performed over a given length of time? I'm pretty much retarded when it comes to writing sql queries. Yes I know I need to learn ;)
Sam Barr

User avatar
cmcgehee
Posts: 711
Joined: Tue Aug 25, 2015 5:06 pm
Location: Salem, Oregon

Re: query help

Post by cmcgehee » Wed Nov 09, 2016 8:11 am

You can use the built-in Daily Procedures report and group it by procedure code. This will tell you the number of procedures completed for the chosen time frame. If you need a twist on this report, let me know what it is, and I'll see if I can find it on the Query Examples page.
Chris McGehee
Open Dental Software
http://www.opendental.com

rhaber123
Posts: 415
Joined: Fri Dec 11, 2009 12:09 pm

Re: query help

Post by rhaber123 » Wed Nov 09, 2016 9:07 am

Top 100 procedures during a certain period of time:
===============================================

SET @FromDate='2016-01-01' , @ToDate='2016-12-31';
SELECT Count(*),pv.Abbr, pc.ProcCode, pc.AbbrDesc, AVG(pl.ProcFee) as '$Ave. Fee' , SUM(pl.ProcFee) AS '$Tot. Prod' FROM patient
INNER JOIN procedurelog pl ON patient.PatNum=pl.PatNum
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum
INNER JOIN provider pv ON pl.ProvNum=pv.ProvNum
WHERE pl.ProcStatus = '2' AND (pl.ProcDate >=@FromDate
AND pl.ProcDate <= @ToDate)
GROUP BY pc.ProcCode
ORDER BY Count(*)DESC Limit 100

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

Re: query help

Post by Tom Zaccaria » Wed Nov 09, 2016 1:25 pm

You may want to try query #1184

drtmz

sam-I-am
Posts: 85
Joined: Thu Aug 16, 2012 7:58 pm

Re: query help

Post by sam-I-am » Thu Nov 17, 2016 3:31 am

Thanks to all...#1184 was just what I wanted. But I still have to learn how to write these things myself! Sam Barr

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

Re: query help

Post by Tom Zaccaria » Thu Nov 17, 2016 8:15 am

No need. There are almost 1200 examples on the query page to fit most needs. Usually you can cobble something from the examples. If not, post to this group for added guidance.

drtmz

Post Reply