I've been asked to create a report that shows the contribution date, the scheduled due date(s), and the payment received dates(s). I just can't find a link between the payment and the schedule table that will pull those items together. Does anyone have a clue how to do that?
Thanks,
Jan
Hi Jan,
T_SCHEDULE has a transaction_no field, but I bet you have already discovered that it is not going to be useful because it appears to only have recorded the first transaction and none of the follow up payments. This means there isn't a link between the schedule and the payments, so I think we will have to link the schedule to the contribution and then also link the payments to the contribution.
Joining both the schedule and the payments to the contribution in the same select statement will cause a multiplication of rows returned. Maybe that is what you want, or maybe you have a way to deal with that. What I would do instead of trying to get everything all at once is to grab each piece individually--each piece being the contribution info, the schedule info, and the payment info--and then I would union them together in a manner that would let me group them by distinct contribution and information type.
For example, here is one way to do it using Common Table Expressions for the three segments.
--------------------------------------------------------------------------------------
;WITH contributions AS ( Select a.customer_no ,a.ref_no ,a.cont_dt ,a.cont_amt From dbo.VS_CONTRIBUTION a Where a.ref_no in (3772177) ) , contributions_schedule AS ( Select a.customer_no ,a.ref_no ,b.due_dt ,b.amt_due From contributions a JOIN dbo.T_SCHEDULE b on a.ref_no = b.ref_no ) , contributions_payments AS ( Select a.customer_no ,a.ref_no ,d.pmt_dt ,d.pmt_amt From contributions a JOIN dbo.T_TRANSACTION c on a.ref_no = c.ref_no JOIN dbo.T_PAYMENT d on c.transaction_no = d.transaction_no and c.sequence_no = d.sequence_no ) Select a.customer_no ,a.ref_no ,[grouping] = 'Contribution info' ,[date] = a.cont_dt ,[amount] = a.cont_amt From contributions a UNION Select a.customer_no ,a.ref_no ,[grouping] = 'Schedule info' ,[date] = a.due_dt ,[amount] = a.amt_due From contributions_schedule a UNION Select a.customer_no ,a.ref_no ,[grouping] = 'Payment info' ,[date] = a.pmt_dt ,[amount] = a.pmt_amt From contributions_payments a
-------------------------------------------------------------------------
This would produce results that look like this:
----------------------------------------------------------------------
customer_no ref_no grouping date amount ----------- ----------- ----------------- ----------------------- --------------------- 37587 3772177 Contribution info 2015-10-20 00:00:00.000 500.00 37587 3772177 Payment info 2015-10-21 10:39:55.000 50.00 37587 3772177 Payment info 2015-11-16 13:18:41.930 50.00 37587 3772177 Payment info 2015-12-15 09:20:15.860 50.00 37587 3772177 Payment info 2016-01-15 09:20:16.720 50.00 37587 3772177 Payment info 2016-02-16 04:18:09.927 50.00 37587 3772177 Schedule info 2015-10-21 00:00:00.000 50.00 37587 3772177 Schedule info 2015-11-15 00:00:00.000 50.00 37587 3772177 Schedule info 2015-12-15 00:00:00.000 50.00 37587 3772177 Schedule info 2016-01-15 00:00:00.000 50.00 37587 3772177 Schedule info 2016-02-15 00:00:00.000 50.00 37587 3772177 Schedule info 2016-03-15 00:00:00.000 50.00 37587 3772177 Schedule info 2016-04-15 00:00:00.000 50.00 37587 3772177 Schedule info 2016-05-15 00:00:00.000 50.00 37587 3772177 Schedule info 2016-06-15 00:00:00.000 50.00 37587 3772177 Schedule info 2016-07-15 00:00:00.000 50.00 (16 row(s) affected)
This contribution has a schedule with 10 installments. There have been 5 payments made. I added one row for the contribution info and added all together we get 16 rows.
If I had tried to select the data in one statement while joining schedule to contribution and then joining payments to contribution I would have multiplied the returns. Five payments times ten schedule installments would have given us a total of 50 rows.
I'm guessing the return of 16 rows is closer to what you are looking for. This way when you format your presentation layer in either InfoMaker or SSRS you can group first by customer_no/ref_no and then under that group by data segment (what I called "grouping" in the example) to show more clearly what is scheduled and what is paid and then add in subtotals if desired.
Good luck. This report is more difficult than it appears on the surface.
Jared
Just looking around to see if there was anything I was missing here. Is there really no connection between schedules and the actual payments? How does a schedule get updated if a larger payment is made? This is going to be a big nuisance for a data sharing project I'm currently working on.
The schedule doesn't get updated. Unless, of course, a user goes in and deliberately makes a change, which they always can.
The billing utility simply makes a comparison of how much has been paid on the contribution (or order) and the amount that should have been paid by that date by summing up the amounts on the schedule that have installment dates which have passed. The dollar amount of the difference, if greater than zero, is identified as the current amount due and is then processed by the billing utility.
Transactions are processed against the contribution (or order) and not the schedule. This is vital for the finance records having a credit and debit side with GL numbers. The payment method is on one side and the fund (or ticket or fee) is on the other side.
The purpose of the billing schedule is to create a statement of where the value of the amount paid should be by a certain time, and the billing utility is used to bring that amount up to date.