Pledge Payment Dates in Analytics

Best I've managed to get is the schedule dates,  amount paid,  balance amount and total amount

Contribution date does the schedule for me but I've not been able to get the schedule amount

Thanks for your input Heath!

I was able to find the full schedule of Pledge payments is in T_SCHEDULE. In Analytics, you can pull the Constituent ID, Contribution Number, and Contribution Date (filtered by Pledge status). The Contribution Number in Analytics is the ref_no in T_CONTRIBUTION and T_SCHEDULE.

In SSMS, I did the following:

SELECT C.customer_no, CUST.sort_name,C.ref_no, S.due_dt as [Due Date], S.amt_due
FROM T_CONTRIBUTION AS C
JOIN T_SCHEDULE AS S ON C.ref_no = S.ref_no
JOIN T_CUSTOMER AS CUST ON CUST.customer_no = C.customer_no
WHERE status = 'S'
/*S = Scheduled*/
AND
C.customer_no IN (
--insert your comma delimited list here
)
GROUP BY C.customer_no, CUST.sort_name, C.ref_no, S.due_dt, S.amt_due
;

Sort_name is not the best way to display the name. I only used it as a proof of concept.

As it turns out, this was not highly needed; so I'll file this knowledge in a folder and move on to the next thing. Oh well, hopefully, this information will help someone else.

Thanks for your input Heath!

I was able to find the full schedule of Pledge payments is in T_SCHEDULE. In Analytics, you can pull the Constituent ID, Contribution Number, and Contribution Date (filtered by Pledge status). The Contribution Number in Analytics is the ref_no in T_CONTRIBUTION and T_SCHEDULE.

In SSMS, I did the following:

SELECT C.customer_no, CUST.sort_name,C.ref_no, S.due_dt as [Due Date], S.amt_due
FROM T_CONTRIBUTION AS C
JOIN T_SCHEDULE AS S ON C.ref_no = S.ref_no
JOIN T_CUSTOMER AS CUST ON CUST.customer_no = C.customer_no
WHERE status = 'S'
/*S = Scheduled*/
AND
C.customer_no IN (
--insert your comma delimited list here
)
GROUP BY C.customer_no, CUST.sort_name, C.ref_no, S.due_dt, S.amt_due
;

Sort_name is not the best way to display the name. I only used it as a proof of concept.

As it turns out, this was not highly needed; so I'll file this knowledge in a folder and move on to the next thing. Oh well, hopefully, this information will help someone else.

That's wonderful Neil. I should ve also said i have a SSRS report that i created that pulls out the schedules, balance, unpaid etc for a campaign.  I wrote it for capital campaigns a while back and can send it if you need

Looks a little something like this.  Add a campaign variable and it becomes a fun little report

SELECT
sal.esal1_desc,
a.ref_no,
a.customer_no,
CONVERT(DATE,a.cont_dt) as pledge_date,
b.amt_due as pledge_payment_amount_due, b.amt_recd as pledge_payment_amount_received,
CONVERT(DATE,b.due_dt) as pledge_payment_due_date,
--CONVERT(DATE,t.trn_dt) as pledge_payment_transaction_date,
d.description as pledge_status,
CAST(a.recd_amt as money) as total_amount_received,
CAST(a.cont_amt as money) as original_pledge_amount,
a.cont_amt - a.recd_amt as pledge_balance,
g.description as campaign_description,
h.description as appeal_description,
i.description as fund_description,
e.street1 + ' ' + ISNULL(e.street2, '') + ' ' + e.city + ' ' + e.state + ' ' + e.postal_code as address,
f.address as email

FROM T_CONTRIBUTION a
JOIN T_SCHEDULE b on a.ref_no = b.ref_no
JOIN T_CUSTOMER c on a.customer_no = c.customer_no
LEFT JOIN TR_PLEDGE_STATUS d on a.pledge_status = d.id
JOIN T_ADDRESS e on c.customer_no = e.customer_no and primary_ind = 'Y'
LEFT JOIN T_EADDRESS f on c.customer_no = f.customer_no and f.primary_ind = 'Y'
JOIN T_CAMPAIGN g on a.campaign_no = g.campaign_no
JOIN T_APPEAL h on a.appeal_no = h.appeal_no
JOIN T_FUND i on a.fund_no = i.fund_no
Left Join TX_CUST_SAL sal on c.customer_no = sal.customer_no
WHERE a.cont_type = 'P'
and a.recd_amt <> a.cont_amt
and a.campaign_no = --insert your campaign parameter here