I need to show maxInspectionDate in my result but I can't figure out how to do it.

Regards

Phil

SELECT E.equipmentID, E.assetNumber, E.T5Code , E.InspectionDuration, E.Description,
E.AssetType, E.WorkingLimits
FROM Equipment E
WHERE EXISTS
(SELECT t.equipmentID, r.maxInspectionDate
FROM (
      SELECT equipmentID, MAX(nextInspectionDate) as maxInspectionDate
      FROM equipmentInspection
      GROUP BY equipmentID
) r
INNER JOIN equipmentInspection t
ON t.equipmentID = r.equipmentID AND t.nextInspectionDate = r.maxInspectionDate)

Hello - You can try this:

SELECT E.equipmentID, E.assetNumber, E.T5Code , E.InspectionDuration, E.Description,
E.AssetType, E.WorkingLimits, EI.maxInspectionDate
FROM Equipment E INNER JOIN 
(
	SELECT  equipmentID, MAX(nextInspectionDate) as maxInspectionDate
	FROM equipmentInspection
    GROUP BY equipmentID
)EI
ON E.equipmentID = EI.equipmentID

Hope  this helps !

Hi,

You need to specify the subquery one more time and join that to the main result..

Please try the following and let me if it works.

SELECT E.equipmentID, E.assetNumber, E.T5Code , E.InspectionDuration, E.Description,
E.AssetType, E.WorkingLimits, MI.maxInspectionDate
FROM Equipment E,

-- Added
(

SELECT t.equipmentID, r.maxInspectionDate
FROM (
      SELECT equipmentID, MAX(nextInspectionDate) as maxInspectionDate
      FROM equipmentInspection
      GROUP BY equipmentID
) r
INNER JOIN equipmentInspection t
ON t.equipmentID = r.equipmentID AND t.nextInspectionDate = r.maxInspectionDate
) MI


WHERE EXISTS
(

SELECT t.equipmentID, r.maxInspectionDate
FROM (
      SELECT equipmentID, MAX(nextInspectionDate) as maxInspectionDate
      FROM equipmentInspection
      GROUP BY equipmentID
) r
INNER JOIN equipmentInspection t
ON t.equipmentID = r.equipmentID AND t.nextInspectionDate = r.maxInspectionDate
)

-- Added
AND E.equipmentID = MI.equipmentID




 

• Marked as answer by philmarsd Tuesday, June 02, 2015 10:40 AM

Hi Manu,

This worked!

Thank you for you help!

Phil

Hi Veera,

This also worked so many thanks!

Regards

Phil

Hi,

I wonder if you can help me further?

A new element has been added where we can now have more than 1 type of inspection per equipmentID.

These are made unique by an inspectionName column.

For instance equipmentID 1 can now have 1 or more maxInspectionDates determined by inspectionName.

I had a go but can only manage to get 1 equipment to report.

SELECT eI.*
FROM equipmentInspection eI
INNER JOIN
    (
    SELECT inspectionName, MAX(nextInspectionDate) AS MaxDateTime
    FROM equipmentInspection
    GROUP BY inspectionName
)  groupeI ON eI.inspectionName =  groupeI.inspectionName 
And eI.nextInspectionDate =  groupeI.MaxDateTime

Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data. We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI-ISO Standard SQL. 

Now we have to guess at the DDL because you have bad manners. And do the typing for you, too. 

Please think about how silly your EXISTS (SELECT <column list>..) is. Just write EXISTS (SELECT * ..) instead. EXISTS() is a predicate; it will only return TRUE or FALSE.  Please Google why we do not use camelCase any more. 

Your next_inspection_date is wrong. It is not an attribute, but a calculation. It changes from day to day! The prefix next_ is called a role, the inspection is an attribute and the postfix _date is an attribute property. You need to learn data modeling, but do not try to read ISO-11179 until you have at least one basic book or course on this topic. 

I will guess that the inspections are in a schedule for each piece of equipment. SQL is a database language, not a computational language for weird temporal math. Is a 100 years big enough? 

CREATE TABLE Equipment_Inspections
(inspection_date DATE NOT NULL,
 equipment_id CHAR(15) NOT NULL
  REFERENCES Equipment(equipment_id)
  ON UPDATE CASCADE
  ON DELETE CASCADE,
 PRIMARY KEY (inspection_date, equipment_id),
 inspection_status CHAR(5) DEFAULT 'schd' NOT NULL
  CHECK (inspection_status IN ('schd', 'miss', 'done', ..)),
 ..);

To find the next scheduled inspection is now a simple query with the CURRENT_TIMESTAMP. 

SELECT equipment_id, MIN(inspection_date) AS next_inspection_date
  FROM Equipment_Inspections 
 WHERE inspection_date  >= CURRENT_TIMESTAMP
 GROUP BY equipment_id; 

Most of the work in SQL is done in the DDL, not the DML. 

 

SELECT E.equipmentID, E.assetNumber, E.T5Code , E.InspectionDuration, E.Description,E.AssetType, E.WorkingLimits, EI.maxInspectionDate
FROM Equipment E INNER JOIN 
(SELECT  equipmentID, MAX(nextInspectionDate) as maxInspectionDate FROM equipmentInspection GROUP BY equipmentID
)b ON E.equipmentID = EI.equipmentID