DevOps, CI/CD and Automation

I have a table that stores time slots.

1. One field is called available_time (and is a number field and need to be converted to hours/minutes).
2. There are 2 other date/time fields as well - these being start_time and end_time.


Here is where I am having problems.

Anything before 12 noon is AM, after is PM. If any slots run from AM to PM then I need to use the start and end times and split the time into AM and PM eg start 05/01/2012 07:30:00 and end 05/01/2012 13:04:00 I need to do 05/01/2012 12:00:00-05/01/2012 07:30:00 to get AM and 05/01/2012 13:04:00-05/01/2012 12:00:00 to get PM.

Below shows the kind of layout I'm looking for in my report.

Any help would be greatly appreciated.

PS I am using Oracle Database 11g and Oracle Developer Suite 10g (reports)

Team Leader: ID and Name
Senior: ID and Name

Carer ID Name Date AM PM Total
11111 Person 1 02/03/2012 2:20 3:30 5:50
11111 Person 1 03/03/2012 4:00 4:00 8:00

Carer ID Name Date AM PM Total
22222 Person 2 02/03/2012 3:00 3:00 6:00
22222 Person 2 03/03/2012 4:00 4:00 8:00

Team Leader: ID and Name
Senior: ID and Name

Carer ID Name Date AM PM Total
33333 Person 3 02/03/2012 3:00 3:00 6:00
33333 Person 3 03/03/2012 4:00 4:00 8:00