Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Combining two queries and make results join into one column

Status
Not open for further replies.
kbsc

kbsc

Technical User
Sep 21, 2007
30
US
I've created a very long and complicated Derived Query in order to Combine Two Queries and but my problem is its not giving me the correct results. My extremely long query is below, but here are my results and then I'll show you what I need it to do. Thanks in advance for any help!

First of all the query is getting me the TOP 9 results based on a money value, and then I need it to SUM all value - top 9 and display. The value = 1332151.8700 (is my SUM all values - top 9 values)

160102189.9800 1332151.8700
37099823.4100 1332151.8700
47792.0700 1332151.8700
43098.9700 1332151.8700
42694.0800 1332151.8700
30458.4600 1332151.8700
28810.2900 1332151.8700
26504.0900 1332151.8700
25238.4300 1332151.8700

But I need the query results to do the following:

160102189.9800
37099823.4100
47792.0700
43098.9700
42694.0800
30458.4600
28810.2900
26504.0900
25238.4300
1332151.8700

Here's my extremely long query, any suggestion on making it smaller would be great. The reason its so long is because I'm summing data that has different parameters, so I then had to put it inside another query to SUM the different values again.

SELECT Top9.Top9Total, TR_Minus_Top9.TRMinusTop9
FROM
(SELECT TOP 9 trans.vchTrustee, trans.vchCaseNumber, Trans.TotalReceipts, SUM(a.mnyBalanceAmount) As totalAssetBalance,
Trans.TotalReceipts + SUM(a.mnyBalanceAmount) As Top9Total

FROM tblCase c1

LEFT JOIN tblAsset a ON c1.intCaseID = a.intCaseID
LEFT JOIN
(Select trans40.vchTrustee, trans40.vchCaseNumber, SUM(trans40.TdMnyAmount) As TotalReceipts

From
(SELECT c.vchTrustee, c.vchCaseNumber, SUM(td.mnyAmount) As TdMnyAmount
FROM tblTransactionDetail td

INNER JOIN tblTransaction t ON td.intTransactionID = t.intTransactionID
INNER JOIN tblBankAccount b ON t.intAccountID = b.intAccountID
INNER JOIN tblCase c ON b.intCaseID = c.intCaseID

GROUP BY c.intDeleted, b.intDeleted, td.intDeleted, t.intDeleted,
c.vchTrustee, c.vchCaseNumber, c.intCaseID, td.intTransactionType,
t.intTransactionMethod, c.intStatus

HAVING t.intDeleted = 0 AND td.intDeleted = 0 AND b.intDeleted = 0 AND c.intDeleted = 0
AND td.intTransactionType = 200
AND (t.intTransactionMethod = 40 OR t.intTransactionMethod = 41 OR t.intTransactionMethod = 43)
AND c.vchTrustee = 'RLW' AND c.intStatus = 1

) As Trans40

GROUP BY Trans40.vchTrustee, Trans40.vchCaseNumber
) As Trans ON c1.vchCaseNumber = Trans.vchCaseNumber

GROUP BY trans.vchTrustee, trans.vchCaseNumber, a.intDeleted, c1.intDeleted, trans.TotalReceipts, c1.intStatus

HAVING a.intDeleted = 0 AND c1.intDeleted = 0 AND trans.vchTrustee = 'RLW' AND c1.intStatus = 1
ORDER BY Trans.TotalReceipts + SUM(a.mnyBalanceAmount) DESC
) As Top9

Left Join

(SELECT Top9.vchTrustee, (AllCases.TR - SUM(Top9.Top9Total)) As TRMinusTop9
FROM

(SELECT AllTotalReceipts.vchTrustee, SUM(AllTotalReceipts.TotalMoney) As TR
FROM

(SELECT trans.vchTrustee, trans.vchCaseNumber, Trans.TotalReceipts, SUM(a.mnyBalanceAmount) As totalAssetBalance,
(Trans.TotalReceipts + SUM(a.mnyBalanceAmount)) As TotalMoney

FROM tblCase c1

LEFT JOIN tblAsset a ON c1.intCaseID = a.intCaseID
LEFT JOIN
(Select trans40.vchTrustee, trans40.vchCaseNumber, SUM(trans40.TdMnyAmount) As TotalReceipts

From
(SELECT c.vchTrustee, c.vchCaseNumber, SUM(td.mnyAmount) As TdMnyAmount
FROM tblTransactionDetail td
INNER JOIN tblTransaction t ON td.intTransactionID = t.intTransactionID
INNER JOIN tblBankAccount b ON t.intAccountID = b.intAccountID
INNER JOIN tblCase c ON b.intCaseID = c.intCaseID

GROUP BY c.intDeleted, b.intDeleted, td.intDeleted, t.intDeleted,
c.vchTrustee, c.vchCaseNumber, c.intCaseID, td.intTransactionType,
t.intTransactionMethod, c.intStatus

HAVING t.intDeleted = 0 AND td.intDeleted = 0 AND b.intDeleted = 0 AND c.intDeleted = 0
AND td.intTransactionType = 200
AND (t.intTransactionMethod = 40 OR t.intTransactionMethod = 41 OR t.intTransactionMethod = 43)
AND c.vchTrustee = 'RLW' AND c.intStatus = 1

) As Trans40

GROUP BY Trans40.vchTrustee, Trans40.vchCaseNumber
) As Trans ON c1.vchCaseNumber = Trans.vchCaseNumber

GROUP BY trans.vchTrustee, trans.vchCaseNumber, a.intDeleted, c1.intDeleted, trans.TotalReceipts, c1.intStatus
HAVING a.intDeleted = 0 AND c1.intDeleted = 0 AND trans.vchTrustee = 'RLW' AND c1.intStatus = 1
) As AllTotalReceipts

GROUP BY AllTotalReceipts.vchTrustee) As AllCases

LEFT JOIN

(SELECT TOP 9 trans.vchTrustee, trans.vchCaseNumber, Trans.TotalReceipts, SUM(a.mnyBalanceAmount) As totalAssetBalance,
Trans.TotalReceipts + SUM(a.mnyBalanceAmount) As Top9Total

FROM tblCase c1

LEFT JOIN tblAsset a ON c1.intCaseID = a.intCaseID
LEFT JOIN
(Select trans40.vchTrustee, trans40.vchCaseNumber, SUM(trans40.TdMnyAmount) As TotalReceipts

From
(SELECT c.vchTrustee, c.vchCaseNumber, SUM(td.mnyAmount) As TdMnyAmount
FROM tblTransactionDetail td

INNER JOIN tblTransaction t ON td.intTransactionID = t.intTransactionID
INNER JOIN tblBankAccount b ON t.intAccountID = b.intAccountID
INNER JOIN tblCase c ON b.intCaseID = c.intCaseID

GROUP BY c.intDeleted, b.intDeleted, td.intDeleted, t.intDeleted,
c.vchTrustee, c.vchCaseNumber, c.intCaseID, td.intTransactionType,
t.intTransactionMethod, c.intStatus

HAVING t.intDeleted = 0 AND td.intDeleted = 0 AND b.intDeleted = 0 AND c.intDeleted = 0
AND td.intTransactionType = 200 AND (t.intTransactionMethod = 40 OR t.intTransactionMethod = 41 OR t.intTransactionMethod = 43)
AND c.vchTrustee = 'RLW' AND c.intStatus = 1

) As Trans40

GROUP BY Trans40.vchTrustee, Trans40.vchCaseNumber
) As Trans ON c1.vchCaseNumber = Trans.vchCaseNumber

GROUP BY trans.vchTrustee, trans.vchCaseNumber, a.intDeleted, c1.intDeleted, trans.TotalReceipts, c1.intStatus

HAVING a.intDeleted = 0 AND c1.intDeleted = 0 AND trans.vchTrustee = 'RLW' AND c1.intStatus = 1
ORDER BY Trans.TotalReceipts + SUM(a.mnyBalanceAmount) DESC
) As Top9

ON AllCases.vchTrustee = Top9.vchTrustee
GROUP By Top9.vchTrustee, AllCases.TR

) As TR_Minus_Top9

ON Top9.vchTrustee = TR_Minus_Top9.vchTrustee

 
Sort by date Sort by votes
Look up WITH ROLLUP (under GROUP BY) and the GROUPING() function.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

peac3
  • Locked
  • Question
split one row into multiple rows... help pls 2
Replies
12
Views
566
Chris Miller
Chris Miller
coorsman
  • Locked
  • Question
Column Data Type. 2
Replies
9
Views
361
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
Consolidate multiple rows into single row 1
Replies
4
Views
369
Chris Miller
Chris Miller
grnzbra
  • Locked
  • Question
How Do I Add Column to Middle of Table
Replies
4
Views
381
BobSuruncle
BobSuruncle
eboughey1008
  • Locked
  • Question
Import columns from one table into another table with dissimilar field layout
Replies
7
Views
175
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top