Need Help with Select - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Need Help with Select

Author

Topic

BradleyGZ
Starting Member

4 Posts

Posted - 2011-02-23 : 12:14:09

Here is the SQL Command I currently have.

SELECT DatEmployee.*, RefDepartment.DeptName, RefDepartment.DeptNum, RefTitle.JobTitle, RefTitle.WorkCompRef, DatAlias.AliasFirstName, DatAlias.AliasLastName, RefEEO1Category.EEO1Category, RefGovtID.GovtIDDesc FROM DatEmployee INNER JOIN RefDepartment ON DatEmployee.HomeDeptRef = RefDepartment.DeptRef INNER JOIN RefTitle ON DatEmployee.HomeTitleRef = RefTitle.TitleRef INNER JOIN RefEEO1Category ON DatEmployee.EEOCRef = RefEEO1Category.EEO1Ref INNER JOIN RefGovtID ON DatEmployee.GovtIDRef = RefGovtID.GovtIDRef LEFT OUTER JOIN DatAlias ON DatEmployee.TinNum = DatAlias.TinNum AND DatAlias.IsSSAName = 1 WHERE DatEmployee.DeptRef = @DeptRef

This statement is working. It grabs all employees regardless of Active or Terminated. Now what I want to do:
There is a field in DatEmployee called TerminationDate (of Date Type). This field is NULL unless they are terminated. I need to grab ALL Active employees and ONLY Terminated employees that have been terminated in the last 180 days. I dont know how to construct the WHERE Clause to grab if TerminationDate is null or within 180 days.

Please Help.

Thanks!

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-02-23 : 13:33:00

Try:
WHERE DatEmployee.DeptRef = @DeptRef and (TerminationDate is null or datediff(day,TerminationDate,getdate())<180)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-02-23 : 13:33:02

where
terminationDate >= dateadd(day,-180,getdate())
or terminationDate is null

Jim

Everyday I learn something that somebody else already knew

BradleyGZ
Starting Member

4 Posts

Posted - 2011-02-23 : 14:44:31

Thanks PK and JimF. I ended up using JimF's solution with the DateAdd function. Its working.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-02-23 : 15:05:53

It's also wise to avoid putting functions on your columns if possible. SQL wouldn't be able to take advantage of any index that might exist on terminationDate once you wrap a function around it.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources