April 20, 2014 at 8:11 pm
I have 4 tables involved here. The priority table is TABLE1:
NAMEID TRANDATE TRANAMT RMPROPID TOTBAL
000001235 04/14/2014 335 A0A00 605
000001234 04/14/2014 243 A0A01 243
000001236 04/14/2014 425 A0A02 500
TRANAMT being the amount paid & TOTBAL being the balance due per the NAMEID & RMPROPID specified.
The other table includes a breakdown of the total balance, in a manner of speaking, by charge code (thru a SUM(OPENAMT) query of DISTINCT CHGCODE
TABLE2
NAMEID TRANDATE TRANAMT RMPROPID CHGCODE OPENAMT
000001234 04/01/2014 400 A0A01 ARC 0
000001234 04/05/2014 -142 A0A01 ARC 228
000001234 04/10/2014 15 A0A01 ALT 15
000001235 04/01/2014 400 A0A00 ARC 400
000001235 04/05/2014 50 A0A00 ALT 50
000001235 04/10/2014 105 A0A00 ACF 105
000001235 04/11/2014 50 A0A00 ADR 50
000001236 04/01/2014 500 A0A02 ARC 500
The other table stores the priority order of the charge codes
TABLE3
CHGCODE PRIORITY DESCRPTN
ACF 1 Court fee
ALT 2 Late fee
ANS 3 NSF fee
ARC 4 Rent
ADR 5 Repair
AUR 6 Utility
While the forth stores the customer data:
TABLE4
NAMEID RMPROPID FIRSTNAME LASTNAME NAMEGROUP
000001234 A0A01 Jane Doe 000001234
000001235 A0A00 John White 000001235
000001236 A0A02 John Smith 000001236
000001237 A0A02 Jennifer Smith 000001236
This table's importance comes by the inclusion of the NAMEGROUP. This way if an account has multiple NAMEIDs, it can be kept straight by their shared NAMEGROUP.
I am trying to create a report using queries that will: A) calculate the sum of the OPENAMT per NAMEGROUP per DISTINCT CHGCODE B) count the number of records (DISTINCT CHGCODEs) per DISTINCT NAMEID in ORDER by the CHGCODE PRIORITY Then C) calculate a case query whereas:
CASE WHERE
TABLE1.TRANAMT=> the calculated sum of the highest priority CHGCODE THEN 'TABLE1.TRANAMT'
ELSE WHERE TABLE1.TRANAMT <= the calculated sum of the highest priority CHGCODE
THEN 'the calculated sum of the highest priority CHGCODE'
...then...
CASE WHERE
TABLE1.TRANAMT less the sum of the highest priority CHGCODE => the calculated sum of the 2nd priority CHGCODE (if not null)
THEN
'TABLE1.TRANAMT less the sum of the highest priority CHGCODE'
ELSE
WHERE TABLE1.TRANAMT less the sum of the highest priority CHGCODE <= the calculated sum of the 2nd priority CHGCODE
THEN
'the calculated sum of the 2nd priority CHGCODE'
The results should be something like:
NAMEID TRANDATE TRANAMT RMPROPID CHGCODE APPLAMT
000001235 04/14/2014 335 A0A00 ACF 105
000001235 04/14/2014 335 A0A00 ALT 15
000001235 04/14/2014 335 A0A00 ARC 215
000001234 04/14/2014 243 A0A01 ALT 15
000001234 04/14/2014 243 A0A01 ARC 228
000001236 04/14/2014 425 A0A02 ARC 425
Also with a remaining balance (per CHGCODE) column.
Unless there's an alternative solution that someone could suggest that would effectively split the TABLE1.TRANAMT up into the respective TABLE2.CHGCODE balances? Either way, I can't figure out how to word the queries. I've been working on this forever & I'm stuck.
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply