Need a Query

For users or potential users.
Post Reply
User avatar
Hersheydmd
Posts: 700
Joined: Sun May 03, 2009 9:12 pm

Need a Query

Post by Hersheydmd » Fri Mar 15, 2013 4:28 pm

Need a query that will return production for one hygienist last year, totaled by day of the week. In other words, what did she produce on Mondays, what did she produce on Thursdays, etc.
Robert M Hersh DMD, FAGD
Univ. of Penn 1982
Brooklyn, NY 11234
https://www.facebook.com/pages/Robert-M ... 1471599429

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: Need a Query

Post by jsalmon » Sat Mar 16, 2013 12:22 am

Breaking up the results by day is the tricky part of that request. I doubt there's anything like this in our query examples page. It'd be worth a look. At the very least, try to find one that breaks results down by day. Then piecing the two queries together wouldn't be difficult. Otherwise, it sounds simple enough that we could write it for you for a charge, if you so desired.
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

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

Re: Need a Query

Post by Hersheydmd » Mon Mar 18, 2013 8:28 pm

Jason,
Thanks. That was a good idea.
I found a query that totaled production by day.
I was able to modify it to give me results for a single provider.
Very cool.

Here it is:
/*Production and Average daily production for given provider and a given time period, summed by day of week and overall total*/
SET @FromDate='2012-01-01', @ToDate='2012-12-31',/*Change dates here*/
@Provider=9;/*Change Provider here */
DROP TABLE IF EXISTS tmp;
CREATE TABLE tmp
SELECT v.LName, pl.ProvNum, pl.ProcFee, pl.ProcDate, DAYOFWEEK(pl.ProcDate) AS Day
FROM provider v, procedurelog pl
WHERE v.ProvNum=pl.ProvNum AND pl.ProvNum=@Provider AND pl.ProcDate BETWEEN @FromDate AND @ToDate AND ProcStatus=2 /*Complete*/;
SELECT (CASE
WHEN Day=1 THEN 'Sunday'
WHEN Day=2 THEN 'Monday'
WHEN Day=3 THEN 'Tuesday'
WHEN Day=4 THEN 'Wednesday'
WHEN Day=5 THEN 'Thursday'
WHEN Day=6 THEN 'Friday'
WHEN Day=7 THEN 'Saturday' ELSE 'Unknown' END) AS 'DayOfWeek',
SUM(ProcFee) AS $Production,
SUM(ProcFee)/COUNT(DISTINCT ProcDate) AS $AveDailyProd
FROM tmp
GROUP BY Day
UNION ALL
SELECT Concat(LName, '-Total') AS 'DayOfWeek',
SUM(ProcFee) AS $Production,
SUM(ProcFee)/COUNT(DISTINCT ProcDate) AS $AveDailyProd
FROM tmp;
Robert M Hersh DMD, FAGD
Univ. of Penn 1982
Brooklyn, NY 11234
https://www.facebook.com/pages/Robert-M ... 1471599429

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: Need a Query

Post by jsalmon » Mon Mar 18, 2013 11:36 pm

You're the man!
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

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

Re: Need a Query

Post by Tom Zaccaria » Tue Mar 19, 2013 3:20 am

Very useful. Should be included in the examples list.

drtmz

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: Need a Query

Post by jsalmon » Tue Mar 19, 2013 9:00 am

It will need some minor tweaks like getting rid of the use of creating a temp table (has the possibility to harm replication) and the provider would be changed to use abbreviations as opposed to the provider PK. Not your average user will know what that is or how to get it. I'll definitely let the gentleman who manages the query examples page know though.
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

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

Re: Need a Query

Post by Hersheydmd » Wed Mar 20, 2013 1:44 pm

That's working well for me.
Now I want to do production by operatory.
Can't find what table the operatory is in.
Robert M Hersh DMD, FAGD
Univ. of Penn 1982
Brooklyn, NY 11234
https://www.facebook.com/pages/Robert-M ... 1471599429

bpcomp
Posts: 304
Joined: Mon Feb 27, 2012 7:30 am
Location: Tucson, AZ
Contact:

Re: Need a Query

Post by bpcomp » Wed Mar 20, 2013 2:07 pm


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

Re: Need a Query

Post by Hersheydmd » Wed Mar 20, 2013 9:33 pm

Thanks Ray. That helped.

This query will return production for a particular provider in a particular operatory, for each day of the week.

/*Production for given provider in a particular operatory, over a given time period, summed by day of week*/
SET @FromDate='2012-01-01', @ToDate='2012-12-31',/*Change dates here*/
@Provider=4,/*Change Provider here */ @Op=3;/*Change operatory here*/
DROP TABLE IF EXISTS tmp;
CREATE TABLE tmp
SELECT v.LName, pl.ProvNum, pl.ProcFee, pl.ProcDate, DAYOFWEEK(pl.ProcDate) AS Day
FROM provider v, procedurelog pl, appointment a
WHERE a.AptNum=pl.AptNum AND v.ProvNum=pl.ProvNum AND pl.ProvNum=@Provider AND a.Op=@Op AND pl.ProcDate BETWEEN @FromDate AND @ToDate AND ProcStatus=2 /*Complete*/;
SELECT (CASE
WHEN Day=1 THEN 'Sunday'
WHEN Day=2 THEN 'Monday'
WHEN Day=3 THEN 'Tuesday'
WHEN Day=4 THEN 'Wednesday'
WHEN Day=5 THEN 'Thursday'
WHEN Day=6 THEN 'Friday'
WHEN Day=7 THEN 'Saturday' ELSE 'Unknown' END) AS 'DayOfWeek',
SUM(ProcFee) AS $Production,
SUM(ProcFee)/COUNT(DISTINCT ProcDate) AS $AveDailyProd
FROM tmp
GROUP BY Day
UNION ALL
SELECT Concat(LName, '-Op', @Op,'-Total') AS 'DayOfWeek',
SUM(ProcFee) AS $Production,
SUM(ProcFee)/COUNT(DISTINCT ProcDate) AS $AveDailyProd
FROM tmp;
Robert M Hersh DMD, FAGD
Univ. of Penn 1982
Brooklyn, NY 11234
https://www.facebook.com/pages/Robert-M ... 1471599429

bpcomp
Posts: 304
Joined: Mon Feb 27, 2012 7:30 am
Location: Tucson, AZ
Contact:

Re: Need a Query

Post by bpcomp » Thu Mar 21, 2013 7:48 am

As a quick tutorial for anyone wanting to use this query in your own office, you need to run two other queries first to gather some information. First run:

SELECT * FROM provider

Change format from "human-readable" to "raw" and you will see the ProvNum. Next query to run is:

SELECT * FROM operatory

Now you can enter the provider and operatory numbers into the main query.

Post Reply