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