July 14, 2014 at 3:21 am
Hi Friends,
i ve the table like
create table account
(
fs_acc_no varchar(100),
fs_tran_type char(50),
fs_amount numeric(500)
fs_date datetime
)
insert into account values ('E01','CR','3000','2014-04-01')
values ('E01','DR','4000','2014-04-05')
values ('E02','DR','4000','2014-04-04')
values ('E04','DR','500','2014-04-03')
values ('E03','CR','200','2014-04-03')
values ('E03','DR','500','2014-04-05')
here i m taking sum of expenses amount for the month april
formula for taking sum is= "DR-CR"
so i wrote the query like
select fs_acc_no,
amount=sum(case when fs_acc_no like 'E%' and Fs_tran_type='CR' then fs_amount * -1
when fs_acc_no like 'E%' and Fs_tran_type='DR' then Fs_amount)
group by fs_acc_no
now i wanna output like
sales
account sales
E01 1000
E03 300
=====
total 1300
=======
Maintaience
E02 4000
E04 500
======
4500
=====
here account no are alws fixed to the below dept how make code for my expecting output?
July 14, 2014 at 3:55 am
what exactly the issue you are facing ? as per the query you will get the aggregated result. One question, when you say "Account Type" Maintenance/Sales ? How do u know which is which?
However, I have updated the query so that you can get a result set.
Declare @account table
(
fs_acc_no varchar(100),
fs_tran_type char(50),
fs_amount numeric,
fs_date datetime
)
insert into @account
values ('E01','CR','3000','2014-04-01')
, ('E01','DR','4000','2014-04-05')
, ('E02','DR','4000','2014-04-04')
, ('E04','DR','500','2014-04-03')
, ('E03','CR','200','2014-04-03')
, ('E03','DR','500','2014-04-05')
select fs_acc_no,sum(case when Fs_tran_type = 'CR' then fs_amount * -1 when Fs_tran_type='DR' then Fs_amount end)
from @account
group by fs_acc_no
hope it helps
July 14, 2014 at 4:07 am
Hi Twin Devil,
Here Some Of the accounts are manually defined in sales and maintenance after the Sum of account calculation i was struck to calculate sum of sales & maintainence
how to do that?
July 14, 2014 at 4:30 am
raghuldrag (7/14/2014)
Hi Twin Devil,Here Some Of the accounts are manually defined in sales and maintenance after the Sum of account calculation i was struck to calculate sum of sales & maintainence
how to do that?
Declare @account table
(
fs_acc_type varchar(100),
fs_acc_no varchar(100),
fs_tran_type char(50),
fs_amount numeric,
fs_date datetime
)
insert into @account
values
('Sales', 'E01','CR','3000','2014-04-01')
,('Sales', 'E01','DR','4000','2014-04-05')
,('Maintaience', 'E02','DR','4000','2014-04-04')
,('Maintaience', 'E04','DR','500','2014-04-03')
,('Sales', 'E03','CR','200','2014-04-03')
,('Sales', 'E03','DR','500','2014-04-05')
select *
from (
select fs_acc_type, fs_acc_no,sum(case when Fs_tran_type = 'CR' then fs_amount * -1 when Fs_tran_type='DR' then Fs_amount end) as Amt
from @account
group by fs_acc_type, fs_acc_no
union all
select fs_acc_type,'' AS fs_acc_no,sum(case when Fs_tran_type = 'CR' then fs_amount * -1 when Fs_tran_type='DR' then Fs_amount end)
from @account
group by fs_acc_type
) A
Order by fs_acc_type, fs_acc_no desc
hope it helps
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply