logo
    Helping YOU do what YOU want with YOUR data.
Welcome Guest! To enable all features please Login or Register.

Notification

Icon
Error

Options
Go to last post Go to first unread
Adavidson  
#1 Posted : Wednesday, September 18, 2019 10:24:01 AM(UTC)
Adavidson
Rank: Newbie

Reputation:

Groups: Registered
Posts: 7

I am trying to return the date and time and latest value from a dataset of water level in ~2000 bores. 

Is it possible to do this in ESdat filters? 

I tried brielfy using SQL, but with no luck. 

Warwick Wood  
#2 Posted : Thursday, September 19, 2019 10:01:14 PM(UTC)
Warwick Wood
Rank: Administration

Reputation:

Groups: Registered, Administrators
Posts: 422
Man
Location: Byron Bay

Was thanked: 9 time(s) in 9 post(s)

You can easily get the most recent date that a water level was collected at each location in a query if you are writing SQL.  You just group by the Location Code and return the Max of the Date.  If you're not sure how to write aggregate queries in SQL do a quick google search there is lots of information out there.

To get the actual value and other data for the Max date at each location is a bit more complex than you might expect in SQL.  You need save the above output as a query and then create a new query that references this query and also the original data and join the two on Location and the Date then you will get what you want.

Creating these kinds of SQL queries is more suitable to the Access version of ESdat Desktop where you can create database queries fairly easily.  It's not so suitable for the Server database as most users dont have the permissions to create ad hoc SQL. 

For the Server database, if it is suitable to use Excel it would be easiest to just export to Excel, sort by Location and Date, then use formulaes to flag the last value for each location, and then filter on those flags.

I hope this helps,

Warwick

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.