NEFEC

Change date range using year and month columns. 

SELECT        

    n."LAST-NAME" AS "NAME", 

    (LTRIM(LTRIM(a."STREET-DIR") || ' ' ||

    LTRIM(a."STREET-NUMBER")|| ' ' ||

    LTRIM(a."STREET-NAME")||

    RTRIM(LTRIM(a."POBOX")))) AS "ADDRESS/PO BOX", 

    z."ZIP-CITY" AS "CITY", 

    z."ZIP-STATE" AS "STATE", 

    z."ZIP-COUNTRY" AS "COUNTRY", 

    z."ZIP-CODE" AS "ZIP", 

    n."EIN" AS "TIN", 

    n."PRIMARY-PHONE" AS "TELEPHONE", 

    n."NALPHAKEY" AS "NALPHAKEY", 

    SUM(ct."FFVMTC-PAYMENTS") AS "SPEND"

FROM            

    "PUB"."FFVMTC-CAL-TOTALS" AS ct,

    "PUB"."NAME" AS n, 

    "PUB"."ADDRESS" AS a, 

    "PUB"."ZIP" AS z

WHERE        

    ct."FFVMVM-VEN-ID" = n."NAME-ID" AND 

    n."ADDRESS-ID" = a."ADDRESS-ID" AND 

    a."ZIP-CODE" = z."ZIP-CODE" AND 

    ((ct."FFVMTC-CAL-YEAR" = 2023) AND (ct."FFVMTC-CAL-MONTH" > 10) OR 

    (ct."FFVMTC-CAL-YEAR" = 2024) AND (ct."FFVMTC-CAL-MONTH" <= 10))

GROUP BY 

    ct."FFVMVM-VEN-ID", 

    n."NAME-ID", n."LAST-NAME", 

    a."STREET-DIR", 

    a."STREET-NUMBER", 

    a."STREET-NAME", 

    z."ZIP-CITY", 

    z."ZIP-STATE", 

    z."ZIP-COUNTRY", 

    z."ZIP-CODE", 

    n.EIN, 

    n."PRIMARY-PHONE", 

    n.NALPHAKEY,

    a."POBOX"

ORDER BY spend DESC