Calculate price based on duration
I am currently working on a database to store information about amount of time a staff member works, amount owed and amount paid in. My transactions are created automatically. The first stage is I am trying to work out how much is owed based ont he duration of time worked. The transactions are created automatically and the duration field is created in hh:mm:ss format and is already rounded to nearest minute.
The amount of time worked is based on time band groupings. Below is what I would essentially like to achieve:
less or equal to 36 minutes ; price 1
37 to 52 minutes ; price 2
52 - 66 minutes ; price 3...... etc
I have looked at using the Case function however how can I get it to work properly based on the format I have (due to volume of records it would be impractical to change the field values manually)