Help with Query

For complex topics that regular users would not be interested in. For power users and database administrators.
Post Reply
User avatar
wjstarck
Posts: 935
Joined: Tue Jul 31, 2007 7:18 am
Location: Keller, TX
Contact:

Help with Query

Post by wjstarck » Thu Jan 19, 2023 12:17 pm

I want to display ADA Code, Description and Fee for a fee schedule for a range of codes.

This query gives me the ADA Code and Description but I am having trouble figuring out how to change the query to add the fee.
SELECT ProcCode AS Code, Descript AS Description FROM procedurecode INNER JOIN fee ON fee.CodeNum = procedurecode.CodeNum WHERE ProcCode >= 'D7140' AND ProcCode <= 'D7953' AND fee.FeeSched = 53 ORDER BY Code Asc
Cheers,

Bill Starck, DDS
Big Idea Software, LLC
Developer, EASy(Electronic Anesthesia System) for Open Dental
817-807-1709
TX, USA

allends
Posts: 235
Joined: Fri Aug 23, 2013 11:29 am

Re: Help with Query

Post by allends » Fri Jan 20, 2023 8:34 am

Your query is pretty close to selecting all the information you need.
I have edited the query to add in a select for the fee.Amount as well as a couple of Where-clause additions to narrow down your fee to the clinic and provider default (0 clinic/prov). If you need provider and clinic overrides, just change the values on those Where-clauses to the appropriate primary keys.
Hope this helps!

Code: Select all

SELECT
  ProcCode AS CODE,
  Descript AS Description,
  fee.Amount AS Fee
FROM
  procedurecode
  INNER JOIN fee
    ON fee.CodeNum = procedurecode.CodeNum
WHERE ProcCode >= 'D7140'
  AND ProcCode <= 'D7953'
  AND fee.FeeSched = 53
  AND fee.ProvNum=0
  AND fee.ClinicNum=0
ORDER BY CODE ASC;
Allen
Open Dental Software
http://www.opendental.com

User avatar
wjstarck
Posts: 935
Joined: Tue Jul 31, 2007 7:18 am
Location: Keller, TX
Contact:

Re: Help with Query

Post by wjstarck » Fri Jan 20, 2023 9:23 am

That works.

Thanks Allen
Cheers,

Bill Starck, DDS
Big Idea Software, LLC
Developer, EASy(Electronic Anesthesia System) for Open Dental
817-807-1709
TX, USA

Post Reply