Page 1 of 1
How many days did i work?
Posted: Thu May 01, 2014 12:24 am
by aussiedentist
Is there any easy way of seeing how many days I had patients booked?
I am looking at finding my daily production amount, but i can only guess at how many days I worked in the last 4 months!!
I dont want to look at payments, as sometimes payments might come through when I am not physically at work
Re: How many days did i work?
Posted: Thu May 01, 2014 11:00 am
by efeuer
I'm sure that there are more efficient ways to do this, but one way might be to run a canned daily report for Procedures; choosing only the designated dates and providers, and then export the report to excel.
Once your in excel, you could grab the column for dates and then remove duplicates / grab a count of unique values.
http://office.microsoft.com/en-us/excel ... 73943.aspx (something like this)
Re: How many days did i work?
Posted: Thu May 01, 2014 4:41 pm
by tgriswold
This will tell you the number of days that you had a completed procedure between the two dates that you set at the top.
Code: Select all
/*Number of days worked in date range (days where procedures were completed)*/
SET @FromDate='2014-04-29', @ToDate='2014-04-29';
SELECT COUNT(DISTINCT pl.ProcDate) AS 'Number of days worked'
FROM procedurelog pl
WHERE pl.ProcStatus=2
AND pl.ProcDate BETWEEN @FromDate AND @ToDate;
Alternatively, this will show the number of days you had an appointment completed between the two dates set at the top of the query.
Code: Select all
/*Number of days worked in date range (days where appointments were completed)*/
SET @FromDate='2014-05-01', @ToDate='2014-05-01';
SELECT COUNT(DISTINCT DATE(ap.AptDateTime)) AS 'Number of days worked'
FROM appointment ap
WHERE ap.AptStatus=2
AND DATE(ap.AptDateTime) BETWEEN @FromDate AND @ToDate;
Re: How many days did i work?
Posted: Fri May 02, 2014 3:21 am
by Tom Zaccaria
I have been searching for one of these forever. Now if we could just modify the first one to include half days that would be even better.
Now it counts a half day as a full day. I need the distinction between the two as in 5.5 days.
drtmz
Re: How many days did i work?
Posted: Fri May 02, 2014 3:36 am
by Tom Zaccaria
even better yet would be full days and half days totaled over a date range by provider, such as;
Dr H 5.0
Dr K 6.5
Dr T 6.0
Hyg 1 3.5
drtmz
Re: How many days did i work?
Posted: Fri May 02, 2014 6:00 am
by Tom Zaccaria
Ok I tweaked the second qurey to list all the ops used for the month of April 2014
/*Number of days worked in date range (days where appointments were completed)*/
/* Z=4, L =1*/
SET @FromDate='2014-04-01', @ToDate='2014-04-31';
SELECT COUNT(DISTINCT DATE(ap.AptDateTime)) AS 'Number of days worked'
FROM appointment ap
WHERE ap.AptStatus=2
AND DATE(ap.AptDateTime) BETWEEN @FromDate AND @ToDate
group by op;
This returns the following:
Number of days worked
13
13
12
12
9
12
How can I add the providers to this output?
Dr K...13
Dr K...13
Dr J...12
Dr J...12
etc
Thanks
drtmz
Re: How many days did i work?
Posted: Fri May 02, 2014 7:25 am
by allends
Code: Select all
SET @FromDate='2014-04-01', @ToDate='2014-04-31';
SELECT pv.Abbr, COUNT(DISTINCT DATE(ap.AptDateTime)) AS 'Number of days worked'
FROM appointment ap, provider pv
WHERE ap.AptStatus=2
AND ap.ProvNum=pv.ProvNum
AND DATE(ap.AptDateTime) BETWEEN @FromDate AND @ToDate
GROUP BY Abbr;
This should solve your problem. I Used Abbreviation, but you can use FName and LName if you want.