sql - WHERE Clause with conditions in it -
i want create clause take account termination date , if hire date between these values , term date isn't on or before hiredate - ie employee never started - how go that? far have this:
select a.adpid employeeid, isnull(a.lname, '') [last name], isnull(a.fname, '') [first name], isnull(a.primaryemail, '') [email], isnull(m.fname + ' ' + m.lname, '') manager, isnull(convert(varchar(100), a.hiredate, 101), '') hiredate, isnull(convert(varchar(100), a.terminationdate, 101), '') termdate, isnull(div.divisionname, '') division, isnull(fun.functionname, '') [function], isnull(dep.departmentname, '') department, isnull(wgp.workgroupname, '') workgroup, isnull(lob.ccalobname, '') lob, isnull(maes.empstatusdesc, '') [employee type] dbo.associates left outer join dbo.associates m -- associate information on a.superprincipal = m.associd left outer join dbo.masterccalob lob -- line of business on a.lobid = lob.ccalobid left outer join dbo.masterassocempstatus maes -- employee type on a.empstatusid = maes.empstatusid left outer join dbo.associatesdepartment dep with(nolock) -- department on a.associatesdepartmentid = dep.associatesdepartmentid left outer join dbo.associatesdivision div with(nolock) -- division on a.associatesdivisionid = div.associatesdivisionid left outer join dbo.associatesworkgroup wgp with(nolock) -- workgroup on a.associatesworkgroupid = wgp.associatesworkgroupid left outer join dbo.associatesfunction fun with(nolock) -- function on a.associatesfunctionid = fun.associatesfunctionid ( len(a.terminationdate) = 0 , isnull(a.hiredate, '1900-01-01') between '2015-10-01' , getdate() ) or ( len(a.terminationdate) > 0 , isnull(a.terminationdate, '1900-01-01') > isnull(a.hiredate, '1900-01-01') , isnull(a.hiredate, '1900-01-01') between '2015-10-01' , getdate() ) order a.hiredate desc
there clause bad can because craziest results this
Comments
Post a Comment