Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Need to qualify the results of a query

Author  Topic 

ajsqlnoob
Starting Member

5 Posts

Posted - 2014-11-24 : 13:02:48
I have the following query that returns employee numbers:

select distinct personnum from VP_TMSHTCOMMENTV42 where COMMENTTEXT = 'Late'

I have another query that returns another set of employee numbers:
select distinct a.PERSONNUM from VP_WATPA a
join VP_ALLPERSONV42 b on (b.PERSONNUM = a.PERSONNUM)
where a.POLICYNM = 'Yearly Award' and b.EMPLOYMENTSTATUS = 'Active'
and b.HOMELABORLEVELNM5 in ('100141', '100142', '100143',
'100144', '100145', '100146', '100147')
and b.ISWFCATTENDANCELIC = '1' and b.PAYRULENAME not like '%Salar%' and (WATPASTATUSID = '2') and
startdtm = DATEADD(dd, -1, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))
order by a.PERSONNUM


I need a query that will use the 2nd query as a qualifier for the first. Meaning I only want to return employee #s from my 1st query as long as those employee #s are not returned in the 2nd query.

For example, the first query returns:
EE#
1234
3421
5521

And the 2nd query returns:
1234
3421

My query would need to return 5521 since it does not exist in the 2nd query.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-24 : 13:08:22
Try this:


select distinct personnum
from VP_TMSHTCOMMENTV42 v
where COMMENTTEXT = 'Late'
and not exists
(
select *
from VP_WATPA a
join VP_ALLPERSONV42 b on (b.PERSONNUM = a.PERSONNUM)
where v.PERSONNUM = a.PERSONNUM
and a.POLICYNM = 'Yearly Award' and b.EMPLOYMENTSTATUS = 'Active'
and b.HOMELABORLEVELNM5 in ('100141', '100142', '100143', '100144', '100145', '100146', '100147')
and b.ISWFCATTENDANCELIC = '1' and b.PAYRULENAME not like '%Salar%' and (WATPASTATUSID = '2')
and startdtm = DATEADD(dd, -1, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))
)


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

ajsqlnoob
Starting Member

5 Posts

Posted - 2014-11-24 : 13:24:03
It runs but it doesn't return any results. But I do have employees in the first query that do not exist in the 2nd query.
Go to Top of Page

ajsqlnoob
Starting Member

5 Posts

Posted - 2014-11-24 : 13:41:55
quote:
Originally posted by ajsqlnoob

It runs but it doesn't return any results. But I do have employees in the first query that do not exist in the 2nd query.



Nevermind, got it working! Thank you! Just had to join the two tables.
Go to Top of Page
   

- Advertisement -