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)
 Query ,,,,,,,,,,, |
|
|
| Author |
Topic |
|
ashishashish
Constraint Violating Yak Guru
408 Posts |
 Posted - 2009-02-17 : 04:31:52
|
| How can i write this query in some another way....mean any optimization of this query....select dealer_id,'Debit'= case when amount > 0 then amount end ,'Credit'= case when amount < 0 then amount end from dbo.tbl_paymentThanks In Advance |
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-02-17 : 06:07:38
|
| This looks good to me. |
 |
|
|
ashishashish
Constraint Violating Yak Guru
408 Posts |
Posted - 2009-02-17 : 06:17:12
|
| I just want to know if i can do this in some other way......mean i just donn like to use 2 case statements in a single query so if anybody has any other query of this task so ,,,plzz let me know,,,,,,,,,Thanks For Reply... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-17 : 08:53:23
|
quote:
Originally posted by ashishashish
How can i write this query in some another way....mean any optimization of this query....select dealer_id,'Debit'= case when amount > 0 then amount end ,'Credit'= case when amount < 0 then amount end from dbo.tbl_paymentThanks In Advance
seems like what you're looking for is thisselect dealer_id,'Debit'= sum(case when amount > 0 then amount else 0 end) ,'Credit'= sum(case when amount < 0 then amount else 0 end)from dbo.tbl_paymentgroup by dealer_id |
|
|
|
ashishashish
Constraint Violating Yak Guru
408 Posts |
Posted - 2009-02-17 : 09:13:11
|
| Sorry But M not looking for sum,,,,,,,,,,,i dunn want to sum d amount,,,i just like to see all the transaction,,,,,,,jus.,....like my query does.Thanks. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-17 : 09:15:34
|
quote:
Originally posted by ashishashish
Sorry But M not looking for sum,,,,,,,,,,,i dunn want to sum d amount,,,i just like to see all the transaction,,,,,,,jus.,....like my query does.Thanks.
how will be your table data? can you post 5 or 10 rows? |
|
|
|
ashishashish
Constraint Violating Yak Guru
408 Posts |
Posted - 2009-02-17 : 09:48:04
|
| yeait same as u suggested me query...mean like this.....Dealer_id, Ref_id, ref_name, ref_per, Amount, Balance20, 10, aa, 4.5, 295, 29520, 10, aa, 4.5, -295, 021, 17, bb, 4.5, 500, 50021, 17, bb, 4.5, 200, 70021, 17, bb, 4.5, -600, 100So i like that Output Must Be like This.....Dealer_id Debit Credit20 295 020 0 29521 500 021 200 021 0 600My query does it ........select dealer_id,'Debit'= case when amount > 0 then amount end ,'Credit'= case when amount < 0 then amount end from dbo.tbl_paymentBut i like to have any alternative way of this mean ....i just dont want to use 2 case statements in a single query so .....Any Suggestions ..Thanks In Advance....... |
|
|
|
ashishashish
Constraint Violating Yak Guru
408 Posts |
Posted - 2009-02-19 : 12:13:40
|
| Vishak Sir do u find any another method to do this.....i also provided u with my table structure and data?Thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-19 : 12:18:25
|
sorry i had missed this. use the below:-select Dealer_id,COALESCE(NULLIF(SIGN(Amount),-1.0),0) as Debit,COALESCE(NULLIF(SIGN(Amount),1.0),0) as Creditfrom Table |
|
|
|
|
|
|
|
|
