2

I have built an appointments system for various medical practices. Practice schedule appointments - I am looking to provide analysis on the amount of time that has been utilised by the practice.

I can break up my requirements into lots of smaller queries in order to calculate the percentage of available time that has been utilised by the practice.

e.g A query to calculate the total available time that has been allocated to each practice for a given week:

# Get the practice and number of working hours they have per week allocated to them
SELECT
    pd.practice_id,
    sum((TIME_TO_SEC(TIMEDIFF(pd.close,pd.open)) - TIME_TO_SEC(TIMEDIFF(pd.lunch_stop, pd.lunch_start)) ) / 3600 ) as working_hours_per_week
FROM
    practice_days as pd
GROUP BY
    practice_id;

Then for each practice, I can sum the appointment lengths which are in the completed status in a given date range (e.g. last week) to see how many hours have been scheduled and completed for that week.

Finally - I can divide the time used by the working hrs per week to get a percentage utilisation.

Already this is a little hairy - however what happens when I want to look at a different weeks / months or all historical etc.

Is there a better cleaner way of analysing this data? e.g. a decent reporting system of some sort. I am using PHP mysql and Laravel PHP framework.

I don't really want to be going down the route of setting up a Shiny R server just for this unless absolutely necessary.

  • 1
    GROUP BY the week as well. Or, use HAVING. Then apply your aggregates like SUM. You shouldn't need custom queries for each week. – Robert Harvey Dec 29 '14 at 20:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.