Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query problem
SQL Query problem [message #11120] Mon, 08 March 2004 09:58 Go to next message
pinka
Messages: 2
Registered: March 2004
Junior Member
I am trying to get the sum of what each person worked from the overtime information table wgrovdt which has information about the ot_hours(overtime hours) and when they worked those overtime hours(wgrovdt_date)

So I am taking the sum of what each person worked in that job type (programmer, clerk etc) and taking the minimum from that group..

My problem is that wgremdt table is the employee data table..

If there is a person who never worked at all then there is no record in the wgrovdt table for that person on those days(wgrovdt_date)..

So when I calculte the minimum hours I want the zero to show up since there was a programmer who didnt do overtime so minimum is zero.. but there is no record in the table so how do I get the zero?

This gets the minimum hours .. but if someone is zero it doesnt consider that..

select min(sum(wgrovdt_ot_hours)) from wurdpov,wurovtm,wgrovdt,wgremdt where wgremdt_pidm = wgrovdt_pidm and wgrovdt_ot_type = wurovtm_ov_desc and wurovtm_ov_code = wurdpov_ov_code and wurdpov_dept_code = '1' and wurdpov_equal_type = 'Y' and wgremdt_class_code = '1' and trunc(wgrovdt_date) between trunc(sysdate-365) and trunc(sysdate) group by wgrovdt_id

 
Re: SQL Query problem [message #11161 is a reply to message #11120] Wed, 10 March 2004 01:23 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
select wgremdt_pidm, nvl(sum(wgrovdt_ot_hours),0)
from wurdpov, wurovtm, wgrovdt, wgremdt
where wgremdt_pidm = wgrovdt_pidm(+)
and wgrovdt_ot_type = wurovtm_ov_desc(+)
and wurovtm_ov_code = wurdpov_ov_code(+)
and wurdpov_dept_code(+) = '1'
and wurdpov_equal_type(+) = 'Y'
and wgremdt_class_code = '1'
and trunc (wgrovdt_date(+)) between trunc(sysdate-365) and trunc(sysdate)
group by wgremdt_pidm;
Previous Topic: Free PLSQL editor/compiler/object viewer
Next Topic: Program unit not found.
Goto Forum:
  


Current Time: Thu Mar 28 17:42:54 CDT 2024