AnsweredAssumed Answered

SQL: Returning ? for 1/3 stats

Question asked by openspace on Mar 9, 2015
Latest reply on Mar 15, 2015 by openspace

I have a question fairly specific to some sql that myself and some lovely contributors from this forum helped me write previously. The code below produces three bits of statistical information for my file: Total members that lapsed, total memberships that lapsed, and total in revenue made from lapsed memberships. The result that I'm getting for the members is returning as a "?". I've check all my field types and as far as I can tell the code should work.

 

Furthermore, I am puzzled because when I change the where clause in the sql (in red) to WHERE MDateExpiry > EndDate AND MDateCreation  <= EndDate to calculate the total "active" members I get the correct results.

 

When I remove the lines WHERE MDateExpiry <= EndDate AND MDateExpiry >= StartDate it returns a result of 0. If anyone could help me get to the bottom of this I would greatly appreciate it. I'm running fm 13. MDateExpiry, StartDate, and EndDate are all date fields. MContactIds is a text field that lists all contacts linked to each membership record.


Let ( [

members= Let ( [

  memberIDs =

    ExecuteSQL ( "

      SELECT (\"MContactIds\")

      FROM \"Line Items\"

     WHERE MDateExpiry <= EndDate AND MDateExpiry >= StartDate

     AND MDateRenewal < StartDate

      " ; "" ; "" )

    ] ;

  Case (

    not IsEmpty ( memberIDs ) ;

    ExecuteSQL ( "

      SELECT COUNT (*)

      FROM Contacts

      WHERE \"CONTACT ID MATCH FIELD\" IN (" & Substitute ( memberIDs ; ¶ ; "," ) & ")

       " ; "" ; ""

    ) ;

    0

  )

) ;

membershipsdollars = ExecuteSQL ("

SELECT COUNT (DISTINCT MContactIds), SUM (MTotal)

FROM \"Line Items\"

WHERE MDateExpiry <= EndDate AND MDateExpiry >= StartDate

AND MDateRenewal < StartDate

" ;¶; ¶) ] ;

members  & ¶ & membershipsdollars )

 

The result:

?

54

1905

 

Screen Shot 2015-03-09 at 11.27.19 AM.png

Outcomes