topic Re: Most Recent Reading from Unit and Channel in Data Management Questions

Most Recent Reading from Unit and Channel

MarcCusumano — Fri, 13 Nov 2015 20:07:01 GMT

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?

Re: Most Recent Reading from Unit and Channel

JoshuaBixby — Sat, 14 Nov 2015 17:35:03 GMT

Can you provide the specific Python code? Although you have provided the SQL code that works directly in SQL Server, it is helpful to see how you are creating/building the arguments and passing them to the tool. And, please use Syntax Highlighting with your code, it makes it much easier to read, thanks.

Re: Most Recent Reading from Unit and Channel

MarcCusumano — Sun, 12 Dec 2021 03:53:36 GMT

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

This seems to work in ArcCatalog, but the results are not expected (I am getting readings from different dates for each device and channel):

DateTime_UTC in (SELECT max( DateTime_UTC ) 
FROM PT_RO_Pressure_Tracker_Interval_Data 
GROUP BY UDI, Channel)

I have not tried any automation in Python yet; I would first like to get the extract working in Table to Table before attempting automation.

Thanks.

Re: Most Recent Reading from Unit and Channel

JoshuaBixby — Mon, 16 Nov 2015 16:29:58 GMT

Honestly, I am a bit confused. You are using the Table to Table tool, right? And you are pasting the entire SQL above into the Expression box or are you pasting only the part after WHERE? If one looks at the Syntax table for the tool, the "Expression" box in the GUI tool is really a "where_clause," not a general SQL box.