Record of last request not in current past 9 months

melon · 2015-08-20 05:00:50 UTC

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

Kristen · 2015-08-20 07:16:08 UTC

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

mmkrishna1919 · 2015-08-20 07:22:52 UTC

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

melon · 2015-08-20 18:03:42 UTC

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

Kristen · 2015-08-20 19:03:26 UTC

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.