"Payment Plan Manager" Query Help

[drtech]

I made a new module with an interactive "Payment Plan Manager" for my plugin. Currently, it loads similar data to the built in static payment plan report, except it is interactive using the same copy of the "Treatment Finder" windows so it loads the pt Account when clicking on a row.

I am trying to add more data, but pretty much a newbie at writing MySQL queries. Could I get some help here?

i am trying to load 1) the amt (principal + interest) and 2)date of the Latest due payplancharge only...so the last row <= to today's date.

I figured out the amt query in SQLyog, but when I put the code in the code block and debug I am having difficulty in that it tells me can't find a field for the subquery. (does a temp table not have the ability to use other fields from the query?)
The date one I am still stumped on, it keeps returning a minimum date of 01/01/0001....so I think my codes was wrong...but don't really know how to get what I want.

Here is what I have that works

} string command=@"SELECT FName,LName,MiddleI,Preferred, PlanNum,patient.PatNum,
(SELECT SUM(Principal+Interest) FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum
AND ChargeDate <= "+datesql+@") ""_accumDue"",
(SELECT SUM(Principal+Interest) FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum
AND ChargeDate <= "+DbHelper.DateAddDay(datesql,POut.Long(PrefC.GetLong(PrefName.PayPlansBillInAdvanceDays)))+@") ""_dueTen"",
(SELECT SUM(SplitAmt) FROM paysplit WHERE paysplit.PayPlanNum=payplan.PayPlanNum) ""_paid"",
(SELECT SUM(Principal) FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum) ""_principal""
FROM payplan
LEFT JOIN patient ON patient.PatNum=payplan.Guarantor "
//WHERE SUBSTRING(Birthdate,6,5) >= '"+dateFrom.ToString("MM-dd")+"' "
//+"AND SUBSTRING(Birthdate,6,5) <= '"+dateTo.ToString("MM-dd")+"' "
+"GROUP BY FName,LName,MiddleI, Preferred, payplan.PayPlanNum ORDER BY LName,FName";

When I add the italicizes line it says payplan.PayPlanNum not found, but it works in SQLyog...why the difference?(does a temp table not have the ability to use other fields from the query?)


} string command=@"SELECT FName,LName,MiddleI,Preferred, PlanNum,patient.PatNum,
(SELECT SUM(Principal+Interest) FROM (SELECT Principal, Interest FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum
AND ChargeDate <= "+datesql+@" ORDER BY ChargeDate DESC LIMIT 1) tmp1) ""_latestDueAmt"",
(SELECT SUM(Principal+Interest) FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum
AND ChargeDate <= "+datesql+@") ""_accumDue"",
(SELECT SUM(Principal+Interest) FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum
AND ChargeDate <= "+DbHelper.DateAddDay(datesql,POut.Long(PrefC.GetLong(PrefName.PayPlansBillInAdvanceDays)))+@") ""_dueTen"",
(SELECT SUM(SplitAmt) FROM paysplit WHERE paysplit.PayPlanNum=payplan.PayPlanNum) ""_paid"",
(SELECT SUM(Principal) FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum) ""_principal""
FROM payplan
LEFT JOIN patient ON patient.PatNum=payplan.Guarantor "
//WHERE SUBSTRING(Birthdate,6,5) >= '"+dateFrom.ToString("MM-dd")+"' "
//+"AND SUBSTRING(Birthdate,6,5) <= '"+dateTo.ToString("MM-dd")+"' "
+"GROUP BY FName,LName,MiddleI, Preferred, payplan.PayPlanNum ORDER BY LName,FName";


Also, this is the other subquery I am trying to use and getting a min date result of 01/01/0001 when I run the code
datesql="CURDATE()"
(SELECT ChargeDate FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum
AND ChargeDate <= "+datesql+@" ORDER BY ChargeDate DESC LIMIT 1) ""_latestDueDate"",

reading online I think the above is completely wrong and why I am getting a bad date. This might be more what I want (but would have the same result as above I think)
datesql="CURDATE()"
(SELECT MAX(ChargeDate) FROM (SELECT ChargeDate FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum
AND ChargeDate <= "+datesql+@" ORDER BY ChargeDate DESC) tmp2) ""_latestDueDate"",

Any help would be appreciated...thanks in advance.
Do I use Join instead of temp tables?

[drtech]

This gives me all 01/01/0001 dates also! what is the problem? I thought this would work since it is almost identical to the original queries!!!
(SELECT MAX(ChargeDate) FROM payplancharge WHERE payplancharge.PayPlanNum=payplan.PayPlanNum AND ChargeDate <= "+datesql+@") ""_latestDueDate"",

[drtech]

Any help on how to do this from SQL wizards?

Just need
1) Date of last payment due
2) Amt of last payment due (principal + interest)

related to the payment plan data in the rest of the query...

[B.Thomas]

Nice work on the payment plan report. I like how double clicking brings you to the patient in question. I wish I could help you with the Query stuff, but I too am a noob to this. One thing I noticed is the insurance payment plans come up as -734382 days past due.

[drtech]

yes, I haven't worked on this in a lonq while and I don't remember why those really negative numbers are showing up in the list, however it is partially functional and does help...thanks!