Hi everyone,
from a table recording dates to make reservations for instruments in my lab, I need to generate a simple (dynamic) report with 3 columns in which the total amount of hours reserved for each user for each instrumentation is calculated.
Each reservation record stores a DATE_START and a DATE_END in 'timestamp' format, thus the time for each record is a difference between 2 timestamps.
Here's an example:
| ID | APPLICANT | PRODUCT_ID | DATE_START | DATE_END |
| 1 | luca | 30 | 04-SEP-19 12.00.00.000000 PM | 04-SEP-19 02.00.00.000000 PM |
| 2 | mario | 31 | 04-SEP-19 01.00.00.000000 PM | 04-SEP-19 02.00.00.000000 PM |
| 3 | mario | 32 | 04-SEP-19 02.15.00.000000 PM | 04-SEP-19 04.00.00.000000 PM |
| 4 | luca | 31 | 05-SEP-19 01.00.00.000000 PM | 05-SEP-19 02.15.00.000000 PM |
| 5 | luca | 31 | 05-SEP-19 04.00.00.000000 PM | 05-SEP-19 04.15.00.000000 PM |
| 6 | mario | 30 | 05-SEP-19 01.00.00.000000 PM | 05-SEP-19 02.15.00.000000 PM |
| 7 | paolo | 31 | 06-SEP-19 10.00.00.000000 AM | 06-SEP-19 02.00.00.000000 PM |
| 8 | paolo | 31 | 06-SEP-19 03.00.00.000000 PM | 06-SEP-19 04.00.00.000000 PM |
| 9 | paolo | 30 | 06-SEP-19 05.00.00.000000 AM | 06-SEP-19 06.10.00.000000 PM |
| 10 | luca | 32 | 06-SEP-19 10.00.00.000000 AM | 06-SEP-19 02.00.00.000000 PM |
| 11 | luca | 32 | 06-SEP-19 02.00.00.000000 AM | 06-SEP-19 04.30.00.000000 PM |
| 12 | paolo | 31 | 07-SEP-19 01.00.00.000000 PM | 07-SEP-19 02.00.00.000000 PM |
| 13 | mario | 32 | 07-SEP-19 02.30.00.000000 PM | 07-SEP-19 04.00.00.000000 PM |
| 14 | mario | 31 | 07-SEP-19 05.10.00.000000 PM | 07-SEP-19 07.00.00.000000 PM |
| 15 | paolo | 30 | 07-SEP-19 09.00.00.000000 AM | 07-SEP-19 02.10.00.000000 PM |
The results should be like (I typed random numbers in HOURS column just for example) :
| APPLICANT | PRODUCT_ID | HOURS |
|---|
| Luca | 30 | 6,5 |
| Luca | 31 | 4 |
| Luca | 32 | 3 |
| Mario | 30 | 2,5 |
| Mario | 31 | 5 |
| Mario | 32 | 1,5 |
| Paolo | 30 | 7 |
| Paolo | 31 | 3 |
I used GROUP BY function to correlate users and products and calculated the difference between timestamps in hours but just for 1 record, e.g.:
select extract( hour from diff) hours
from (select DATE_END – DATE_START diff FROM RESERVATIONS where ID=411)
Any suggestion please?
Thank you
K