• Registered: 2020-01-19
  • Posts: 29

Topic: Calculating Age with sql

Hi all

If there is anybody that help me solve this issue. 
I've gotten his far in my search with my minimal on going learning of SQL.

I want to filter everybody over a certain age.
Originally I was scripting everybody's AGE and saving in the table. I then realized that it wouldn't get updated automatically and would have to go into everybody's account and press save.

So I know the answer lies in SQL and came up with some kind of logic !
I know it doesn't work but Im missing the gaps of programming to get it going.


At Form startup (SQL QUERY)

        SELECT client, message, FROM dbclient
        WHERE
                date(Datebirth) – curdate() >= 58;


Result is sent to a table.

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,673

Re: Calculating Age with sql

Hi,
A few different options.
The simplest is to use a calculated field for the age calculation (so it is automatically updated as soon as you start your application) and then, if your filter is fixed (in your example, at 58 and over), you can just add this as a static filter to the tablegrid (please see attached).
Regards,
Derek.

Post's attachments

Attachment icon age filter.zip 338.44 kb, 332 downloads since 2021-12-11 

  • Registered: 2020-01-19
  • Posts: 29

Re: Calculating Age with sql

Hi Derek

And thanks as it works.

This example of coding directly within a Database tables, using a field, brings a new dimension in attempting to manipulate data.

I still don't know how whole script line works but I have a concrete example to dissect and think about.

With my little knowledge of programming, your example was scripted SQL  ?


Also, do you recommend any books for both Delphi and SQL that would coincide with MVD ?

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,673

Re: Calculating Age with sql

Hi,
MVD uses a subset of the features that Delphi (or Pascal) and SQL offer.  That's not a criticism of MVD in anyway (I think it's an amazing bit of software) but it does mean that it can be a bit confusing if you find something that 'works' in Delphi, for example, but when you come to try it in MVD, it's a feature that hasn't been incorporated. 
As such, I wonder whether any sort of reference books might add to the confusion.The bits of MVD that I've learned about have all been picked up from this Forum and from the MVD Help system.
With regard to your specific question about filtering by age,
1.  hard coding the criteria as part of the tablegrid filter is fine provided it's never going to change. 
2.  if it is liable to change, you're always better off using a filter that you can apply at run-time by using the 'search' option, the 'sql query' option or writing a bit of script.  Please have a look at the attachment as a way of using a variable filter with the 'search' option.
3.  for an 'age' related query such as yours, a calculated field is, I believe, the right option.  But one drawback is that it only performs the calculation once a record has been saved.  To get around that, you can add a small script which, if the action is 'newrecord' then it can show the age before the record has been saved which is a bit more user friendly.  It's still a calculated field, but just getting around the issue of it not immediately displaying (again, have a look at the attachment).
You also asked if the calculated field in the previous example was scripted SQL - well, not exactly!  What you find is that:
1.  the list of available commands is limited when using calculated fields and the syntax can be a bit different
2.  the list of available commands is greater when using 'sql query' and the syntax is similar to writing scripts
3.  the list of available commands is greatest when writing scripts.
I guess it's just a case of using the best tool for the job.
Hopefully, the above has explained things a bit rather than confounded them!!  But any questions, just ask and someone will usually be able to help out.
Derek.

Post's attachments

Attachment icon age filter2.zip 339.65 kb, 354 downloads since 2021-12-11