Need a Query

[Hersheydmd]

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.

[jsalmon]

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.

[Hersheydmd]

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;

[jsalmon]

You're the man!

[Tom Zaccaria]

Very useful. Should be included in the examples list.

drtmz

[jsalmon]

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.

[Hersheydmd]

That's working well for me.
Now I want to do production by operatory.
Can't find what table the operatory is in.

[bpcomp]

http://www.opendental.com/OpenDentalDoc ... #operatory

[Hersheydmd]

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;

[bpcomp]

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.