WHERE NOT EXISTS/Outer join problem 2

[thrybergh]

Hello.

I have 2 tables. One table (called ITAS_USER) contains one column (EMPLID) and this table holds 600+ EMPLID numbers. These are all the people who should be entering data in a survey.

The other table (TIME_RECORD) contains the hours they record, with the principal columns being EMPLID, D_WKST (the week that the hours are recorded for) and HOURS (number of hours).

What I would like my query to do is select the EMPLIDs of the users who have NOT entered hours for a particular week, so that an email can be sent to them.

This query selects those have entered hours in the week beginning 04-10-2004

select distinct
    tr.emplid,
    tr.d_wkst,
    nvl(sum(tr.hours), 0)
from
    ita.time_record tr
where
    tr.d_wkst = TO_DATE('2004-10-04', 'YYYY-MM-DD')
group by
    tr.emplid,
    tr.d_wkst

I would like to be able to cross-reference my results against the ITAS_USER table to find the users who have NOT entered any hours. Simply put, if they appear in the ITAS_USER table, but have a total of 0 hours entered in the TIME_RECORD table, they should be selected.

Thank you.

 

[JaxtheDog]

thrybergh,

Its not clear if you have a record in the time_record table, or no record for employees who have not entered hours. Depending on that, you have several choices for your query:

If there is no record in time_record for an employee for the week you want, this will find it:

Select empid from itas_user where empid not in
(select emp_id from time_record where d_wkst = TO_DATE('2004-10-04', 'YYYY-MM-DD'))

If you do have a record in time_record with a zero or null field in that record and you want to find only those you can use the having keyword. I don't think this is the case from your sample, but in case you start with a null record:

select tr.emplid, tr.d_wkst, nvl(sum(tr.hours), 0) as hours
from
ita.time_record tr
where
tr.d_wkst = TO_DATE('2004-10-04', 'YYYY-MM-DD')

having hours=0

group by
tr.emplid,
tr.d_wkst

 

[Turkbear]

Hi,
Try:

select distinct
    tr.emplid from
    ita.itas_user 
MINUS
select  tr.emplid,
from (select distinct
    emplid,
    d_wkst,
    nvl(sum(hours), 0)
from
    ita.time_record where
    d_wkst = TO_DATE('2004-10-04', 'YYYY-MM-DD')
group by
    emplid,
    d_wkst) tr

Should return only those Emplids that do not have hours in time_record data for that week

 

[thrybergh]

Thank you both very much for your help, it has helped me to solve my problem!