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