Date of exam more than 5 years ago

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 2000 Forums

SQL Server Development (2000)

Date of exam more than 5 years ago

Author

Topic

Stevan2020
Starting Member

25 Posts

Posted - 2003-07-01 : 15:08:57

Let me preface this by saying that my DB administrator is on a months vacation. He is much better with SQL than I am.

I am trying to adapt this query that returns either the most recent eye exam date or an employees "Entered on duty" date (EOD) for one office. This query (below) works correctly.

SELECT E.EmpID, E.Fname, E.LastName, E.MI, E.Occtitle, E.LDC,
Case When I.DriverType is null then 99 else I.DriverType end as DriverType,
Case When I.DriverType is null then 0 else I.RecordID end as RecordID,
Case When Y.ExamDate is null then convert(smalldatetime, E.EOD) else Y.ExamDate End AS EyeDate,
Case When Y.ExamCert is null then '' else Y.ExamCert End AS EyeCert,
Case When M.ExamDate is null then convert(smalldatetime, E.EOD) else M.ExamDate End AS MedDate,
Case When M.ExamCert is null then '' else M.ExamCert End AS MedCert from tbl_Employee E
left join tbl_EmpInfo I on E.SSN = I.SSN
left join (select Z.SSN, Z.ExamDate, ExamCert
from tbl_EyeExamCertification X
join (select SSN, MAX(ExamDate) as ExamDate
from tbl_EyeExamCertification where ExamType = 'eye' and ExamCompleted = 1
Group By SSN) Z on X.SSN = Z.SSN and X.ExamDate = Z.ExamDate ) Y ON E.SSN = Y.SSN
left join (select Z2.SSN, Z2.ExamDate, ExamCert from tbl_EyeExamCertification X2
join (select SSN, MAX(ExamDate) as ExamDate
FROM tbl_EyeExamCertification where ExamType = 'med' and ExamCompleted = 1
Group By SSN) Z2 on X2.SSN = Z2.SSN and X2.ExamDate = Z2.ExamDate ) M ON E.SSN = M.SSN
join tbl_FinanceLoc F ON E.FinLoc = F.FinLoc join tbl_Offices O ON F.OfficeID = O.OfficeID
WHERE O.OfficeID = 'OffID'
ORDER BY M.EyeDate

The new query needs to return all employees (from all offices) that have not had an eye exam or were Entered on Duty (EOD) more than 5 years (less 90 days) ago. Also only for Drivers (DriverType <> 0). Employees do not necessarily have an entry for DriverType. In this case (DriverType is null) and they are assumed to be a driver.

My attempt:

SELECT E.EmpID, E.LastName, O.OfficeID,
Case When I.DriverType is null then 99 else I.DriverType end as DriverType,
Case When I.DriverType is null then 0 else I.RecordID end as RecordID,
Case When Y.ExamDate is null and (E.EOD < DateAdd(day,-1735,getDate())) then convert(smalldatetime, E.EOD)
else Y.ExamDate End AS EyeDate, convert(smalldatetime, E.EOD) as EOD
from tbl_Employee E join tbl_EmpInfo I on E.SSN = I.SSN
left join (select Z.SSN, Z.ExamDate, ExamCert
from tbl_EyeExamCertification X
left join (select SSN, MAX(ExamDate) as ExamDate
FROM tbl_EyeExamCertification
where ExamType = 'eye' and ExamCompleted = 1 and (ExamDate < DateAdd(day,-1735,getDate()))
Group By SSN) Z on X.SSN = Z.SSN and X.ExamDate = Z.ExamDate
) Y ON E.SSN = Y.SSN
join tbl_FinanceLoc F ON E.FinLoc = F.FinLoc
join tbl_Offices O ON F.OfficeID = O.OfficeID
WHERE I.DriverType <> 0
ORDER BY O.OfficeID, EyeDate desc

At this point the query is only returning EOD dates, though it does filter some records out that have had exams witin the past 5 years. However other records that have had recent exams still return the EOD date.

Thanks for any assistance

-- Steve

Edited by - Stevan2020 on 07/01/2003 16:36:44

mr_mist
Grunnio

1870 Posts

Posted - 2003-07-02 : 03:05:06

Case When Y.ExamDate is null and (E.EOD < DateAdd(day,-1735,getDate())) then convert(smalldatetime, E.EOD)
else Y.ExamDate End AS EyeDate

The logic here seems a bit flawed to me. I think that AND should be an OR in order to return, as you want, either people without an exam OR people with an EOD > 5 years ago.

Also, I think you would need a similar piece of logic in the where clause to limit the query results to just these people.

-------
Moo.

Stevan2020
Starting Member

25 Posts

Posted - 2003-07-02 : 10:53:43

Unfortunately changing the "and" to an "or" and addding the same to the where statement returned the same recordset.

Where (I.DriverType <> 0 and (E.EOD < DateAdd(day,-1735,getDate())))
- returns the same recordset

Where (I.DriverType <> 0 or (E.EOD < DateAdd(day,-1735,getDate())))
- also adds non-drivers in the recordset

I am trying to get a list of employees that are due for an eye exam. That means that they were hired (EOD) more than 5 years ago and have not had an exam within the last 5 years. The recordset does contain employees that meet this criteria, but it also includes a few that were hired (EOD) within the last 5 years.

It looks like employees that drive (DriverType <> 0) are always in the recordset and the EOD date is returned regardless of whether they had a recent exam or not.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-02 : 13:11:31

SELECT R.EmpID, R.LastName, R.OfficeID,
Case When R.DriverType is null then 99 else R.DriverType end as DriverType,
Case When R.DriverType is null then 0 else R.RecordID end as RecordID,
Case When Y.ExamDate is null -- and (R.EOD < DateAdd(day,-1735,getDate()))
then '00000000' -- convert(smalldatetime, E.EOD)
else Y.ExamDate End AS EyeDate,
convert(smalldatetime, R.EOD) as EOD

from
(tbl_Employee E join tbl_EmpInfo I on E.SSN = I.SSN
join tbl_FinanceLoc F ON E.FinLoc = F.FinLoc
join tbl_Offices O ON F.OfficeID = O.OfficeID
WHERE I.DriverType <> 0) R
LEFT JOIN
(select SSN, MAX(ExamDate) as ExamDate
FROM tbl_EyeExamCertification
where ExamType = 'eye' and
(ExamCompleted = 1 and ExamDate > DateAdd(day,-1735,getDate()))
Group By SSN) Z) Y ON R.SSN = Y.SSN

ORDER BY R.OfficeID, EyeDate desc

- Vit

Stevan2020
Starting Member

25 Posts

Posted - 2003-07-02 : 17:31:24

Vit,
I am not familiar with the way that you constructed the table join ...

from
(tbl_Employee E join tbl_EmpInfo I on E.SSN = I.SSN
join tbl_FinanceLoc F ON E.FinLoc = F.FinLoc
join tbl_Offices O ON F.OfficeID = O.OfficeID
WHERE I.DriverType <> 0) R

... and am therefore unable to resolve a couple of syntax errors

one is near "WHERE" (above)

and ...

LEFT JOIN
(select SSN, MAX(ExamDate) as ExamDate
FROM tbl_EyeExamCertification
where ExamType = 'eye' and
(ExamCompleted = 1 and ExamDate > DateAdd(day,-1735,getDate()))
Group By SSN) Z) Y ON R.SSN = Y.SSN

... the other syntax error near Z

You did give me some good ideas and I revised my query to:

SELECT E.EmpID, E.LastName, O.OfficeID,
Case When I.DriverType is null then 99 else I.DriverType end as DriverType,
Case When I.DriverType is null then 0 else I.RecordID end as RecordID,
Case When Y.ExamDate is null and (E.EOD < DateAdd(day,-1735,getDate())) then convert(smalldatetime, E.EOD)
else Y.ExamDate End AS EyeDate,

from tbl_Employee E join tbl_EmpInfo I on E.SSN = I.SSN
join tbl_FinanceLoc F ON E.FinLoc = F.FinLoc
join tbl_Offices O ON F.OfficeID = O.OfficeID
left join (select Z.SSN, Z.ExamDate, ExamCert
from tbl_EyeExamCertification X
left join (select SSN, MAX(ExamDate) as ExamDate
FROM tbl_EyeExamCertification
where ExamType = 'eye' and ExamCompleted = 1
Group By SSN) Z on X.SSN = Z.SSN and X.ExamDate = Z.ExamDate
) Y ON E.SSN = Y.SSN

WHERE (Y.ExamDate is not null and (Y.ExamDate < DateAdd(day,-1735,getDate())) and (E.EOD < DateAdd(day,-1735,getDate()))
and O.OfficeID in (5) and I.DriverType <> 0 ) or
(Y.ExamDate is null and (E.EOD < DateAdd(day,-1735,getDate()) ) and O.OfficeID in (5) and I.DriverType <> 0 )
ORDER BY EyeDate desc

Unfortunately this returns the correct list for OfficeID = 5 (limited recordeset) PLUS one employee that has never had an exam and was hired 2 years ago.

In other words those with an Exam within the last 5 years were eliminated but those hired within that last 5 years that did not have an Exam were not.

-- Steve

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-03 : 04:57:23

Sorry, Steve, for my inaccuracy.

SELECT R.EmpID, R.LastName, R.OfficeID,
Case When R.DriverType is null then 99 else R.DriverType end as DriverType,
Case When R.DriverType is null then 0 else R.RecordID end as RecordID,
Case When Z.ExamDate is null -- and (R.EOD < DateAdd(day,-1735,getDate()))
then Null -- convert(smalldatetime, R.EOD) why select EOD two times?
else Z.ExamDate End AS EyeDate,
convert(smalldatetime, R.EOD) as EOD

from
(select E.SSN, E.EmpID, E.LastName, F.OfficeID, I.DriverType, E.EOD
from tbl_Employee E join tbl_EmpInfo I on E.SSN = I.SSN
join tbl_FinanceLoc F ON E.FinLoc = F.FinLoc
join tbl_Offices O ON F.OfficeID = O.OfficeID
WHERE I.DriverType <> 0) R
LEFT JOIN
(select SSN, MAX(ExamDate) as ExamDate
FROM tbl_EyeExamCertification
where ExamType = 'eye' and
(ExamCompleted = 1 and ExamDate > DateAdd(day,-1735,getDate()))
-- seek only those completed exam within 5 last years
Group By SSN) Z ON R.SSN = Z.SSN

ORDER BY R.OfficeID, EyeDate desc

- Vit

Edited by - Stoad on 07/03/2003 05:31:19

Stevan2020
Starting Member

25 Posts

Posted - 2003-07-03 : 10:22:40

Thanks again, but this returns a nearly identical recordset to the last one that I posted. The difference is that yours returns null for all employees that have never had an exam. Mine returned the EOD.

Changing this line:
then Null -- convert(smalldatetime, R.EOD)
to:
then convert(smalldatetime, R.EOD)
Result is the exact same recordset.

I need a date for all employees that have not had an exam or were hired within 5 years. I also want to return just those records, eliminating any employess that were hired or had an exam within 5 years from the recordset.
Both queries still return employees that were hired within the last 5 years.

I was selecting the EOD (a second time) just to see which date was being returned. I had planned to eliminate that field. I should have mentioned this earlier.

-- Steve

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-03 : 10:49:00

If I understand your post, then moving the statement "R.EOD < DateAdd(day,-1735,getDate()" from the CASE to the rowset R definition in the FROM clause should do it.

Jonathan
{0}

Stevan2020
Starting Member

25 Posts

Posted - 2003-07-03 : 11:21:56

This returns the same recordset, which includes records for those that never had an exam but were hired in the last couple of years...

SELECT R.EmpID, R.LastName, R.OfficeID,
Case When R.DriverType is null then 99 else R.DriverType end as DriverType,
Case When R.DriverType is null then 0 else R.RecordID end as RecordID,
Case When Z.ExamDate is null -- and (R.EOD < DateAdd(day,-1735,getDate()))
then convert(smalldatetime, R.EOD)
else Z.ExamDate End AS EyeDate

from
(select E.SSN, E.EmpID, E.LastName, F.OfficeID, I.DriverType, E.EOD, I.RecordID
from tbl_Employee E join tbl_EmpInfo I on E.SSN = I.SSN
join tbl_FinanceLoc F ON E.FinLoc = F.FinLoc
join tbl_Offices O ON F.OfficeID = O.OfficeID
WHERE I.DriverType <> 0 and (E.EOD < DateAdd(day,-1735,getDate()))
) R
LEFT JOIN
(select SSN, MAX(ExamDate) as ExamDate
FROM tbl_EyeExamCertification
where ExamType = 'eye' and
(ExamCompleted = 1 and ExamDate < DateAdd(day,-1735,getDate()))
-- seek only those NOT completed exam within 5 last years
Group By SSN) Z ON R.SSN = Z.SSN

ORDER BY R.OfficeID, EyeDate desc

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-03 : 17:54:10

Something weird is going on...
BTW, why '1735' in DateAdd(day,-1735,getDate())?
Is it correct? I mean 5*365=1825...

- Vit

Stevan2020
Starting Member

25 Posts

Posted - 2003-07-07 : 10:13:13

You're telling me...

BTW I am using 1735 to give the users a warning that an exam is within 90 days of being past due.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-07 : 13:24:24

Try this:

WHERE isnull(I.DriverType, 2) <> 0 and (E.EOD < DateAdd(day,-1735,getDate()))
) R
LEFT JOIN

- Vit

Stevan2020
Starting Member

25 Posts

Posted - 2003-07-07 : 16:38:41

Same results...

Is there any additional info that I could provide that may help?

Stevan2020
Starting Member

25 Posts

Posted - 2003-07-07 : 17:44:48

All,

I asked my Manager of IS to take a look at this query. He is a long time C and VB programmer, who loves to code but rarely has time since he became Manager.

He realized that that the EOD and the statement DateAdd(day,-1735,getDate()) never matched because they were in different formats. the EOD comes from a corporate database as 20010704. So after converting that to smalldatetime, the errant records were changed to null.

Still need to figure out how to eliminate the null records from the recordset.

Case When Y.ExamDate is null and convert(smalldatetime, E.EOD) < DateAdd(day,-1735,getDate()) then convert(smalldatetime, E.EOD)
else Y.ExamDate End AS EyeDate, convert(smalldatetime, E.EOD) as EOD , Y.ExamDate as ExamDate

-- thanks everybody, Steve

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-07 : 18:48:12

Different formats? Heh. Who compares formats? SQL Server
compares inner representations of dates which are the same
for all possible datetime formats.

Let's check this:

SELECT R.EmpID, R.LastName, R.EOD, Z.ExamDate
from
(select E.SSN, E.EOD from tbl_Employee E
WHERE E.EOD < DateAdd(day,-1735,getDate())
) R
LEFT JOIN
(select SSN, MAX(ExamDate) as ExamDate
FROM tbl_EyeExamCertification
where ExamType = 'eye' and
(ExamCompleted = 1 and ExamDate < DateAdd(day,-1735,getDate()))
-- seek only those NOT completed exam within 5 last years
Group By SSN) Z ON R.SSN = Z.SSN

Is any EOD > DateAdd(day,-1735,getDate())?
If so, then remove from the 2-nd 'Where' ExamType='eye' condition,
then - ExamCompleted=1 condition. What then?

- Vit

Stevan2020
Starting Member

25 Posts

Posted - 2003-07-08 : 11:40:27

Unfortunately your new query still returns EOD dates that are less than 5 years ago.

The field EOD is imported as text from a mainframe database.

These 2 Case statements returning NotCoverted and Converted show the difference beween Converting the EOD and Not Converting the EOD to smalldatetime.

Case When (E.EOD) < DateAdd(day,-1735,getDate()) then convert(smalldatetime, E.EOD)
else Y.ExamDate End AS NotConverted,
... returns the EOD for all employees, including those that had an EOD less than 5 years ago.

Case When convert(smalldatetime, E.EOD) < DateAdd(day,-1735,getDate()) then convert(smalldatetime, E.EOD)
else Y.ExamDate End AS Converted,
... returns null for employees that had an EOD less than 5 years ago and have not had an exam within the past 5 years.

BTW I eliminated the null values by applying the same conversion of the EOD to the where statement.

-- Steve

Edited by - Stevan2020 on 07/08/2003 11:42:53

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-08 : 12:44:45

I'm very glad to hear this. :)

- Vit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources