Last record not in past 9 months from today's date

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




August 20th, 2015 6:17pm

Interesting ... it looks like it should work.

If you have just the CarId and MAX(mcDate), do you get the desired results?  I am just trying to rule out if the other column values may be interfering with the result set.

Also, on your correlated subquery, you may want to consider changing it to an EXIST and mcDate <= DATEADD(...).  In addition, why are you joining on MC?  It doesn't seem to be used.

Free Windows Admin Tool Kit Click here and download it now
August 20th, 2015 6:58pm

Try

;with cte as (select C.*, Cust.name, MC.*, L.mcDate, ROW_NUMBER() over (partition by C.CarId order by L.mcDate DESC) as Rn
 from Car C inner join Customer Cust on C.custId = Cust.custId
 INNER JOIN McLog L ON C.carId = L.carId INNER JOIN MC ON L.mcId = MC.mcId
 where not exists(select 1 from McLog L1 where L1.carId = C.carId 
 and L1.mcDate between dateadd(month, -9, CURRENT_TIMESTAMP) and CURRENT_TIMESTAMP))

 select * from cte where Rn = 1

August 20th, 2015 7:37pm

Hi heyweed,

The repeated was caused by the group by clause in your query. In this case the group by clause is not very necessary. Please see the tweaked query below.

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
WHERE NOT EXISTS    --Add this NOT EXISTS TO get the latest MCLOG for each car
(
SELECT * FROM MCLog WHERE carID=MCL.carID AND mcdate>MCL.mcDate
)
AND MCL.mcDate<DATEADD(MM,-9,GETDATE())

If you have any question, feel free to let me
Free Windows Admin Tool Kit Click here and download it now
August 21st, 2015 8:51am

Hi,

Cross apply is best for this.

SELECT cu.custId,ca.carId,mc.mcDate
FROM @Customer AS cu
INNER JOIN @Car AS ca ON cu.custId = ca.custId
CROSS APPLY(
	SELECT TOP 1 *
	FROM @MCLog AS mc
	WHERE mc.carId = ca.carId
	AND mc.mcDate < DATEADD(DAY,-90,'2015-08-21')
	ORDER BY mc.mcDate DESC) AS mc

with ddl.

DECLARE @Car AS TABLE(carId CHAR(20),custId VARCHAR(50))

DECLARE @Customer AS TABLE(custId VARCHAR(50))

DECLARE @MCLog AS TABLE(carId CHAR(20),mcId VARCHAR(5),mcDate SMALLDATETIME)

INSERT INTO @Customer VALUES (1)

INSERT INTO @Car VALUES ('SGY12345',1)
INSERT INTO @Car VALUES ('SGX55661',1)
INSERT INTO @Car VALUES ('SGD56721',1)

INSERT INTO @MCLog VALUES ('SGY12345',1,'2010-01-30')
INSERT INTO @MCLog VALUES ('SGY12345',2,'2012-03-30')
INSERT INTO @MCLog VALUES ('SGX55661',3,'2015-05-30')
INSERT INTO @MCLog VALUES ('SGX55661',4,'2015-06-15')
INSERT INTO @MCLog VALUES ('SGD56721',5,'2014-05-30')
INSERT INTO @MCLog VALUES ('SGD56721',6,'2014-07-15')

SELECT cu.custId,ca.carId,mc.mcDate
FROM @Customer AS cu
INNER JOIN @Car AS ca ON cu.custId = ca.custId
CROSS APPLY(
	SELECT TOP 1 *
	FROM @MCLog AS mc
	WHERE mc.carId = ca.carId
	AND mc.mcDate < DATEADD(DAY,-90,'2015-08-21')
	ORDER BY mc.mcDate DESC) AS mc

August 21st, 2015 9:12am

Hi Eric,

Referring to the subquery only, it does display the most recent date from current date and output correctly. Thank you much appreciated.

  • Edited by heyweed 18 hours 52 minutes ago
Free Windows Admin Tool Kit Click here and download it now
August 22nd, 2015 8:17am

Hi Steelleg,

Appreciate your help. As there are mcId with multiple mcDate, the suggested query display does not check the dates and get last record of each group that excludes past 9 months.

Following query modified to check mcId in MC and MCLog.

SELECT cu.custId,ca.carId,mc.mcId, mcl.mcDate
FROM @Customer AS cu
INNER JOIN @Car AS ca ON cu.custId = ca.custId
CROSS APPLY(
    SELECT TOP 1 *
    FROM @MCLog AS mc
    WHERE mc.carId = ca.carId
    AND mc.mcDate < DATEADD(MM,-9,getdate())
    ORDER BY mc.mcDate DESC) AS mcl
INNER JOIN @MC AS mc ON mcl.mcId=MC.mcId

August 22nd, 2015 8:29am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics