Hello all. While we are waiting for GeoEvent processor to be rolled out in my organization, I am attempting to create a temporary solution to get pressure tracking data into our GIS system to be made available to our end-users. I have the workflow planned out, but the only thing in my way is getting the most recent readings into a seperate table.
We have a SQL Server database that holds all of the readings in a single table, which contains over 2 million rows. Data is held in the table per reading date, UDI (device identifier), channel (3 channels per device), and value (actual readings).
Since the process will be scripted in python, I am trying to automate the copying of the most recent readings from this table into a new table. I am using the Table to Table tool, with a SQL.
Here is the code I am trying to use, which seems to work well inside of SQL Server:
select UDI,Channel,Value, DateTime_UTC
from PTData4 t1
WHERE EXISTS(SELECT 1
FROM PTData4 t2
WHERE t2.UDI = t1.UDI
AND t2.Channel = t1.Channel
GROUP BY t2.UDI,
HAVING t1.DateTime_UTC = MAX(t2.DateTime_UTC))
However in ArcGIS I get an error that states: "There was an error with the expression. Failed to parse the where clause."
Any ideas how to convert this code to be compatible with ArcGIS?