topic Re: QueryLayer help with DateTime field in ArcGIS Pro Questions

QueryLayer help with DateTime field

BrianBulla — Thu, 31 Aug 2023 19:14:23 GMT

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,

Re: QueryLayer help with DateTime field

AlfredBaldenweck — Fri, 01 Sep 2023 14:47:35 GMT

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.

Re: QueryLayer help with DateTime field

AlfredBaldenweck — Fri, 01 Sep 2023 15:03:07 GMT

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

Re: QueryLayer help with DateTime field

BrianBulla — Fri, 01 Sep 2023 15:32:57 GMT

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.

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??