Link to home
Start Free TrialLog in
Avatar of erikTsomik
erikTsomikFlag for United States of America

asked on

sql help with inspection

I am trying to create an alert for the managers when the cars are due for inspection. There are 2 fields inspectiondate and nextinspectiondate that are responsible for the inspection. The alert need to be triggered 30 days before its expire .

SELECT 1 as 'Due for Inspection',
c.carNumber,
C.nextinspectiondate,C.inspectiondate,
C.model,c.carnumber,c.make,c.modelyear,
isNUll(DATEDIFF(DAY,GETDATE(),c.nextinspectiondate),0) as Next,
isNull(DATEDIFF(DAY,GETDATE(),c.inspectiondate),0) as Curr

FROM CAR C
   		WHERE C.carActiveIn = 1
   		--and DATEDIFF(DAY,GETDATE(),c.nextinspectiondate) > 0 OR DATEDIFF(DAY,GETDATE(),c.inspectiondate)  > 0
   		ORDER BY C.carKey

Open in new window

Avatar of Qlemo
Qlemo
Flag of Germany image

The dateDiff is correct, but you need to check for exact 30 days (= 30), or less or equal to 30 (<= 30) if you want to get the alert each day.
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial