Selecting latest date in a range

Hi Jeremy,

Can you use a subquery? Like:
WHERE Date <= (select max(some_date_column) from your_date_table)

What do you mean with custom SQL queries? How and where do you want to use them?

Sorry for my late reply, I’m on holiday! ??

Best regards,
Peter

Hi Peter and apologies for taking so long to reply.

Ive used your example above and made some progress however, this only returns the most recent record (by date) rather than showing the most recent record from each individual.

The custom SQL queries is as you have described above (typed into the ‘where’ field in Data Designer). I wasnt sure whether there is another field where you can add in custom SQL queries to manipulate the data prior to using it in a child page as in;

Main data table —> Custom queries —> Custom data Table for use in Data Project —> data used in Page

Hi @covoljeremy,

>>> this only returns the most recent record (by date) rather than showing the most recent record from each individual

Can you add the user info to your where clause? Make sure the user id of your subquery equals the user id of your main table. That should solve it.

Let me know if this helps…

Good luck,
Peter

Thanks Peter. With multiple WHERE clauses, what separator should I be using?

Ive tried colon/semicolon, comma, space but none seem to work?

I would like the WHERE phrase to be;

Date_Trained >= (select max(Date_Trained) from wp_database), Candidate_Name = “Xxxx X”, Candidate_Name = “Yyyy Y”, Candidate_Name = “Zzzz,Z”

Hi Jeremy,

Not completely sure but I guess you’re looking for something like this:

Date_Trained >= (select max(Date_Trained) from wp_database where Candidate_Name = 'Xxxx X' and Candidate_Name = 'Yyyy Y' and Candidate_Name = 'Zzzz,Z'

Let me know,
Peter

Hi Peter, the above syntax returns no results.

Im trying various versions of this now but….

…using this code;
Date_Trained >= (select max(Date_Trained) from wp_database) where Candidate_Name = “Xxxx X”

gives me everyones records from the max(Date_Trained) record of Candidate_Name = “Xxxx X”

So, if I have the following records;
NAME Latest Record
Xxxx X 2018/10/06
Yyyy Y 2017/09/23
Zzzz Z 2018/11/15

the above code only returns Xxxx and Zzzz records and doesnt include Yyyy which would make sense as the date has been set by Xxxx latest record.

What I am after is everyones latest record.

Thanks, jeremy

Ah, we’re getting closer… ??

How about this:

Date_Trained >= (select max(Date_Trained) from wp_database where Candidate_Name = 'Xxxx X' or Candidate_Name = 'Yyyy Y' or Candidate_Name = 'Zzzz,Z'

use OR instead of AND

Best regards,
Peter

Thanks Peter, but ive tried the OR and it then finds the person who has the most recent entry only :-/

This SQL statement returns the correct results;

SELECT Candidate_Name, MAX(Date_Trained) AS most_recent_date FROM wp_dbnamexxx GROUP BY Candidate_Name

The difference here is that it is finding the name first and then finding the most recent record (and then groups by name)

the problem with the current code is that it looks for the most recent date and then stops and returns a name (or based on a name if the “” where Candidate_Name = ‘Xxxx X’ “” statement is used. I am tryng to obtain everyone’s most recent date.

Im still trying to find a way so your continued help is invaluable

Thanks, Jeremy

Its the ‘Group By’ statement that has the greatest effect as when I remove that from the SQL query, I get the same results as the code being used above.

Is there a similar statemnent to ‘Group By’?

Thanks, Jeremy

Can you share your create table and view scripts? Including your data?

You can use the contactform on the plugin website if you prefer to send it in private.

Thanks,
Peter

Hi Peter Ive replied to you

Hi Peter, using the view coding (Import scripts through Data Explorer) with an SQL query has worked and displays via Data Pub just as I wanted so thanks very much for your help.

Great! ?? Your welcome!