I need some help setting up a query or refining a query for the following logic.
AVO_LASTDATE is the last inspection date
I only want to show inspections where AVO_LASTDATE is greater than or equal to 15 days ago, but not in this month of this year.
For example, if the AVO_LASTDATE is 1/4/2025, I shouldn't see it in the inbox until 2/1/2025.
If AVO_LASTDATE is 1/31/2025, I should see it in the inbox starting 2/15/2025
Inspections have to be at least 15 days apart but I can't do two in the same month. This is what I have now, but am getting a 400 error.
(CURRENT_DATE - 15 >= AVO_LASTDATE) AND MONTH(AVO_LASTDATE) != MONTH(CURDATE())
AND YEAR(AVO_LASTDATE = YEAR(CURDATE())
Solved! Go to Solution.
Your last 15 days logic is backwards, you're mixing CURRENT_DATE with CURDATE() (not a real function), those MONTH and YEAR functions won't work and you're testing for inequality with != instead of <>. Putting that all together with some help from this post:
AVO_LASTDATE > (CURRENT_DATE - 21) AND extract(month from AVO_LASTDATE) <> extract(month from CURRENT_DATE) AND extract(year from AVO_LASTDATE) = extract(year from CURRENT_DATE)
Your last 15 days logic is backwards, you're mixing CURRENT_DATE with CURDATE() (not a real function), those MONTH and YEAR functions won't work and you're testing for inequality with != instead of <>. Putting that all together with some help from this post:
AVO_LASTDATE > (CURRENT_DATE - 21) AND extract(month from AVO_LASTDATE) <> extract(month from CURRENT_DATE) AND extract(year from AVO_LASTDATE) = extract(year from CURRENT_DATE)
Thanks David,
This appears to have done the trick.
I ran into a bit of a snag with the EXTRACT (month....). There are no errors, but the query doesn't return any results. I tested it with EXTRACT (year....) and that is working fine. Any ideas on why it's not liking the month option?
This version works:
(ASSETTEAM = 'Eagle Ford') AND (AVO = 'Yes') AND (ROUTE_NUMBER = 'A1-101') AND (CURRENT_DATE - 15 >= AVO_LASTDATE) AND EXTRACT (year from AVO_LASTDATE) <> EXTRACT (year from CURRENT_DATE)
This version doesn't:
(ASSETTEAM = 'Eagle Ford') AND (AVO = 'Yes') AND (ROUTE_NUMBER = 'A1-101') AND (CURRENT_DATE - 15 >= AVO_LASTDATE) AND EXTRACT (month from AVO_LASTDATE) <> EXTRACT (month from CURRENT_DATE)
Ideally, I'd like to get something along the lines of:
(ASSETTEAM = 'Eagle Ford') AND (AVO = 'Yes') AND (ROUTE_NUMBER = 'A1-101') AND (CURRENT_DATE - 15 >= AVO_LASTDATE) AND EXTRACT (month from AVO_LASTDATE) <> EXTRACT (month from CURRENT_DATE) AND EXTRACT (year from AVO_LASTDATE) <> EXTRACT (year from CURRENT_DATE)