CODEWITHSUNDEEP
sqloracle-database
DaRoGa
DaRoGa

Reputation: 2404

sql combine query results

I have the following query:

select 
    employee.last_name || ', ' || employee.first_name name, 
    job.title,
    daystotime(time_sheet.finish_date_time - time_sheet.start_date_time) time
from 
    employee 
inner join 
    employee_case on employee.employee_id = employee_case.employee 
inner join
    time_sheet on time_sheet.employee_case = employee_case.employee_case_id 
inner join 
    job on employee.job = job.job_id
where 
    employee_case.case = 1
order by 
    employee.last_name;

this gives the following result:

enter image description here

However, what i need is to be able to combine the time column for the same employee to give a total time.

The function i have created to generate the time field is:

create or replace function DaysToTime(p_val in number)
return varchar2
is
  l_days     number;
  l_hours    number;
  l_minutes  number;
  l_seconds  number;
begin
  l_days := p_val;
  l_Hours := (l_days - trunc(l_days)) *24;
  l_minutes := (l_hours - trunc(l_hours)) * 60;
  l_seconds := (l_minutes - trunc(l_minutes)) * 60;
 return to_char(trunc(l_days), 'fm09')  ||':'||
        to_char(trunc(l_hours), 'fm09')  ||':'||
        to_char(trunc(l_minutes), 'fm09')||':'||
        to_char(trunc(l_seconds), 'fm09');
end;

Thanks for any help you could possibly give me with this and just say if you need any additional info

Upvotes: 0

Views: 64

Answers (1)

Multisync
Multisync

Reputation: 8797

Use SUM inside your function + GROUP BY:

select 
    employee.last_name || ', ' || employee.first_name name, 
    job.title,
    daystotime(sum(time_sheet.finish_date_time - time_sheet.start_date_time)) time
from 
    employee 
inner join 
    employee_case on employee.employee_id = employee_case.employee 
inner join
    time_sheet on time_sheet.employee_case = employee_case.employee_case_id 
inner join 
    job on employee.job = job.job_id
where 
    employee_case.case = 1
group by employee.last_name, employee.first_name, 
         job.title  
order by 
    employee.last_name;

P.S. There is an INTERVAL type in Oracle (maybe you don't need your own function):

select numtodsinterval(d2 - d1, 'day') from
(select to_date('02 12:44:01', 'DDHH24:MI:SS') d1, 
        to_date('03 12:44:05', 'DDHH24:MI:SS') d2 
 from dual)

Upvotes: 1

Related Questions