SQLTeam.com | Weblogs | Forums

Record of last request not in current past 9 months


#1

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

#2

I think your

having mcDate < DATEADD(month,-9,getdate())

might need to be

having MAX(mcDate) < DATEADD(month,-9,getdate())

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


#3

Try this

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


#4

Hi,

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?

Incorrect Output:
carId: SGY12345 (repeated)
mcDate: 2010-01-30
mcDate: 2012-03-30

carId: SGD56721 (repeated)
mcDate: 2014-05-30
mcDate: 2014-07-15

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

#5

Add to SELECT clause:

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.