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)
);
Tried the Query but it only display Car record and custId of past 9 months, does not give last Car record based on mcDate not in past 9 months from today's date.
-- Query 1
SELECT C.carId, model, CONVERT(VARCHAR(15), ImportDate, 105) AS 'date of import', Cust.custId, 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
GROUP BY C.carId, model, ImportDate, Cust.custId
having mcDate < DATEADD(month,-9,getdate()) and mcDate = max(mcDate)
ORDER BY C.carId
-- Query 2
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 mcDate < DATEADD(month,-9,getdate())
GROUP BY C.carId, model, ImportDate, Cust.custId
ORDER BY C.carId