AnsweredAssumed Answered

SQL: SELECT SUM of column2 in relation to column1?

Question asked by openspace on Mar 21, 2015
Latest reply on Mar 23, 2015 by 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.

Outcomes