help with query

For users or potential users.
Post Reply
babysilvertooth
Posts: 129
Joined: Sat Jun 12, 2010 3:18 pm

help with query

Post by babysilvertooth » Thu Nov 03, 2011 2:43 pm

IS there a query for: top 50 procedures by number. That is, the procedures that are done the most by quantity. (how many pros, crowns, etc).

User avatar
Hersheydmd
Posts: 703
Joined: Sun May 03, 2009 9:12 pm

Re: help with query

Post by Hersheydmd » Thu Nov 03, 2011 5:54 pm

Try this:
SET @FromDate='2011-01-01' , @ToDate='2011-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 50
Robert M Hersh DMD, FAGD
Univ. of Penn 1982
Brooklyn, NY 11234
https://www.facebook.com/pages/Robert-M ... 1471599429

babysilvertooth
Posts: 129
Joined: Sat Jun 12, 2010 3:18 pm

Re: help with query

Post by babysilvertooth » Mon Nov 07, 2011 8:35 am

Thanks!

HOw about one for: Pt's who have spent >$sum for the year.

User avatar
Hersheydmd
Posts: 703
Joined: Sun May 03, 2009 9:12 pm

Re: help with query

Post by Hersheydmd » Fri Nov 11, 2011 3:48 pm

babysilvertooth wrote:Thanks!

HOw about one for: Pt's who have spent >$sum for the year.
Put Production in the search field and you'll find many examples at:
http://70.90.133.65:1942/ODQueryList/QueryList.aspx
Robert M Hersh DMD, FAGD
Univ. of Penn 1982
Brooklyn, NY 11234
https://www.facebook.com/pages/Robert-M ... 1471599429

Post Reply