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
but I think you could probably do away with the GROUP BY and use an Exists
SELECT C.carId, ...
FROM Car AS C
INNER JOIN Customer AS Cust
ON C.custId=Cust.custId
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
)
If you need the actual date of the most recent maintenance record then I would use a sub-select to get that, rather than GROUP BY - let us know what version of SQL you are using because newer / new-ish versions support slicker ways of doing that such as using APPLY or CTE
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
and mcl.mcDate = (select MAX(mcl1.mcdate) from MCLog mcl1 where mcl.carId = mcl1.carId)
INNER JOIN MC AS MC
ON MCL.mcId=MC.mcId
where mcl.mcDate < DATEADD(month,-9,getdate())
ORDER BY C.carId
I tried with SQL 2012, record returns all Car record that did not exist for last 9 months. I need the latest mcDate instead, how do i modify the query?
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
CONVERT(VARCHAR(10), MCL2.MAX_mcDate, 105) AS 'maintainenance date'
change the FROM clause to be (just these tables):
FROM Car AS C
INNER JOIN Customer AS Cust
ON C.custId=Cust.custId
JOIN
(
SELECT carId,
MAX(mcDate) AS MAX_mcDate
FROM MCLog
GROUP BY carId
HAVING MAX(mcDate) <= DATEADD(month,-9,getdate())
) AS MCL2
ON MCL2.carId = C.carId
This presumes that you don't need MCLog nor MC in the SELECT, and once you add this JOIN to the GROUP BY you won't need the NOT EXISTS either.