How many days did i work?

[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

[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)

[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;

[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

[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

[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

[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.