Altering transaction lines to include in sum total

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 2005 Forums

Transact-SQL (2005)

Altering transaction lines to include in sum total

Author

Topic

erica686
Starting Member

13 Posts

Posted - 2009-11-29 : 23:56:18

Hi there,

I have a table as follows:

PERSON_CODE, DR_CR, AMOUNT
JKM, D, 10
JKM, C, 2.5
ALK, C, 6.9
ION, C, 79
ALK, D, 80
ALK, D, 786
ION, C, 23

I need to sum the transaction lines, however 'D' lines must be converted to negative beforehand.

Normally I would just select all transactions, change 'D' to negative, and then group the transactions in my report to create a sum total. Is there any way that I can achieve this same result in the SQL query?

Eg:

PERSON_CODE, SUM_AMOUNT
JKM, 7.5
ALK, -859.1
ION, 102

Thanks

kbhere
Yak Posting Veteran

58 Posts

Posted - 2009-11-30 : 00:28:27

SELECT PERSON_CODE,
(SELECT SUM(AMOUNT) FROM Table WHERE DR_CR LIKE 'C') - (SELECT SUM(AMOUNT) FROM Table WHERE DR_CR LIKE 'D')
FROM Table

Balaji.K

erica686
Starting Member

13 Posts

Posted - 2009-11-30 : 01:05:27

Thanks, that is almost working.

Using the example below, that query is giving me:

JKM,749.6
ALK,749.6
ION,749.6

instead of grouping the sum transactions by the person code:

JKM, 7.5
ALK, -859.1
ION, 102

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-11-30 : 01:15:09

try like this


DECLARE @t table (PERSON_CODE varchar(32), DR_CR varchar(32), AMOUNT decimal(18,2))
insert into @t select 
'JKM', 'D', 10 union all select 
'JKM', 'C', 2.5  union all select 
'ALK', 'C', 6.9  union all select 
'ION', 'C', 79  union all select 
'ALK', 'D', 80  union all select 
'ALK', 'D', 786  union all select 
'ION', 'C', 23

select distinct
 t.person_code, ISNULL(cr,0)-ISNULL(dr,0)as sum_amount
from @t t
LEFT join (Select person_code,sum(amount) as dr from @t where dr_cr ='d'
 group by person_code) d on d.person_code = t.person_code
LEFT join (Select person_code,sum(amount) as cr from @t where dr_cr ='c' 
group by person_code) c on c.person_code = t.person_code

kbhere
Yak Posting Veteran

58 Posts

Posted - 2009-11-30 : 01:50:30

quote:
Originally posted by erica686

Thanks, that is almost working.

Using the example below, that query is giving me:

JKM,749.6
ALK,749.6
ION,749.6

instead of grouping the sum transactions by the person code:

JKM, 7.5
ALK, -859.1
ION, 102

That was the basic idea.. You can alter that as per your need..
All the Best..
With Regards,

Balaji.K

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources