Hi All,
We are attempting to write a query that will tell us who has payment plan payments coming up by due date and who has money outstanding on said payment plans for our school.
Here is the code I came up with (which I'm pretty proud of, newbie here....) that has got us almost there:
select C.customer_no, C.fname, C.lname, E.address, O.order_no, O.MOS, O.tot_paid_amt as 'Total Paid To Date', O.tot_due_amt as 'Total Order Amount', S.due_dt, S.amt_due as 'Due on next run',B.order_amt, B.order_paid, B.bill_amt, B.past_due from T_Customer as C join T_EADDRESS as E on C.customer_no = E.customer_no join T_Order as O on C.customer_no = O.customer_no join T_ORDER_SCHEDULE as S on O.order_no = S.order_no join T_ORDER_BILL_DETAIL as B on S.order_no = B.order_no where S.due_dt between '2023-06-14' and '2023-07-16' and O.MOS = '11' and E.primary_ind = 'Y' order by C.customer_no asc
But we are stuck on the past due line. This constituent missed their July payment, so theoretically they should have a past due amount associated with their August payment, but it's not pulling anything in:
Any thoughts on what we are doing wrong? Or how I can amend the code so that it shows us that information?
Thanks!
Nicki LeGrand
I can't advise in detail without access to your system and data, but if I were you, I would avoid trying to join T_ORDER_SCHEDULE and T_ORDER_BILL_DETAIL directly to T_ORDER. We use order schedules on subscription renewal orders, and when I look at those, I stick mostly to T_ORDER_SCHEDULE.
E.g. I might look for Sub MOS orders with order dates within our renewal period, and among those with due < paid, find the orders with a schedule, and only then look at T_ORDER_SCHEDULE and amounts due.