How to return records when no dates in range? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

How to return records when no dates in range?

Author

Topic

siuc1996
Starting Member

6 Posts

Posted - 2010-04-13 : 15:45:16

I am trying to right a pretty simple SQL query and have run into a road block. I have a need to be able to see when we have rehired a former employee this year but have not had them get a drug test this calendar year yet. I know how to bring in records that have drug tests this year and in the past but I just can't figured out how have it show those employees hired that don't have anything for this year.

Select Clause:

select bHRDT.HRRef as [HRRef], bHRDT.Date as [Date], 
bHRDT.JCCo as [JCCo], bHRDT.Job as [Job], 
bHRDT.Location as [Location], bHRDT.TestStatus as [TestStatus],
 bHRDT.Results as [Results], bHRRM.PREmp as [PREmp], 
bHRRM.LastName as [LastName], bHRRM.FirstName as [FirstName],
 bHRRM.MiddleName as [MiddleName], bHRRM.Suffix as [Suffix],budHREmployeeHistory.DateStarted as [DateStarted]

Where Clause:

FROM bHRDT (Nolock)
JOIN bHRRM (Nolock)  on bHRDT.HRCo = bHRRM.HRCo and bHRDT.HRRef = bHRRM.HRRef
JOIN budHREmployeeHistory on bHRDT.HRRef=budHREmployeeHistory.Emp
where bHRDT.JCCo='1' and  bHRRM.ActiveYN='Y' and
 budHREmployeeHistory.DateStarted > '2010-01-01 00:00:00' and budHREmployeeHistory.DateDischarged is null

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2010-04-13 : 16:41:58

So if they did not take a drug test there won't be a record in bHRDT right?

you can left outer join to get the info.

SELECT C.REF, DTDATE, JCCO, JOB, LOCATION, TESTSTAT, RESULT, PREMP,
LNAME, FNAME, MNAME, SFX, DATESTARTED
FROM BHRRM A JOIN HISTORY B ON A.HRREF = B.EMP
LEFT OUTER JOIN BHRDT C ON C.HRCO = B.HRCO AND C.HRREF = B.HRREF
AND JCCO = '1'
WHERE ACTIVEYN = 'Y' AND B.DATESTARTED > '2010-01-01' AND B.DATEDISCHARGED IS NULL

siuc1996
Starting Member

6 Posts

Posted - 2010-04-13 : 16:44:23

Actually they could have a record in there if we had given them a drug test in the past years. I will try to play around with the outer join.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources