5 Replies Latest reply on Dec 2, 2014 4:46 AM by filemaker@i-absolute.com

    ExecuteSQL and BETWEEN

    filemaker@i-absolute.com

      Hello everyone, I would like to get the sum of column A, where my search value is between column B and column C.

      I tried it with this query, but does not seem to work properly:

       

       

      SELECT SUM (T.columnA) FROM T WHERE ? BETWEEN T.columnB AND T.columnC

       

       

      T.columnA, T.columnB and T.columnC are INT

      ie. ? = Year (Get (currentDate))

       

       

      thanks,

      Fabio

        • 1. Re: ExecuteSQL and BETWEEN
          beverly

          Do you have some sample data of what you have and what you want?

           

          -- sent from myPhone --

          Beverly Voth

          --

          • 2. Re: ExecuteSQL and BETWEEN
            filemaker@i-absolute.com

            Hi Beverly,

            here a sample data.

             

            columnAcolumnBcolumnCcolumnEcolumnF
            10111120142014
            20101120142015
            591020152015
            38920142015
            2101120142015
            10111120152015

             

            search criteria:

            m=11

            a=2015

             

            query:

            ExecuteSQL ( “SELECT SUM (T.columnA) FROM T WHERE ? BETWEEN T.columnB AND T.columnC AND ? BETWEEN T.columnE AND T.columnF”; “”; “”; m; a )

             

            Output should be: 32 (20+2+10)

             

            Thanks,

            Fabio

            • 3. Re: ExecuteSQL and BETWEEN
              Padster

              Hi Fabio,

               

              Just looking at this, the only bit taht I can see that might be incorrect is the table reference, in this example, ar you actually using a table that you have named 'T'?

               

              This is what I have done with your code, and have only added the 'SQL_TEST' as the table name.

               

              Let ( [

                        a = 2015 ;

                        m = 11 ;

                        sql =

                                  ExecuteSQL (

                                            "SELECT SUM (T.columnA) FROM

                                            SQL_TEST T

                                            WHERE

                                                      ? BETWEEN T.columnB AND T.columnC AND

                                                      ? BETWEEN T.columnE AND T.columnF" ;

                                                      ""; "";

                                                      m ; a )

                        ] ;

                        sql

              )

               

              This works as you expect it to.

               

              Pad

              • 4. Re: ExecuteSQL and BETWEEN
                wimdecorte

                Fabio Bosisio wrote:

                 

                 

                Output should be: 32 (20+2+10)

                 

                 

                What output do you get?

                • 5. Re: ExecuteSQL and BETWEEN
                  filemaker@i-absolute.com

                  Thanks Pad, it is correct!

                  What I had written was only the prototype of the query.

                   

                   

                  @wimdecorte: When I run the query, in some cases the result is not correct because the query returns a value even if there is no match with the records.