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

# SQL: SELECT SUM of column2 in relation to column1?

### 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

 ContactIDs Creation Expiry Renewal Name Total 111 2013-12-01 2014-12-01 2013-12-01 Anne 30 111 2013-12-01 2015-12-01 2014-12-01 Anne 20 112 2013-12-01 2014-12-01 2013-12-01 Bob 50

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?

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?

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.