Newbie Question – how do I link a pledge payment to its original pledge in code?

Jeff Chestney
Jeff Chestney
Third Anniversary
in Blackbaud CRM™ General Discussions

I am writing a stored procedure for reunion reporting and I need to only count pledge payments if their associated pledge was made within the current reunion cycle. If someone made a multi-year pledge during their previous reunion cycle and is still paying it off, I do not want to include those payments in the current counts/totals because the pledge total was already counted in the previous reunion.

Comments

  • Spencer Lassen
    Spencer Lassen
    Tenth Anniversary Kudos 1 Name Dropper Participant

    The application for a pledge payment is found on the REVENUESPLIT table, join from there to INSTALLMENTSPLITPAYMENT on the REVENUESPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID to find the installment and then join to the pledge which is in the revenue table by INSTALLMENTSPLITPAYMENT.PLEDGEID = REVENUE.ID

    below is a snippet on how to pull all fields from the revenuesplit table with this join where the pledge data is between a couple parameters. Hope this helps --Spencer

    SELECT RS.*
    FROM REVENUESPLIT RS INNER JOIN
    INSTALLMENTSPLITPAYMENT ISP ON RS.ID = ISP.PAYMENTID INNER JOIN
    REVENUE R ON ISP.PLEDGEID = R.ID
    WHERE R.DATE BETWEEN @STARTDATE AND @ENDDATE

Categories