4 Replies Latest reply on Mar 23, 2015 10:26 AM by openspace

    SQL: SELECT SUM of column2 in relation to column1?

    openspace

      Title

      SQL: SELECT SUM of column2 in relation to column1?

      Post

      I would like to calculate the the Total $$ amount of lapsed memberships in my database that did not also renew given a StartDate and EndDate. The SQL calculation below attempts this but returns an incorrect result. Below is some sample data and the desired result. I'm running fm 13.

      Some sample data:

      StartDate=2014/04/01 EndDate=2015/03/11

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
      ContactIDsCreationExpiryRenewalNameTotal
      1112013-12-012014-12-012013-12-01Anne30
      1112013-12-012015-12-012014-12-01Anne20
      1122013-12-012014-12-012013-12-01Bob50

      dollarsexpired =
      ExecuteSQL ("
      SELECT SUM (Total)
      FROM \"Line Items\"
      WHERE MDateExpiry >= StartDate
      " ;¶; ¶) ;

      dollarsrenewed =
      ExecuteSQL
      SELECT SUM (Total)
      F
      ROM \"Line Items\"
      WHERE MDateRenewal >= StartDate
      " ;¶; ¶)
      ] ;

      dollarsexpired - dollarsrenewed )

      Desired Result:
      50

      Longer explanation: In the example function I would like to know the $$ Total of members that lapsed, but did not renew. To find this number I subtract all of the lapsed memberships from those that renewed--this works if all the values are distinct, but shows an incorrect result when they are not (For example, when I change SELECT SUM (Total) TO SELECT DISTINCT (ContactIDs) I get a correct result of how many memberships lapsed, but when I change this back to SELECT SUM (Total) the calculation does not account for duplicate values (or members) given the start and end date. Both Anne and Bob lapsed, but Anne also renewed. Which means my result would show that Bob lapsed for a total of $50.

        • 1. Re: SQL: SELECT SUM of column2 in relation to column1?
          philmodjunk

          As I read the SQL, the result that you'd get would be 0 not 50. Is that the "incorrect" result that you are getting.

          All the records shown have an Expiry date greater than the start date if "creation" is the column named StartDate.

          All the records shown have a renewal date greater than or equal to the start date, again, I am assuming that "creation" and StartDate are one and the same.

          In your example, I see no lapsed memberships by the logic of your query anyway.

          Wouldn't lapsed memberships be those records where the Renewal date is less than or equal to expiry date?

          • 2. Re: SQL: SELECT SUM of column2 in relation to column1?
            openspace

            The start date and end date are independent fields chosen by the user. The start date is not the same field as the creation date. The creation date is simply the date of the first renewal for each member so the user can track how long a member has been with the organization. 

            Wouldn't lapsed memberships be those records where the Renewal date is less than or equal to expiry date?

            Lapsed members are determined by the expiry date. So in this example both Anne and Bob's memberships lapsed in 2014-12-01 which lyes within the start and end date written above (StartDate=2014/04/01 EndDate=2015/03/11). However, Anne also renewed on 2014-12-01 which means that the only true lapsed member is Bob.

            Please let me know if I'm not interpreting your question correctly.  

            • 3. Re: SQL: SELECT SUM of column2 in relation to column1?
              philmodjunk

              Yes, but your SQL query refers to Start Date and End Date as fields, not as criteria specified somehow as part of the query. You don't indicate if those are values in fields defined in a table, if they are global fields, or whether they are otherwise specified.

              • 4. Re: SQL: SELECT SUM of column2 in relation to column1?
                openspace

                These are calculation fiels inside the line items table that are copying data from global fields that the user selects in the dashboard table. There is only one start date and one end date across all records.