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
Thank you, Jared. This is complicated because of the duplication of rows. I was trying to get everything on to one row, but this stacked list is better. And I'm glad to know that the link I am looking for between schedule and payment doesn't exist. I thought I was missing something.
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
From: Jan LaRocque <bounce-janlarocque5661@tessituranetwork.com> Sent: 3/7/2016 5:45:22 PM