How to sum my columns in Ms sql 2000

Posted by Raghuldrag under Sql Server on 12/16/2014 | Points: 10 | Views : 574 | Status : [Member] | Replies : 0
Hi Friends,


I ve the table structure below

example table of my original


create table fms
(
fs_locn char(100),
fs_account_no varchar(200),
fs_cost_center_no varchar(100),
fs_tran_type char(50),
fs_post_amt float,
fs_tran_date datetime
)

insert into fms (fs_locn,fs_account_no,fs_cost_center_no,fs_tran_type,fs_post_amt,fs_tran_date)
values ('CHN','E002-SW100-2100','200PROD','CR','1500,'2014-01-15'),
values ('CHN','E002-SW100-2100','200PROD','DR','4000,'2014-01-18'),
values ('DEL','E002-SW100-2110','201PROD','DR','1500,'2014-01-17'),
values ('DEL','E002-SW100-2110','201PROD','CR','2500,'2014-01-19'),
(here Cr is credit ,Dr is Debit concept of expense calculation formula is DR-CR)

the values on different account_no,locn & Cost_centers also now
i wanna to show the amount each cost_center wise and sum of the values so i wrote the Sql Query


select
coalesce(fs_account_no,'Total') as fs_accounts
,sum(case when left(fs_account_no,1) ='E' and fs_dr_cr_code='Dr' and fs_cost_center_no='200PROD' then fs_post_amt
when left(fs_account_no,1) ='E' and fs_dr_cr_code='Cr' and fs_cost_center_no='200PROD' then fs_post_amt * -1
else 0
end
)as '200PROD'
, sum(case when left(fs_account_no,1) ='E' and fs_dr_cr_code='Dr' and fs_cost_center_no='201PROD' then fs_post_amt
when left(fs_account_no,1) ='E' and fs_dr_cr_code='Cr' and fs_cost_center_no='201PROD' then fs_post_amt * -1
else 0
end
)as '201PROD'

from

FMS
where
fs_tran_date between '2012-01-01' and '2012-01-31'
and
fs_locn_code='CHN' and left(fs_account_no,1) not In ('A','R','L')and fs_account_no in ('E002-SW100-2100','E002-SW100-2106','E002-SW100-2103','E002-SW100-2105','E002-SW100-2104','E002-SW100-2114')

group by

fs_account_no with Rollup
order by
fs_account_no


expecting Output :

Account 200Prod 201PROD ProdcutionCost
E002-SW100-2100 2500 1000 3500


How to do that?


i tried like


select
k.fs_acoounts,
k.200Prod,
(
my query
)k

its showing error '200prod'


what is the problem?




Responses

(No response found.)

Login to post response