13 Replies Latest reply on Jul 7, 2014 3:38 AM by erolst

    Date sql




      A little help...


      sql ( "SELECT COUNT ( id ) FROM PATIENT WHERE date (create_timestamp__am) = curdate()" )


      Returning a "?"



        • 1. Re: Date sql



          You may want to take a look at SeedCodes SQL Explorer and Beverly Voth's The Missing FM 12 ExecuteSQL reference




          so that stated,  I'm assuming you are using the ExecuteSQL command for this so try this:


          SELECT COUNT( a."ID")

          FROM "PATIENT" a WHERE a."create_timestamp_am" = CURDATE



          1 of 1 people found this helpful
          • 2. Re: Date sql

            Thanks Bruce.


            I'll have another read through of those resources. What I keep struggling with is when to use the table variables (a. b.) because I don't use them in most of my calculations and then sometimes they are necessary. For instance, why do I need them in this sql statement?


            Thanks again

            • 3. Re: Date sql



              If you look at the statement,  (a."ID") is the field in the "PATIENT" table.  If you look at the from section FROM "PATIENT" a

              notice the "a" after "PATIENT"  this designates the PATIENT table as table a.  So the a."ID"  works.  In short you are giving the sql statement a fully qualified field name. 


              I find that using seedcodes SQL Explorer saves me a lot of time developing the queries.   I usually use the "Copy as abstracted calculation" when putting the result into my solutions.  This allows me to change field names and still have the SQL work.


              Frankly I have been surprised by the FileMaker starter solutions that use ExecuteSQL and don't use abstraction.  It becomes way to easy to break the SQL in these solutions.



              • 4. Re: Date sql

                Hey, Hudi. The "alias" or table variables are you called them are ONLY necessary if you may be using the same field/column name from two or more tables. or it the table names are long and you are lazy and don't want to type them over and over...


                     SELECT parent.id, child.id

                     FROM parent, child


                     SELECT p.id, c.id

                     FROM parent AS p, child AS c



                     SELECT p.name_last, c.name_first

                     FROM parentInformationFromTheContactsDB p, childInformationFromTheContactDB c


                     SELECT address, dependant_age

                     FROM parent, child




                     example one uses the full table name

                     example two uses the alias and includes the AS (for clarity)

                     example three uses the alias because of the long table names, but omits the optional AS

                     example four does not use alias because the fields are uniquely named

                     once you NAME an alias, you MUST use it!


                • I prefer to include the AS for clarity but it is optional:


                     parent AS p


                is the same to SQL as


                     parent p


                • I prefer to use the alias if I have a long list of fields (even if different names) when calling more than one table. It just makes the code easier to read.


                HTH, yes re-read all the "documentation" out there. My reference has a lot of links for giving you more examples and ideas.


                1 of 1 people found this helpful
                • 5. Re: Date sql

                  +1 on that, Bruce!


                  Seedcode has updated this tool to be more useful. I haven't tested some of the more complex queries with joins, but this should get you almost there and be easy enough to revise.


                  There are videos on the site, if you want to see examples in action.



                  • 6. Re: Date sql

                    One comment about your "Count ( id )" part of your SQL.  One of the quirks of FileMaker is that if you put a field name in there, it goes really slow compared to "Count ( * )".  The "*" basically counts how many records are in the found set whereas counting a particular field only counts if something is in that field.  But typically an "id" field is a primary key field and will always have something in it, which means "Count ( id )" would give the same results as "Count ( * )", only the "Count ( * )" will return the result MUCH faster.  This is one technique for helping optimize your SQL. 

                    • 7. Re: Date sql

                      I tested only a few cases, but

                      without WHERE clause, COUNT(*) is really faster than COUNT(id)

                      with WHERE clause,  not very faster.


                      Anyway, I think when counting records, (*) is preferable.

                      • 8. Re: Date sql

                        Thanks Bevery,


                        I use your link and the other FilemakerHacks FQL resources at least once a week. It's truly invaluable to those of us who are relatively new to sql ( ie me).

                        • 9. Re: Date sql

                          Hi Bruce,


                          I've used seedcode's sql explorer and it's really an nice tool for complex sql. I've ususually been able to copy the query straight from there into my solutions with only cosmetic tweaks.

                          • 10. Re: Date sql

                            The problem was (seems obvious now but..) that I was using a Timestamp field. I couldn't get the 'date' function in sql to change the timestamp  to dd/mm/yyyy.


                            I'm curious how to compare a timestamp and a date in SQL . This is what I have currently and it's not working.

                            "SELECT COUNT ( id ) FROM REFERRAL WHERE Date ( create_timestamp__am) =   CURDATE()  "



                            I did end up using SeedCode's Explorer to get the final query but I had to change the creation timestamp field to a simple date. here's the query that is in place and working.

                            ExecuteSQL ( "

                            SELECT Count ( a.\"id\" )

                            FROM \"REFERRAL\" a

                            WHERE a.\"create_date__ad\" = ?"

                            ;"";""; Get( CurrentDate )






                            • 11. Re: Date sql

                              Or you could have searched on the timestamp field searching for results starting at midnight and going through the end of the day such as:


                              Let ( [


                              F1 = Get ( CurrentTimestamp ) ;

                              F2 = Date ( F1 ) ;

                              F3 = Timestamp ( F2 ; Time ( 0 ; 0 ; 0 ) ) ;

                              F4 = Timestamp ( F2 ; Time ( 23 ; 59 ; 59 ) ) ;

                              F5 = "SELECT

                              Count ( a.\"id\" )


                              \"REFERRAL\" a


                              a.\"create_timestamp__am\" >= ? and

                              a.\"create_timestamp__am\" <= ?" ;

                              F6 = ExecuteSQL ( F5 ; "" ; "" ; F3 ; F4 )


                              ] ;





                              • 12. Re: Date sql

                                This is great. I need some advice. I have a list of class attendance for students. Those students who turn up for the class will be marked Yes. Otherwise it is marked as No. I need to find out how many time each student has attended a particular class between the two dates. In other words, I need to do Count for each individual student.


                                • 13. Re: Date sql

                                  See if this works:


                                  Let ( [

                                    date1 = Date ( 1 ; 1 ; 2014 ) ; // your start date goes here

                                    date2 = Date ( 12 ; 31 ; 2014 ) ; // your end date goes here

                                    ~sql = " SELECT student, Count ( student ) FROM Attendances WHERE theDate >= ? and theDate <= ? AND attended = ? GROUP BY student "

                                    ] ;

                                    ExecuteSQL ( ~sql ; ": " ; "" ; date1 ; date2 ; "yes" )



                                  If you use a number field to denote attendance as 1 or zero/0, you could use:


                                    ~sql = " SELECT student, Count ( attended ) FROM Attendances WHERE theDate >= ? and theDate <= ? GROUP BY student "


                                  And if you don't use a student name field in the Attendance table, but rather (and correctly) a foreign studentID key, you need a JOIN to get a human-readable result:


                                  ~sql = "

                                  SELECT S.theName, Count ( A.attended )

                                  FROM Attendances A

                                  JOIN Students S

                                  ON A.\"_kf_studentID\" = S.\"_kp_studentID\"

                                  WHERE theDate >= ? and theDate <= ?

                                  GROUP BY S.theName