AnsweredAssumed Answered

Most Recent Reading from Unit and Channel

Question asked by MCusumano on Nov 13, 2015
Latest reply on Nov 16, 2015 by bixb0012

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,

                  t2.Channel

         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?

 

 

Outcomes