Need Help Filtering Records With This Query

[lrdave36]

Hey guys,

I have a query request to return all employees who do not have service history. One way I can do this is to look for anyone that has zero months and years of service.


For example, I have this simple query

select distinct SSN from history
where service_years = 0
and   service_months = 0.00

I ran the query, and was puzzled to find some SSNS that do indeed have service years or months greater than 0. Then it dawned on me that the query was returning any record in the history that had zero value, and the history table contains multiple records for each SSN.

Example


SSN DATE SERVICE YEARS

2345555555 01-01-1992 1
2345555555 01-01-1991 0



This SSN (bogus SSN) would have appeared in my results because there is a zero record for service years.

I don't want this SSN in my query results. I only want people who never had a service year. Can anyone help?

 

[gmmastros]

Select SSN
From   History
Group By SSN
Having Sum(service_years) = 0
       And Sum(service_months) = 0

Not tested.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[lrdave36]

Thanks George. I actually just came up with that same query right after posting. lol Totally forgot about SUM function. I'm getting a lot of records so not sure if it is accurate.