Query-SubQuery and Cases - 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

General SQL Server Forums

New to SQL Server Programming

Query-SubQuery and Cases

Author

Topic

nayabsiddiqi
Starting Member

1 Post

Posted - 2007-10-22 : 03:47:45

Hi everyeone,

do you know if I can do the following task through a single query

TableA(LocID,LocNAME)
TableB(ID,LocID,Amount)

What i need to do is to add sum amount having same locID from TableB and get LocIDs name through TableA.LocName. In the query there should be one thing more, if amount is less than zero put it into credit column, while if positive, puts in debit column

Thus Result(LocId, LocName, Debit, Credit) is the requied structure.
Can anyone help me out. I m not getting how to get the LocName if gets the sum by Groupby LocID also applying condition is confusing me:s

Looking forward for response
take care :)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-22 : 04:03:08

select ....
case when amount>=0 then amount as debit,
case when amount<0 then amount as credit
from yourtable

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-22 : 04:49:07

you might want to remove the negative sign when put in credit column. Use abs() or just - it.

select ....
case when amount >= 0 then  amount as debit,
case when amount <  0 then -amount as credit
from yourtable

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

aarumugavel
Starting Member

8 Posts

Posted - 2007-10-22 : 05:01:13

select locid,locname,case when amount < 0 then amount else 0 end credit,
case when amount > 0 amount else 0 end debit from
(
select a.locid,locname,sum(amount) amount from tableA A inner join TableB B on
A.LocId,B.LocId group by A.Locid,LocName
)x

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-22 : 06:18:07

quote:
Originally posted by khtan

you might want to remove the negative sign when put in credit column. Use abs() or just - it.
select ....
case when amount >= 0 then amount as debit,
case when amount < 0 then -amount as credit
from yourtable

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

Yes it is

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources