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 |
|
greg52
Starting Member
5 Posts |
 Posted - 2007-12-18 : 14:46:15
|
| I have a table in the following format:Client_No Acct_No Amount Invoice_No Dr_Cr--------- ------- ------ ---------- -----7920 1100 255.25 1234 D 8830 1100 343.22 1264 D7920 1100 255.25 1234 C2441 2300 755.00 1511 D8830 1100 125.50 1264 C2441 2300 600.00 1511 C3241 1100 300.00 1527 DI would like to select only the 1100 Acct_No rows and combine the Dr_Cr debits and credit amounts on the save row as such.Client_No Acct_No Dr_Amount Cr_Amount Invoice_No --------- ------- --------- --------- ----------7920 1100 255.25 255.25 1234 8830 1100 343.22 125.50 1264 3241 1100 300.00 0 1527 Can this be done and avoid the use of temporary debit and credit tables then joining them? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-18 : 14:55:11
|
SELECT Client_No, Acct_No,MAX(CASE WHEN Dr_Cr = 'D' THEN Amount ELSE 0 END) AS Dr_Amount,MAX(CASE WHEN Dr_Cr = 'C' THEN Amount ELSE 0 END) AS Cr_Amount,MAX(Invoice_No) AS Invoice_NoFROM Table1WHERE Acct_No = 1100GROUP BY Client_No, Acct_NoORDER BY Client_No, Acct_No
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
greg52
Starting Member
5 Posts |
Posted - 2007-12-18 : 15:10:04
|
| Thank you, Peso |
|
|
|
|
|
|
Combining Rows