4 Replies Latest reply on Jan 1, 2014 9:06 AM by spkr25

    SQL Help




      I have one table, "Deals", with several transactions. There are three fields; InputDate, ID, Amount. with the following data:

      Inputdate, ID, Amount

      25/11/2013, 001, $100

      25/11/2013, 002, $200

      25/11/2013, 003, $180

      26/11/2013, 001, $100

      26/11/2013, 002, $200

      26/11/2013, 003, $180

      26/11/2013, 004, $243


      I would like to have an SQL comparing data from two different dates, in this case; 25/11/2013 and 26/11/2013 then using the ID field to identify which one has been added on the 26/11/2013. The expected result should be 26/11/2013, 004, $243.


      I believe an inner join could do this but I am not sure how to create it. I would appreciate some help here.




      I am trying to use the following Query:


      ExecuteSQL ( "SELECT T1.DateInput, T1.Key

      From Deals as T1

      Where Not exists (Select 1

      From Deals as T2

      where T2.DataInput =? and T1.DataInput =? and T2.Key=T1.Key)



      Unfortunately, it doesnt work.


      Message was edited by: spkr25

        • 1. Re: SQL Help

          An easier way to do this and just as fast is to do two SQL queries:


          one to get the IDs for the first date, one to get the IDs for the 2nd date and then use the FM function FilterValues to compare both lists.


          That is going to be much easier to troubleshoot down the road.

          • 2. Re: SQL Help

            The filtervalue returns the same deals I had the previous day. I need a to know the new deals.

            • 3. Re: SQL Help

              A couple of things:


              - you query should just ask for the PRIMARYKEY field to compare the list.  You'll need a 3rd query at the end to ask for the data of the added records


              - you are specifying "¶" as the delimiter for both fields and records, that does not work.  The default is commas between fields and returns between records, if that is what you want  you can just leave those entries blank:

              ExecuteSQL ( "some sql query" ; "" ; "" ; someParameter )


              - you are using aliases (ID, GL ) but you don't use them in the SELECT field list or for the DATEINPUT field, that is confusing; it is not clear what table those fields come from


              - I'm not entirely sure you need the join, but I don't know enough about your data structure to know for sure.


              - FilterValues will give you the IDs that both lists have in common.  There is a custom function available at www.briandunning.com named AntiFilterValues that gives you the reverse.  That's probably better for you to avoid any post processing of the lists.


              - unrelated to the issue: don't use global variables ($$) where a local variable is enough ($).  It is good practice to limit the life-time of a variable to the scope where it is used.  If you need them in a script they should not survive the end of the script

              • 4. Re: SQL Help

                Thanks for all the comments. Yes, I agree with all of them.