December 15, 2015 at 8:55 pm
I am using MS SQL Server 2012 Express Advanced Edition.
My current table structure of one of the accounts table with data is as follows.
PARTY DEBIT CREDIT
----------------------------------------
A 1000 NULL
B NULL 2500
C 2000 NULL
A NULL 5000
B 3500 NULL
A 10000 NULL
A 4500 NULL
I want to achieve following query result.
PARTY DEBIT CREDIT BALANCE
-----------------------------------------------
A 1000 NULL 10500
B NULL 2500 1000
C 2000 NULL 2000
A NULL 5000 NULL
B 3500 NULL NULL
A 10000 NULL NULL
A 4500 NULL NULL
I want total closing balance of a party occuring just once against that party name, not repeating balance on the same party.
For Example, Closing balance of PARTY A, will be 10500, and PARTY A appears multiple times, so I want closing balance to appear with only first record of PARTY A as shown in desired result. And so on with other parties.
In short, I want closing balance to appear only once against party name irrespective of the multiple appearance of that party.
I tried with following query but it gives balance on each occurrence of party.
select
PARTY,
DEBIT,
CREDIT,
(select PARTY,SUM(DEBIT)-SUM(CREDIT) from ACCOUNTS where PARTY=A.PARTY group by PARTY) as BALANCE
from
ACCOUNTS A
December 16, 2015 at 1:14 am
You need a criteria to choose which row must show closing balance. Isn't DATE column missing?
December 16, 2015 at 1:25 am
Hi,
I think it is like removing the duplicate rows from the table.
The query you have pasted is correct one. Just use a ranking function in your query.
(I am not sure which ranking function will help you. Just try)
And then use update function to make your balance field zero for repeated party.
Thanks
Rajneesh
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply