Combining SQL Server queries for speed

Question

I have two tables, one of items that require regular inspection and one of inspection reports for those items.

Items can be marked as "not available for inspection" if the engineer couldn't see them for whatever reason.

I need to display the list of items, showing the last actual inspection date for each item. If the last actual inspection for the item was not the same as the last visit date (ie last attempted inspection), this needs to be highlighted.

Here's what I have currently:

Tables and columns:

Items
ID, Name, etc...

ReportItems
ReportID, ItemID, InspectionDate, NotAvailable

Commence dodgy pseudocode:

SELECT * FROM Items

' then loop through that to display the data:
For each itemDataRow

  ' Get last actual date
  datatable1 = SELECT TOP 1 ReportID, InspectionDate FROM ReportItems WHERE ItemID = itemDataRow(ID) AND NotAvailable = 0 ORDER BY InspectionDate DESC

  ' Get last attempted date
  datatable2 = SELECT TOP 1 ReportID FROM ReportItems WHERE ItemID = itemDataRow(ID) ORDER BY InspectionDate DESC

  ' Check if they are the same
  itemWasNotAvailableAtLastVisit = datatable1(ReportID) <> datatable2(ReportID)

  ' add row to list to display, showing datatable1(InspectionDate), highlighted if itemWasNotAvailableAtLastVisit == true

End loop

The trouble is that this means 2 extra database queries for each item when populating the data, which is slowing things down considerably, especially on longer lists.

How can I optimise this, ideally into a single fast query?

I have tried subqueries like this but it's very slow:

SELECT ID, Name, (
SELECT TOP 1 InspectionDate FROM ReportItems WHERE ItemID = Items.ID AND Available = 0 ORDER BY InspectionDate DESC
) AS LastInspection,
(
SELECT TOP 1 InspectionDate FROM ReportItems WHERE ItemID = Items.ID ORDER BY InspectionDate DESC
) AS LastVisit
 FROM Items

Sample data:

Items

ID | Name
---------
1  | Crane
2  | Tractor
3  | Forklift

ReportItems

ReportID | VisitID | ItemID | InspectionDate | NotAvailable
----------------------------------------------------------------
1        | 1       | 1      | 2022-04-20     | 0
2        | 1       | 2      | 2022-04-20     | 1
3        | 1       | 3      | 2022-04-20     | 0
4        | 2       | 1      | 2022-03-15     | 0
5        | 2       | 2      | 2022-03-15     | 0
6        | 2       | 3      | 2022-03-15     | 0

Desired results:

Crane, last inspection was 2022-04-20
Tractor, last inspection was **2022-03-15** (NB This is not the last visit date) 
Forklift, last inspection was 2022-04-20

Answer 1

Based on your example query you can probably join/group/max as follows:

SELECT ID, [Name]
    , MAX(CASE WHEN Available = 0 THEN InspectionDate END) AS LastInspection
    , MAX(InspectionDate) AS LastVisit
FROM Items I
JOIN ReportItems RI on RI.ItemID = I.ID
GROUP BY I.ID;