{
  "facility_sql": "SELECT DISTINCT /*FACILITY_TAG*/\r\n    m.ID AS Membership_Tracking_ID,\r\n    c.CUSTOMER_ID,\r\n    c.GUID,\r\n    c.FIRSTNAME AS First_Name,\r\n    c.LASTNAME AS Last_Name,\r\n    c.BDAY AS DOB,\r\n    c.CURRENT_STATUS AS Current_Status,\r\n    c.CUSTOMER_TYPE AS Current_Customer_Type,\r\n    c.MEMBERSHIP_FORM_OF_PAYMENT AS Form_of_Payment,\r\n    m.MTYPE AS Membership_Type,\r\n    m.CUSTOMER_TYPE AS Membership_Customer_Type,\r\n    m.START_DATE AS Membership_Start_Date,\r\n    m.END_DATE_INCLUSIVE AS Membership_End_Date\r\nFROM membership_tracking m\r\nJOIN customers c\r\n  ON c.CUSTOMER_ID = m.CUSTOMER_ID\r\n\r\n-- transfer note that occurs ON THE SAME DATE as the membership start\r\nLEFT JOIN notes n\r\n  ON n.CUSTOMER_ID = c.CUSTOMER_ID\r\n AND n.NOTES LIKE '%transferred from%'\r\n AND DATE(n.POSTDATE) = DATE(m.START_DATE)\r\n\r\n-- membership sales (by GUID + sale date) within the report window\r\nLEFT JOIN (\r\n    SELECT\r\n        cs.GUID AS customer_guid,\r\n        DATE(i.POSTDATE) AS sale_date\r\n    FROM invoices i\r\n    JOIN invoice_items ii\r\n      ON ii.INVOICE_ID = i.INVOICE_ID\r\n    JOIN products p\r\n      ON p.PRODUCT_ID = ii.PRODUCT_ID\r\n    JOIN customers cs\r\n      ON cs.CUSTOMER_ID = i.CHARGETO_CUSTOMER_ID\r\n    JOIN payments py\r\n      ON py.PAYMENT_ID = i.PAYMENT_ID\r\n    WHERE i.VOIDEDINVOICE = 0\r\n      AND (py.DECLINED IS NULL OR py.DECLINED = 0)\r\n      AND IFNULL(py.VOIDEDPAYMENT, 0) = 0\r\n      AND p.REV_CATEGORY = 'Membership'\r\n      -- restrict to the same reporting window for performance; matches your rule since m.START_DATE is in this window\r\n      AND DATE(i.POSTDATE) BETWEEN '/*START_DATE*/' AND '/*END_DATE*/'\r\n    GROUP BY cs.GUID, DATE(i.POSTDATE)\r\n) ms\r\n  ON ms.customer_guid = c.GUID\r\n AND ms.sale_date = DATE(m.START_DATE)\r\n\r\nWHERE m.START_DATE BETWEEN '/*START_DATE*/' AND '/*END_DATE*/'\r\n  -- include all membership starts except when:\r\n  --   (a) there is a transfer note on the same date, AND\r\n  --   (b) there is NO membership sale on that same date for this GUID\r\n  AND NOT (\r\n        n.CUSTOMER_ID IS NOT NULL\r\n    AND ms.customer_guid IS NULL\r\n  );",
  "merge_sql": "",
  "json": "{\r\n  \"facility_column_tag\": \"/*FACILITY_TAG*/\",\r\n  \"wants_start_date\": {\r\n    \"tag\": \"/*START_DATE*/\"\r\n  },\r\n  \"wants_end_date\": {\r\n    \"tag\": \"/*END_DATE*/\"\r\n  }\r\n}",
  "name": "New Members (No Transfers)"
}