Dear all,
I would like to display Car records to retrieve last Maintenance request for all customer who did not make request in past 9 months from current date.
- MC refers to Maintenance.
MCLog data:
carId: SGY12345 (repeated)
mcDate: 2010-01-30
mcDate: 2012-03-30
carId: SGX55661 (repeated)
mcDate: 2015-05-30
mcDate: 2015-06-15
carId: SGD56721 (repeated)
mcDate: 2014-05-30
mcDate: 2014-07-15
Desired Output should return:
carId: SGD56721
mcDate: 2014-07-15
carId: SGY12345
mcDate: 2012-03-30
Here is my Table:
CREATE TABLE Car ( carId Char (20) NOT NULL, model Char (20) NOT NULL, importDate smalldatetime NOT NULL, custId Varchar (50) NOT NULL, CONSTRAINT Car_PK PRIMARY KEY (carId)); CREATE TABLE Customer ( custId Varchar (50) NOT NULL, name Char (50) NOT NULL, CONSTRAINT Customer_PK PRIMARY KEY (custId)); CREATE TABLE MC( mcId Varchar (5) NOT NULL, mcType Char (50) NOT NULL, CONSTRAINT MC_PK PRIMARY KEY (mcId)); CREATE TABLE MCLog ( carId Char (20) NOT NULL, mcId Varchar (5) NOT NULL, mcDate smalldatetime NOT NULL, CONSTRAINT MCLog_PK PRIMARY KEY (carId, mcId),CONSTRAINT Car_MCLog_Relationship Foreign Key (carId) REFERENCES Car (carId), CONSTRAINT MC_MCLog_Relationship Foreign Key (mcId) REFERENCES MC (mcId));
SELECT C.carId, model, CONVERT(VARCHAR(15), ImportDate, 105) AS 'date of import', Cust.custId, max(CONVERT(VARCHAR(10), mcDate, 105)) AS 'maintainenance date' FROM Car AS C INNER JOIN Customer AS Cust ON C.custId=Cust.custId INNER JOIN MCLog AS MCL ON C.carId=MCL.carId INNER JOIN MC AS MC ON MCL.mcId=MC.mcId WHERE NOT EXISTS ( SELECT * FROM MCLog AS MCL WHERE MCL.carId = C.carId AND mcDate > DATEADD(month,-9,getdate()) -- Maintenance record in last 9 month exists ) GROUP BY C.carId, model, ImportDate, Cust.custId ORDER BY C.carId
Incorrect Output:
carId: SGY12345 (repeated)
mcDate: 2010-01-30
mcDate: 2012-03-30
carId: SGD56721 (repeated)
mcDate: 2014-05-30
mcDate: 2014-07-15