[易飞]财务要求和系统一致不管是否有销售发票-优快云博客

那更简单了

/*
*用途:统计销售实际已开票抓INVLA档抓成本
*作者:龚德辉
*日期:2010-05--23
*/

CREATE Procedure UP_SALRealyCost
(
@year as char(4)='2010'
)
as
begin
declare @plan Table
(
type nvarchar(20)
,area nvarchar(20)
,sort int
,acct_month1 decimal(18,9)
,acct_month2 decimal(18,9)
,acct_month3 decimal(18,9)
,acct_month4 decimal(18,9)
,acct_month5 decimal(18,9)
,acct_month6 decimal(18,9)
,acct_month7 decimal(18,9)
,acct_month8 decimal(18,9)
,acct_month9 decimal(18,9)
,acct_month10 decimal(18,9)
,acct_month11 decimal(18,9)
,acct_month12 decimal(18,9)
)
declare @account as decimal(18,9),@date as nvarchar(8),@type as nvarchar(20),@area as nvarchar(20),@sort as int ,@newyear as nvarchar(6),@curryear as nvarchar(4)
set @newyear=@year+substring(convert(varchar(10),getdate(),120),6,2)--当前年月
set @curryear=datepart(year,getdate())

if @year>=@curryear
begin

declare Cursorpawf cursor
local static for
select case when left(m.MB005,1) in ('2','3') THEN '其他' else MA003 END MA003,MR003,Acount,[date], case when m.MB005 in('2','3') then 999 else m.MB005 END as sort from (
select a.CODE,a.Acount,a.[date],b.MR003 from (

select MA076 Dept,TH004 CODE, LA013 AS Acount,LA004 [date]
from
COPTH
LEFT JOIN COPTG ON TG001=TH001 AND TG002=TH002
LEFT JOIN COPMA ON TG004=MA001
inner JOIN INVLA on LA006=TH001 AND LA007=TH002 AND LA008=TH003
where left(LA004,6)<=@newyear AND left(LA004,4)=@year

union all

select MA076 Dept,TJ004 CODE,-LA013 AS Acount,LA004 [date]
from
COPTJ
LEFT JOIN COPTI ON TI001=TJ001 AND TI002=TJ002
LEFT JOIN COPMA ON TI004=MA001
inner JOIN INVLA on LA006=TJ001 AND LA007=TJ002 AND LA008=TJ003
where left(LA004,6)<=@newyear AND left(LA004,4)=@year

) a left join (select * from CMSMR where MR001='2') b on a.Dept=b.MR002 ) c
left join (select MA001,MB001,MB005,MA003 from INVMB LEFT JOIN INVMA ON MB005=MA002 WHERE MA001='1') as m on c.CODE=m.MB001
WHERE Acount<>0

end
if substring(@date,5,2)='10'
begin
insert @plan(type,area,acct_month10,sort)values(@type,@area,@account,@sort)
end
if substring(@date,5,2)='11'
begin
insert @plan(type,area,acct_month11,sort)values(@type,@area,@account,@sort)
end
if substring(@date,5,2)='12'
begin
insert @plan(type,area,acct_month12,sort)values(@type,@area,@account,@sort)
end

end
end
close Cursorpawf
deallocate Cursorpawf

end
else
begin

declare Cursorpawf cursor
local static for

select case when left(m.MB005,1) in ('2','3') THEN '其他' else MA003 END MA003,MR003,Acount,[date], case when m.MB005 in('2','3') then 999 else m.MB005 END as sort from (
select a.CODE,a.Acount,a.[date],b.MR003 from (

select MA076 Dept,TH004 CODE, LA013 AS Acount,LA004 [date]
from
COPTH
LEFT JOIN COPTG ON TG001=TH001 AND TG002=TH002
LEFT JOIN COPMA ON TG004=MA001
inner JOIN INVLA on LA006=TH001 AND LA007=TH002 AND LA008=TH003
where left(LA004,4)=@year

union all

Open Cursorpawf
while 1=1
begin
fetch Cursorpawf into
@type
,@area
,@account
,@date
,@sort
if @@fetch_status<>0
break
if @type is not null and @area is not null and @account<>0
begin

if substring(@date,5,2)='01'
begin
insert @plan(type,area,acct_month1,sort)values(@type,@area,@account,@sort)
end
if substring(@date,5,2)='02'
begin
insert @plan(type,area,acct_month2,sort)values(@type,@area,@account,@sort)
end
if substring(@date,5,2)='03'
begin
insert @plan(type,area,acct_month3,sort)values(@type,@area,@account,@sort)
end
if substring(@date,5,2)='04'
begin
insert @plan(type,area,acct_month4,sort)values(@type,@area,@account,@sort)
end
if substring(@date,5,2)='05'
begin
insert @plan(type,area,acct_month5,sort)values(@type,@area,@account,@sort)
end
if substring(@date,5,2)='06'
begin
insert @plan(type,area,acct_month6,sort)values(@type,@area,@account,@sort)
end
if substring(@date,5,2)='07'
begin
insert @plan(type,area,acct_month7,sort)values(@type,@area,@account,@sort)
end
if substring(@date,5,2)='08'
begin
insert @plan(type,area,acct_month8,sort)values(@type,@area,@account,@sort)
end
if substring(@date,5,2)='09'
begin
insert @plan(type,area,acct_month9,sort)values(@type,@area,@account,@sort)
end
if substring(@date,5,2)='10'
begin
insert @plan(type,area,acct_month10,sort)values(@type,@area,@account,@sort)
end
if substring(@date,5,2)='11'
begin
insert @plan(type,area,acct_month11,sort)values(@type,@area,@account,@sort)
end
if substring(@date,5,2)='12'
begin
insert @plan(type,area,acct_month12,sort)values(@type,@area,@account,@sort)
end

end
end
close Cursorpawf
deallocate Cursorpawf
end

select sort,
rtrim(type) as type,
area
,convert(decimal(19,2),sum(isnull(acct_month1,0))) as 一月
,convert(decimal(19,2),sum(isnull(acct_month2,0))) as 二月
,convert(decimal(19,2),sum(isnull(acct_month3,0))) as 三月
,convert(decimal(19,2),sum(isnull(acct_month4,0))) as 四月
,convert(decimal(19,2),sum(isnull(acct_month5,0))) as 五月
,convert(decimal(19,2),sum(isnull(acct_month6,0))) as 六月
,convert(decimal(19,2),sum(isnull(acct_month7,0))) as 七月
,convert(decimal(19,2),sum(isnull(acct_month8,0))) as 八月
,convert(decimal(19,2),sum(isnull(acct_month9,0))) as 九月
,convert(decimal(19,2),sum(isnull(acct_month10,0))) as 十月
,convert(decimal(19,2),sum(isnull(acct_month11,0))) as 十一月
,convert(decimal(19,2),sum(isnull(acct_month12,0))) as 十二月
,convert(decimal(19,2),sum(isnull(acct_month1,0))+sum(isnull(acct_month2,0))+sum(isnull(acct_month3,0))+sum(isnull(acct_month4,0))+sum(isnull(acct_month5,0))+sum(isnull(acct_month6,0))+sum(isnull(acct_month7,0))+sum(isnull(acct_month8,0))+sum(isnull(acct_month9,0))+sum(isnull(acct_month10,0))+sum(isnull(acct_month11,0))+sum(isnull(acct_month12,0))) 合计
from @plan
group by sort,type,area
union all
select sort,
rtrim(type)+ '合计' as type,
rtrim(type)+'合计' area
,convert(decimal(19,2),sum(isnull(acct_month1,0))) as 一月
,convert(decimal(19,2),sum(isnull(acct_month2,0))) as 二月
,convert(decimal(19,2),sum(isnull(acct_month3,0))) as 三月
,convert(decimal(19,2),sum(isnull(acct_month4,0))) as 四月
,convert(decimal(19,2),sum(isnull(acct_month5,0))) as 五月
,convert(decimal(19,2),sum(isnull(acct_month6,0))) as 六月
,convert(decimal(19,2),sum(isnull(acct_month7,0))) as 七月
,convert(decimal(19,2),sum(isnull(acct_month8,0))) as 八月
,convert(decimal(19,2),sum(isnull(acct_month9,0))) as 九月
,convert(decimal(19,2),sum(isnull(acct_month10,0))) as 十月
,convert(decimal(19,2),sum(isnull(acct_month11,0))) as 十一月
,convert(decimal(19,2),sum(isnull(acct_month12,0))) as 十二月
,convert(decimal(19,2),sum(isnull(acct_month1,0))+sum(isnull(acct_month2,0))+sum(isnull(acct_month3,0))+sum(isnull(acct_month4,0))+sum(isnull(acct_month5,0))+sum(isnull(acct_month6,0))+sum(isnull(acct_month7,0))+sum(isnull(acct_month8,0))+sum(isnull(acct_month9,0))+sum(isnull(acct_month10,0))+sum(isnull(acct_month11,0))+sum(isnull(acct_month12,0))) 合计
from @plan
group by sort, type
order by sort ,type asc
------------------------------合计----------------------
select --sort,
rtrim(type)+ '合计' as type
--rtrim(type)+'合计' area
,convert(decimal(19,2),sum(isnull(acct_month1,0))) as 一月
,convert(decimal(19,2),sum(isnull(acct_month2,0))) as 二月
,convert(decimal(19,2),sum(isnull(acct_month3,0))) as 三月
,convert(decimal(19,2),sum(isnull(acct_month4,0))) as 四月
,convert(decimal(19,2),sum(isnull(acct_month5,0))) as 五月
,convert(decimal(19,2),sum(isnull(acct_month6,0))) as 六月
,convert(decimal(19,2),sum(isnull(acct_month7,0))) as 七月
,convert(decimal(19,2),sum(isnull(acct_month8,0))) as 八月
,convert(decimal(19,2),sum(isnull(acct_month9,0))) as 九月
,convert(decimal(19,2),sum(isnull(acct_month10,0))) as 十月
,convert(decimal(19,2),sum(isnull(acct_month11,0))) as 十一月
,convert(decimal(19,2),sum(isnull(acct_month12,0))) as 十二月

from @plan
group by type