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.
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 TOTBAL000001235 04/14/2014 335 A0A00 605000001234 04/14/2014 243 A0A01 243000001236 04/14/2014 425 A0A02 500TRANAMT 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 CHGCODETABLE2NAMEID TRANDATE TRANAMT RMPROPID CHGCODE OPENAMT000001234 04/01/2014 400 A0A01 ARC 0000001234 04/05/2014 -142 A0A01 ARC 228000001234 04/10/2014 15 A0A01 ALT 15000001235 04/01/2014 400 A0A00 ARC 400000001235 04/05/2014 50 A0A00 ALT 50000001235 04/10/2014 105 A0A00 ACF 105000001235 04/11/2014 50 A0A00 ADR 50000001236 04/01/2014 500 A0A02 ARC 500The other table stores the priority order of the charge codesTABLE3CHGCODE PRIORITY DESCRPTNACF 1 Court fee ALT 2 Late fee ANS 3 NSF fee ARC 4 Rent ADR 5 Repair AUR 6 UtilityWhile the forth stores the customer data:TABLE4NAMEID RMPROPID FIRSTNAME LASTNAME NAMEGROUP000001234 A0A01 Jane Doe 000001234000001235 A0A00 John White 000001235000001236 A0A02 John Smith 000001236000001237 A0A02 Jennifer Smith 000001236This 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 APPLAMT000001235 04/14/2014 335 A0A00 ACF 105000001235 04/14/2014 335 A0A00 ALT 15000001235 04/14/2014 335 A0A00 ARC 215000001234 04/14/2014 243 A0A01 ALT 15000001234 04/14/2014 243 A0A01 ARC 228000001236 04/14/2014 425 A0A02 ARC 425Also 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 CHGCODE2. create a query that counts the number of records (DISTINCT CHGCODEs) per DISTINCT NAMEID in ORDER by the CHGCODE PRIORITY3. 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. |
|
|
|
|
|