Account Balance - SQL Server Forums

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)

Account Balance

Author

Topic

Burzy
Starting Member

6 Posts

Posted - 2012-06-26 : 16:34:11

Hi!

I have problem with query. I have table:

client_ID | account_DT | account_CT | amount
1 | 21 | 50 | 100
2 | 21 | 34 | 300
1 | 45 | 21 | 500
3 | 30 | 50 | 360

And I want to get account balance of certain account, for example 21:

Client_ID | Balance
1 | -400
2 | 300

I can do that with union, but I'd like to know other way.

How can I get such result?

Thank You for Your advice!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-06-26 : 16:54:14

Here's one way:



declare @accountid int
set @accountid = 21

;with yourTable (client_ID, account_DT, account_CT, amount)
as (
select 1 , 21 , 50 , 100 union all
select 2 , 21 , 34 , 300 union all
select 1 , 45 , 21 , 500 union all
select 3 , 30 , 50 , 360
)
select client_id
       ,sum(case 
              when account_dt = @accountid then amount 
              when account_ct = @accountid then amount * -1
              else 0 
              end) as Balance
from yourTable
where  account_DT = @accountid
or     account_CT = @accountid
group by client_id

OUTPUT:
client_id   Balance
----------- -----------
1           -400
2           300

Be One with the Optimizer
TG

Burzy
Starting Member

6 Posts

Posted - 2012-06-27 : 01:18:21

Thanks for that, its working!

But I have another problem. I have same table as previous and another one with latest operations for certain ID.
Something like that:

MyTable2
Client_ID, account_DT, account_CT, amount
1, 30, 21, 500
2, 21, 40, 300
4, 15, 21, 50

As previously, I want to get balance of account 21 but with with these 2 tables.

Example:
Client_id, Balance
1, -900
2, 600
4, -50

How query should be writen to get such result?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-27 : 03:29:29

[code]
select client_id
,sum(case
when account_dt = @accountid then amount
when account_ct = @accountid then amount * -1
else 0
end) as Balance
from
(
select client_ID, account_DT, account_CT, amount from MyTable1 union all
select client_ID, account_DT, account_CT, amount from MyTable2
) t
where account_DT = @accountid
or account_CT = @accountid
group by client_id[/code]

KH
[spoiler]Time is always against us[/spoiler]

Burzy
Starting Member

6 Posts

Posted - 2012-06-27 : 04:52:01

That's all what I want to know. Thanks again! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources