Date expression to exclude prior yrs AND... 3

[monkeysee]

I have a query that I want to show ONLY restaurants that have not had an inspection during the current year, but still exclude prior years. How would I build the expression for the date field to accomplish this?

Fields used in query:
Inspection ID
Facility ID
Date of Inspection

 

[lespaul]

Assuming you have a table of facilities, something like this may work:

SELECT FacilityID FROM FacilityTable WHERE FacilityID NOT IN (SELECT FacilityID FROM InspectionTable WHERE Year([Date of Inspection] = Year(Now) )

the red subquery finds all facilities that HAVE an inspection for this year and the outer query finds all the facilities that are NOT in that list.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[monkeysee]

EXCELLANT!!! Thanks so Much Leslie! It works!

 

[monkeysee]

Leslie,

In further testing your expression, which I greatly appreciated, it seems that it is not searching just the current year (as in 2006), but is actually searching literally one year prior to the current date. Is there something we are leaving out? As I only want it to search for the year 2006 nothing prior. THANKS AGAIN SO MUCH FOR YOUR HELP!!!!

 

[PHV]

What is your actual SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

Leslie's expression
- Finds all the restaurants that were inspected in 2006 (the sub query)
- Then excludes those (NOT IN) from the main query.
- What remains should be all those that were inspected before 2006 or were never inspected at all.

The implicit assumption in Leslie's code is that the [Date of Inspection] field supplies the date of the most recent inspection for each restaurant. If there are in fact multiple inspection dates recorded in the "InspectionTable" for each FacilityID then you will need some more elaborate code

SELECT F.FacilityID 

FROM FacilityTable F INNER JOIN 
   ( SELECT FacilityID
          , MAX([Date of Inspection]) As [LastInspection]  
     FROM InspectionTable 
     Group By FacilityID
   ) As D

   ON F.FacilityID = D.FacilityID

Where Year(D.[LastInspection]) < Year(Date())

UNION

SELECT F.FacilityID 

FROM FacilityTable F LEFT JOIN InspectionTable D
     ON F.FacilityID = D.FacilityID

Where D.FacilityID IS NULL

 

[lespaul]

show off!

(but of course, you're right!!)

have a great day!

les

 

[monkeysee]

Thanks Leslie, yes there are multiple inspection dates for each facility for prior years. What I want to show are those facilities that have had no inspection for the current calendar year so that the inspectors can do.

This is what I now have using your latest sql:

SELECT Food.FacilityID

FROM Food
INNER JOIN
( SELECT FacilityID
, MAX([InspectionDate]) As [LastInspection]
FROM Inspections
Group By FacilityID
) As D

ON Food.FacilityID = D.FacilityID

Where Year(D.[LastInspection]) < Year(Date())

UNION SELECT Food.FacilityID

FROM Food LEFT JOIN Inspections D
ON Food.FacilityID = D.FacilityID

Where D.FacilityID IS NULL;


I would also like to include the following fields:
Food.BusinessName
Food.NameID
Food.AddressLocationID
Food.CertifiedManager
Food.Priority
Food.Status
License.LicenseNo
Address.CityName

With the Food.Priority = 1
and Food.Status = Active

Is this possible?

 

[Golom]

Yes it may be possible.

You would just Join to the "License" and "Address" tables but that could be a bit complex if the relationships between those tables and the "Food" table are not one-to-one (i.e. there is exactly one record in "License" that matches a record in "Food"; and similarly for "Address")

If the relationship is one-to-many or if there is a possibility that License and/or Address data doesn't exist for a given Food.FacilityID then you will need to use OUTER JOINS to ensure that you get all records returned.

 

[monkeysee]

Golom,
I want to make sure that I thank you for your help with the sql that you posted, your help is greatly appreciated!

Yes, there is only one license for each facility, as is there is only one address for each facility. If a facility has more than one location,(like McDonalds, etc) then, a separate license is issued for each particular location. There is never a possiblity that there would not be an address for a facilitly.

I'm not very 'up' on composing sql so thanks so much for any help you can give!

 

[Golom]

Try something like this

SELECT F.FacilityID
     , F.BusinessName
     , F.NameID
     , F.AddressLocationID
     , F.CertifiedManager
     , F.Priority
     , F.Status 
     , L.LicenseNo
     , A.CityName

FROM ((FacilityTable F INNER JOIN 
      ( SELECT FacilityID, MAX([Date of Inspection]) As [LastInspection]  
        FROM InspectionTable 
        Group By FacilityID
      ) As D
     ON F.FacilityID = D.FacilityID)

     INNER JOIN License L
     ON F.FacilityID = L.FacilityID)

     INNER JOIN Address A
     ON F.FacilityID = A.FacilityID
   
Where Year(D.[LastInspection]) < Year(Date())

UNION

SELECT F.FacilityID
     , F.BusinessName
     , F.NameID
     , F.AddressLocationID
     , F.CertifiedManager
     , F.Priority
     , F.Status 
     , L.LicenseNo
     , A.CityName

FROM ((FacilityTable F 

       INNER JOIN License L
       ON F.FacilityID = L.FacilityID)

       INNER JOIN Address A
       ON F.FacilityID = A.FacilityID)

     LEFT JOIN InspectionTable D
       ON F.FacilityID = D.FacilityID

Where D.FacilityID IS NULL

I've assumed that "FacilityID" is the matching field in all the tables.


If every facility has had an inspection (i.e. an inspection record always exists) then you can drop the UNION and everything that comes after it.