I have a column called Duration. It is entered by therapist for amount of time they spent with the client. it is presented to the staff as hh:mm (00:00). Most of the entries are in minutes.
You can enter 01:00 for one hour.
the issue is how to calculate the totals of minutes and hours.
If we have total of minutes, i have 5806 minutes - I divided by 60 but this gives 96.77. How would I then further refine the result to 97.17?
Second issue is the ones who do enter by the hour, how to I include those responses to the total? I am not sure this is best practice, I have been told that
in this case, just give them the 96.77 and they can work out the rest. I still would have the issue of those who enter by the hour not minutes. Some enter 60 but some may enter 1.
Does it require a strict policy that says, only enter minutes i don't think they have this in place.
SELECT 5806 / 50 AS hours, 5806 % 60 AS minutes
If you have only the two valid input formats: mmm and hh:mm, then you can do this to add them up:
SELECT SUM(CASE WHEN Duration LIKE '%:%' THEN LEFT(Duration, CHARINDEX(':') - 1) * 60 + SUBSTRING(Duration, CHARINDEX(':') + 1) ELSE Duration END AS total_minutes