December 16, 2014 at 12:49 am
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?
December 16, 2014 at 3:39 am
There are several problems
Your insert statements have errors
fs_tran_date matching wrong date for data supplied
fs_locn_code does not exist
fs_locn = 'CHN' will ignore two values and will not give you the result you want
not In ('A','R','L') is not needed as you have fixed accounts listed
I changed the query based on the table def
SELECTCOALESCE(fs_account_no,'Total') AS [fs_accounts],
SUM(CASE WHEN fs_cost_center_no = '200PROD' AND fs_tran_type = 'Dr' THEN fs_post_amt
WHEN fs_cost_center_no='200PROD' AND fs_tran_type = 'Cr' THEN -fs_post_amt
ELSE 0
END) AS [200PROD],
SUM(CASE WHEN fs_cost_center_no = '201PROD' AND fs_tran_type = 'Dr' THEN fs_post_amt
WHEN fs_cost_center_no = '201PROD' AND fs_tran_type = 'Cr' THEN -fs_post_amt
ELSE 0
END) AS [201PROD],
SUM(CASE WHEN fs_cost_center_no IN ('200PROD','201PROD') AND fs_tran_type = 'Dr' THEN fs_post_amt
WHEN fs_cost_center_no IN ('200PROD','201PROD') AND fs_tran_type = 'Cr' THEN -fs_post_amt
ELSE 0
END) AS [ProdcutionCost]
FROMFMS
WHEREfs_tran_date BETWEEN '20140101' and '20140131'
ANDfs_locn = 'CHN'
ANDfs_account_no IN ('E002-SW100-2100','E002-SW100-2106','E002-SW100-2103','E002-SW100-2105','E002-SW100-2104','E002-SW100-2114')
GROUPBY fs_account_no WITH ROLLUP
ORDERBY GROUPING(fs_account_no) ASC,fs_account_no ASC
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply