12 Replies Latest reply on Mar 15, 2015 12:46 PM by openspace

    SQL: Returning ? for 1/3 stats

    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