统计销售成本-优快云博客

抓已开票的销货单和退货单关联INVLA抓取相关的数据

/* *用途:统计销售实际已开票抓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,case when TA079='1' then LA013 ELSE -(LA013) END AS Acount,TA038 [date] from ACRTA left join ACRTB ON TA001=TB001 AND TA002=TB002 left join COPTH ON TB005=TH001 AND TB006=TH002 AND TB007=TH003 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(TA038,6)<=@newyear AND left(TA038,4)=@year and TB019>0 union all select MA076 Dept,TJ004 CODE,case when TA079='1' then LA013 ELSE -(LA013) END AS Acount,TA038 [date] from ACRTA left join ACRTB ON TA001=TB001 AND TA002=TB002 left join COPTJ ON TB005=TJ001 AND TB006=TJ002 AND TB007=TJ003 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(TA038,6)<=@newyear AND left(TA038,4)=@year and TA079='1' and TB019<0 --退货 ) 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 ----添加发票不是销售/销退来源龚德辉2010-10-11 增加 union all select '其他' MA003, MR003,case when TA079='1' then TB019 ELSE -(TB019) END AS Acount,TA038 [date],'999' sort from ACRTA left join ACRTB ON TA001=TB001 AND TA002=TB002 LEFT JOIN COPMA ON TA004=MA001 left join (select * from CMSMR where MR001='2') b on MA076=b.MR002 where left(TA038,6)<=@newyear AND left(TA038,4)=@year and TB004 not in('1','2') 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 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,case when TA079='1' then LA013 ELSE -(LA013) END AS Acount,TA038 [date] from ACRTA left join ACRTB ON TA001=TB001 AND TA002=TB002 left join COPTH ON TB005=TH001 AND TB006=TH002 AND TB007=TH003 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(TA038,4)=@year and TB019>0 union all select MA076 Dept,TJ004 CODE,case when TA079='1' then LA013 ELSE -(LA013) END AS Acount,TA038 [date] from ACRTA left join ACRTB ON TA001=TB001 AND TA002=TB002 left join COPTJ ON TB005=TJ001 AND TB006=TJ002 AND TB007=TJ003 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(TA038,4)=@year and TA079='1' and TB019<0 --退货 ) 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 ----添加发票不是销售/销退来源龚德辉2010-10-11 增加 union all select '其他' MA003, MR003,case when TA079='1' then TB019 ELSE -(TB019) END AS Acount,TA038 [date],'999' sort from ACRTA left join ACRTB ON TA001=TB001 AND TA002=TB002 LEFT JOIN COPMA ON TA004=MA001 left join (select * from CMSMR where MR001='2') b on MA076=b.MR002 where left(TA038,4)=@year and TB004 not in('1','2') 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 end GO