QueryLayer help with DateTime field

[BrianBulla]

Hi,

I'm trying to use the following SQL statment in the "Edit Query Layer" dialog, but it seems to be getting hung-up on the "year" part of the query where I am just looking for rows that have an INSP_DATE year of 2022.  I don't think this "year" functionality exists in the query tool, so I am just wondering on an alternative method.

I've tried every SQL trick I can think of, but cannot get this to work.

select GISWRKS1.works.WAT_ControlValve_evw.*
FROM GISWRKS1.works.WAT_ControlValve_evw
WHERE FACILITYID NOT IN (
SELECT FACILITYID FROM GISWRKS1.works.INSPECTIONS_VALVE_INSPECTION
WHERE DATEPART(year, INSP_DATE) = 2022)

 This should exclude all valves that had in inspection done in 2022, but it always shows all valves.

Thanks,

[AlfredBaldenweck]

Try simplifying it?

select * from database.owner.Monitoring_tb
WHERE DATEPART(year, Mon_Date) = 2020

 This worked fine for me.

Edit: Oh whoops, didn't realize you were looking between two tables. Will revise my answer shortly.

[AlfredBaldenweck]

So, I don't have really good data to test this on, since the points used here get inspected more than once.

This shoooould work if the monitoring table has just one record for each valve?

select 
t1.OBJECTID,
t1.Nest_ID
 from db.owner.LOCATIONS_PT t1
INNER JOIN 
db.owner.MONITORING_TB t2
ON t1.Nest_ID = t2.Nest_ID
WHERE DATEPART(year, t2.NestMon_Date) !=2022

For the most recent record:

SELECT 
t1.OBJECTID,
t1.Nest_ID,
t2.NestMon_Date
FROM db.owner.Locations_pt t1 
  INNER JOIN (
    SELECT MIN(OBJECTID) as OID, 
    Nest_ID, 
    MAX(NestMon_Date) as MaxDate   
    FROM db.owner.Monitoring_tb   
    GROUP BY Nest_ID ) MaxDates ON t1.Nest_ID = MaxDates.Nest_ID 
    INNER JOIN db.owner.Monitoring_tb t2 
	ON MaxDates.Nest_ID = t2.Nest_ID 
		AND MaxDates.MaxDate = t2.NestMon_Date 
		AND MaxDates.OID = t2.OBJECTID
WHERE datepart(year,t2.NestMon_Date) !=2022

[BrianBulla]

Hi Alfred,

I was working on this today and what seems to be working is this.  It's not really that different from the first one I tried, but today it's working.

select GISWRKS1.works.WAT_ControlValve_evw.*
FROM GISWRKS1.works.WAT_ControlValve_evw
WHERE FACILITYID NOT IN (
SELECT FACILITYID FROM GISWRKS1.works.INSPECTIONS_VALVE_INSPECTION
WHERE DATEPART(year, GISWRKS1.works.INSPECTIONS_VALVE_INSPECTION.INSP_DATE) >= 2022)

This gives me all valves not done in 2022 (or later), which is what I am looking for.  Maybe just scrapping everything and starting from scratch is what helped??