July 19, 2012 at 3:32 am
accountno transaction amount
=====================================
a1414141 Cr 12334
A64646 Dr 4500
E3636 Cr 900
R9494 Dr 8000
here Cr is "credit",
Dr is "Debit"
now i need to show seprate column of credit and debit amounts
July 19, 2012 at 3:37 am
If you could please read the second link in my signature on posting code, data and expected results it will help us out greatly.
But based on what I think you want, try the below, if not follow the advice above so that we can help you out more specifically.
declare @table table (accountno nvarchar(10),trans char(2), amount int)
insert into @table values ('a1414141','Cr',12334),
('A64646','Dr',4500),
('E3636','Cr',900),
('R9494','Dr',8000)
SELECT
AccountNo,
CASE trans WHEN 'Cr' THEN 'Credit' WHEN 'Dr' THEN 'Debit' END AS Trans,
CASE WHEN trans = 'Cr' THEN amount ELSE 0 END AS CreditAmount,
CASE WHEN trans = 'Dr' THEN amount ELSE 0 END AS DebitAmount
FROM
@table
July 19, 2012 at 3:42 am
please send me as sql query
July 19, 2012 at 3:44 am
that is a sql query
July 19, 2012 at 4:04 am
after executing the output ll be
ACCOUNTNO TRANSC CREDIT DEBIT
==============================================
E007-IN100-2625 Credit13973.00000000NULL
L002-SL101-4221 DebitNULL13973.00000000
A-TWIP Debit NULL 2.00000000
E-EXPN Credit 2.00000000 NULL
A-TWIP DebitNULL2.00000000
E-EXPN Credit2.00000000NULL
A-TWIP DebitNULL2.00000000
E-EXPN Credit2.00000000NULL
A-TWIP DebitNULL2.00000000
E-EXPN Credit2.00000000NULL
A002-CA101-3258 Credit100798.00000000NULL
I NEED without showing null is it possible????
July 19, 2012 at 4:06 am
My script does not put NULL in the columns it puts a 0
declare @table table (accountno nvarchar(10),trans char(2), amount int)
insert into @table values ('a1414141','Cr',12334),
('A64646','Dr',4500),
('E3636','Cr',900),
('R9494','Dr',8000)
SELECT
AccountNo,
CASE trans WHEN 'Cr' THEN 'Credit' WHEN 'Dr' THEN 'Debit' END AS Trans,
CASE WHEN trans = 'Cr' THEN amount ELSE 0 END AS CreditAmount,
CASE WHEN trans = 'Dr' THEN amount ELSE 0 END AS DebitAmount
FROM
@table
AccountNoTransCreditAmountDebitAmount
a1414141Credit123340
A64646Debit04500
E3636Credit9000
R9494Debit08000
July 19, 2012 at 4:54 am
hi,
some of the accoun tno are repaetd how make groupby accountno
July 19, 2012 at 5:03 am
google "group by transact sql"
July 19, 2012 at 5:12 am
hi ,
i got the answer finally
select account_no,
sum(case when trans ='Cr' then amount else 0 end) "Credit",
sum(case when trans ='Dr' then amount else 0 end) "Debit"
from
@table
where fs_tran_date between '01-apr-2011' and '30-apr-2011'
group by account_no
July 19, 2012 at 5:20 am
raghuldrag (7/19/2012)
hi ,i got the answer finally
select account_no,
sum(case when trans ='Cr' then amount else 0 end) "Credit",
sum(case when trans ='Dr' then amount else 0 end) "Debit"
from
@table
where fs_tran_date between '01-apr-2011' and '30-apr-2011'
group by account_no
Great. Good that you could solve the GROUP BY issue yourself
This method of grouping the data is called the CROSS-TAB
You can check the below links for more details
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
http://www.sqlservercentral.com/articles/Crosstab/65048/
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 19, 2012 at 10:16 pm
hi Kingston,
I 'm currently working on server 2000,so that pivot won't allow in that version,is any other possibiliies there ah???
July 20, 2012 at 12:01 am
raghuldrag (7/19/2012)
hi Kingston,I 'm currently working on server 2000,so that pivot won't allow in that version,is any other possibiliies there ah???
In that case CROSS TABS is probably the only and the best option.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 20, 2012 at 12:50 am
Okay, forget you getting frustrated, I'm frustrated. Just how many different threads do you need for the same problem????
July 20, 2012 at 10:53 am
Lynn Pettis (7/20/2012)
Okay, forget you getting frustrated, I'm frustrated. Just how many different threads do you need for the same problem????
Probably looks like their indexes...fragmented all over the place. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply