Dear friends of this forum,
I have several users and several departments. Each user can work in different departments. In my_table I store these data:
-
the user
-
the department in which the user has worked that day
-
Start time
-
End time
CREATE table "MY_TABLE" (
"USER\_NAME" VARCHAR2(20) NOT NULL,
"DEPARTMENT" VARCHAR2(20) NOT NULL,
"START\_JOB" DATE NOT NULL,
"END\_JOB" DATE NOT NULL
);
I saved these example data:
INSERT INTO my_table VALUES ('Mary', 'Department01', to_date ('01/11/2016 09:30', 'dd/mm/yyyy hh24:mi'), to_date ('01/11/2016 12:45', 'dd/mm/yyyy hh24:mi'));
INSERT INTO my_table VALUES ('John', 'Department03', to_date ('01/11/2016 07:35', 'dd/mm/yyyy hh24:mi'), to_date ('01/11/2016 11:20', 'dd/mm/yyyy hh24:mi'));
INSERT INTO my_table VALUES ('Mary', 'Department02', to_date ('02/11/2016 07:40', 'dd/mm/yyyy hh24:mi'), to_date ('02/11/2016 10:50', 'dd/mm/yyyy hh24:mi'));
INSERT INTO my_table VALUES ('David', 'Department01', to_date ('01/11/2016 13:30', 'dd/mm/yyyy hh24:mi'), to_date ('01/11/2016 15:20', 'dd/mm/yyyy hh24:mi'));
INSERT INTO my_table VALUES ('Francisco', 'Department02', to_date ('02/11/2016 09:35', 'dd/mm/yyyy hh24:mi'), to_date ('02/11/2016 13:40', 'dd/mm/yyyy hh24:mi'));
INSERT INTO my_table VALUES ('Margareth', 'Department02', to_date ('02/11/2016 06:25', 'dd/mm/yyyy hh24:mi'), to_date ('02/11/2016 11:30', 'dd/mm/yyyy hh24:mi'));
INSERT INTO my_table VALUES ('Domenic', 'Department03', to_date ('03/11/2016 08:00', 'dd/mm/yyyy hh24:mi'), to_date ('03/11/2016 12:30', 'dd/mm/yyyy hh24:mi'));
INSERT INTO my_table VALUES ('Mary', 'Department01', to_date ('03/11/2016 09:00', 'dd/mm/yyyy hh24:mi'), to_date ('03/11/2016 12:40', 'dd/mm/yyyy hh24:mi'));
INSERT INTO my_table VALUES ('Francisco', 'Department03', to_date ('03/11/2016 12:40', 'dd/mm/yyyy hh24:mi'), to_date ('03/11/2016 17:25', 'dd/mm/yyyy hh24:mi'));
INSERT INTO my_table VALUES ('John', 'Department02', to_date ('02/11/2016 09:35', 'dd/mm/yyyy hh24:mi'), to_date ('02/11/2016 11:30', 'dd/mm/yyyy hh24:mi'));
INSERT INTO my_table VALUES ('David', 'Department01', to_date ('02/11/2016 08:00', 'dd/mm/yyyy hh24:mi'), to_date ('02/11/2016 09:30', 'dd/mm/yyyy hh24:mi'));
INSERT INTO my_table VALUES ('Francisco', 'Department02', to_date ('05/11/2016 11:10', 'dd/mm/yyyy hh24:mi'), to_date ('05/11/2016 13:20', 'dd/mm/yyyy hh24:mi'));
INSERT INTO my_table VALUES ('Margareth', 'Department01', to_date ('03/11/2016 11:00', 'dd/mm/yyyy hh24:mi'), to_date ('03/11/2016 17:00', 'dd/mm/yyyy hh24:mi'));
INSERT INTO my_table VALUES ('John', 'Department03', to_date ('03/11/2016 07:20', 'dd/mm/yyyy hh24:mi'), to_date ('03/11/2016 09:25', 'dd/mm/yyyy hh24:mi'));
INSERT INTO my_table VALUES ('David', 'Department03', to_date ('02/11/2016 10:35', 'dd/mm/yyyy hh24:mi'), to_date ('02/11/2016 13:40', 'dd/mm/yyyy hh24:mi'));
INSERT INTO my_table VALUES ('Margareth', 'Department02', to_date ('04/11/2016 09:30', 'dd/mm/yyyy hh24:mi'), to_date ('04/11/2016 11:40', 'dd/mm/yyyy hh24:mi'));
INSERT INTO my_table VALUES ('Domenic', 'Department03', to_date ('02/11/2016 10:00', 'dd/mm/yyyy hh24:mi'), to_date ('02/11/2016 12:00', 'dd/mm/yyyy hh24:mi'));
INSERT INTO my_table VALUES ('Domenic', 'Department01', to_date ('01/11/2016 09:00', 'dd/mm/yyyy hh24:mi'), to_date ('01/11/2016 09:00', 'dd/mm/yyyy hh24:mi'));
In the picture below you can see the content of the table my_table and, at its right, what I would like to get. I need a report where, for each user, is being calculated the SUM of hours (HH24:MI) worked in each department. In the end, for each department, the general total (HH24:MI). I have used different colors to better explain it.
I am capable to do it with the operator PIVOT and with the values expressed in numbers (minutes) but, unfortunately, I have not been capable to do te same with the values expressed in HH24:MI. Could you help me, please? Thank you
PS: without using PL/SQL please....