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

Popular posts from this blog

sublimetext3 - what keyboard shortcut is to comment/uncomment for this script tag in sublime -

java - No use of nillable="0" in SOAP Webservice -

ubuntu - Laravel 5.2 quickstart guide gives Not Found Error -