amount add row by row(BASE ON CREDIT AND DEBIT )

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

SQL Server 2008 Forums

Transact-SQL (2008)

amount add row by row(BASE ON CREDIT AND DEBIT )

Author

Topic

kond.mohan
Posting Yak Master

213 Posts

Posted - 2012-01-11 : 05:45:46

Hi ,

i am executing one query in SQL SERVER (2008)

i am unable to fetch the amount column like below example

name, a/cno ,amount

aaa 101 1200
aaa 101(debit) 1500(1200+300)
aaa 101(credit) 800(1500-700)
aaa 101(debit) 1800(800+1000)
bbb 102 25
bbb 102(debit) 99(25+74)

can any body knows the amont calculation logic
pls provide reference

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-11 : 05:55:57

is there a column which distinguishes credit and debit? or is it just included as a description as shown above?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kond.mohan
Posting Yak Master

213 Posts

Posted - 2012-01-11 : 06:04:41

quote:
Originally posted by kond.mohan

Hi ,

i am executing one query in SQL SERVER (2008)

i am unable to fetch the amount column like below example

name, a/cno ,amount tran_particulat

aaa 101 1200 dr
aaa 101(debit) 1500(1200+300) Dr
aaa 101(credit) 800(1500-700) cr
aaa 101(debit) 1800(800+1000) dt
bbb 102 25 dt
bbb 102(debit) 99(25+74) dt

can any body knows the amont calculation logic
pls provide reference

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-11 : 07:29:17

do you've date of id field to indicate the order of transaction happening?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kond.mohan
Posting Yak Master

213 Posts

Posted - 2012-01-11 : 07:54:58

i have used tranction date column

quote:
Originally posted by visakh16

do you've date of id field to indicate the order of transaction happening?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-11 : 11:23:16

can you post some sample data with all the required columns? that should make it easier for us to come up with the suggestion


SELECT t.*,t1.amount
FROM Table t
CROSS APPLY (SELECT SUM(CASE WHEN [tran_particulat] = 'dr' THEN amount [tran_particulat] = 'cr' THEN -1 * amount ELSE 0 END) AS [amount]
             FROM Table
             WHERE name = t.name
             AND [a/cno] = t.[a/cno]
             AND [tranction date]<= t.[tranction date]
             )t1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kond.mohan
Posting Yak Master

213 Posts

Posted - 2012-01-12 : 09:05:50

hi
i am sending sample data and expected out put
expected out put is similar ATM Transcation out put
i am debit 100Rs to myaccuount showing balance= bal-creditamou(100)
i am credot 500Rs to myaccuount showing balance= bal+creditamou(100)

acno ,tran_amt, trandate_bal ,particular,trandate, balance
10 , 5000 ,100 ,Debit ,10-jan-2011 , 4990
10 , 4990 ,200 ,Debit ,10-jan-2011 , 4700
10 ,4700 ,500 ,Debit ,13-jan-2011 , 4200
10 ,4200 ,10000 ,Credit ,13-dec-2011 , 14200
10 ,14200 ,200 ,debit ,13-dec-2011 , 14000
20 ,2000 ,500 ,debit ,20-dec-2011 , 1500
20 ,1500 ,200 ,credit ,20-dec-2011 , 1700

here i am exepected output is BALANCE column
i am generating report have 6 columns
Balance column show based on tran particular type show the amount
balance column NEED logic
i think this should possible in CURSOR OR CTE

can any body pls explain the logic.

mohan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-12 : 12:50:15

where will you get initial transactional amount from? ie 5000 in above example

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources