Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Conditional case calculations multiple tables

Author  Topic 

Bhartsell
Starting Member

1 Post

Posted - 2014-04-20 : 22:21:03
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.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-21 : 08:59:10
Divide and conquer!

1. create a query that calculates the sum of the OPENAMT per NAMEGROUP per DISTINCT CHGCODE
2. create a query that counts the number of records (DISTINCT CHGCODEs) per DISTINCT NAMEID in ORDER by the CHGCODE
PRIORITY
3. create your main query that uses the first two as subqueries. Basically, if you can solve the first two, the main query is a lot easier.
Go to Top of Page
   

- Advertisement -