[易飞]主营业务分析图

来源：互联网发布：华为手机降级软件编辑：程序博客网时间：2024/06/10 05:55

上图

SQL:

/****** Object:  StoredProcedure [dbo].[UP_MainBusinessChart]    Script Date: 04/19/2012 10:45:35 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO--============================================================--用途:主营业务分析图表--作者:龚德辉--日期:2012-04-10--============================================================ALTER PROCEDURE [dbo].[UP_MainBusinessChart]ASBEGIN--今年与去年年份declare@year as nchar(4),@lastyear as nchar(4),@cyear as nchar(4),@cmonth as nchar(3),@csaletype as nvarchar(10),@cmount as decimal(10,2),@ccost as decimal(10,2)set @year=datepart(year,getdate()) set @lastyear=datepart(year,dateadd(year,-1,getdate()))  select A.cyear,A.cmonth,A.csaletype,sum(camount) camount,sum(ccost) ccost into #temp01 from( SELECT   datepart(year,TA003) cyear,datepart(month,TA003) as cmonth,CASE WHEN TA001 in ('6101','6102','6103','6104','6105','6106')  then '内销'      --WHEN TA001 in ('6201','6202','6203','6204','6205','6206')  then '外销'else '外销' END AS csaletype,TB019 camount, TA001,convert(decimal(15,2),TB022*LA012) ccost,TB022 qtyFROM  ACRTB LEFT JOIN ACRTA ON TA001=TB001 AND TA002=TB002            LEFT JOIN COPMA ON TA004=MA001            LEFT JOIN INVMB ON TB039=MB001        INNER JOIN INVLA ON LA006=TB005 AND LA007=TB006 AND LA008=TB007            WHERE left(TA003,4) in (@year,@lastyear)  AND TB004 in ('1','2')  and TA025='Y') Agroup by  A.cyear,A.cmonth,A.csaletypeorder by  A.cyear,A.cmonth,A.csaletype ascselect a.* into #temp02  from(/*sales  revenue*/select 'Sales Revenue'+convert(nvarchar(4),cyear) as item,SUM(CASE WHEN cmonth = '1' THEN camount ELSE 0 END) AS Jan,SUM(CASE WHEN cmonth = '2' THEN camount ELSE 0 END) AS Feb,SUM(CASE WHEN cmonth = '3' THEN camount ELSE 0 END) AS Mar,SUM(CASE WHEN cmonth = '4' THEN camount ELSE 0 END) AS Apr,SUM(CASE WHEN cmonth = '5' THEN camount ELSE 0 END) AS May,SUM(CASE WHEN cmonth = '6' THEN camount ELSE 0 END) AS Jun,SUM(CASE WHEN cmonth = '7' THEN camount ELSE 0 END) AS Jul,SUM(CASE WHEN cmonth = '8' THEN camount ELSE 0 END) AS Aug,SUM(CASE WHEN cmonth = '9' THEN camount ELSE 0 END) AS Sep,SUM(CASE WHEN cmonth = '10' THEN camount ELSE 0 END) AS Oct,SUM(CASE WHEN cmonth = '11' THEN camount ELSE 0 END) AS Nov,SUM(CASE WHEN cmonth = '12' THEN camount ELSE 0 END) AS [Dec]from #temp01where cyear=@yeargroup by cyearunion all/*cost of sales*/select 'Cost of Sales'+convert(nvarchar(4),cyear) as item,SUM(CASE WHEN cmonth = '1' THEN ccost ELSE 0 END) AS Jan,SUM(CASE WHEN cmonth = '2' THEN ccost ELSE 0 END) AS Feb,SUM(CASE WHEN cmonth = '3' THEN ccost ELSE 0 END) AS Mar,SUM(CASE WHEN cmonth = '4' THEN ccost ELSE 0 END) AS Apr,SUM(CASE WHEN cmonth = '5' THEN ccost ELSE 0 END) AS May,SUM(CASE WHEN cmonth = '6' THEN ccost ELSE 0 END) AS Jun,SUM(CASE WHEN cmonth = '7' THEN ccost ELSE 0 END) AS Jul,SUM(CASE WHEN cmonth = '8' THEN ccost ELSE 0 END) AS Aug,SUM(CASE WHEN cmonth = '9' THEN ccost ELSE 0 END) AS Sep,SUM(CASE WHEN cmonth = '10' THEN ccost ELSE 0 END) AS Oct,SUM(CASE WHEN cmonth = '11' THEN ccost ELSE 0 END) AS Nov,SUM(CASE WHEN cmonth = '12' THEN ccost ELSE 0 END) AS [Dec]from #temp01where cyear=@yeargroup by cyearunion all/*毛利润*/select 'Gross Profit'+convert(nvarchar(4),cyear) as item,SUM(CASE WHEN cmonth = '1' THEN camount-ccost ELSE 0 END) AS Jan,SUM(CASE WHEN cmonth = '2' THEN camount-ccost ELSE 0 END) AS Feb,SUM(CASE WHEN cmonth = '3' THEN camount-ccost ELSE 0 END) AS Mar,SUM(CASE WHEN cmonth = '4' THEN camount-ccost ELSE 0 END) AS Apr,SUM(CASE WHEN cmonth = '5' THEN camount-ccost ELSE 0 END) AS May,SUM(CASE WHEN cmonth = '6' THEN camount-ccost ELSE 0 END) AS Jun,SUM(CASE WHEN cmonth = '7' THEN camount-ccost ELSE 0 END) AS Jul,SUM(CASE WHEN cmonth = '8' THEN camount-ccost ELSE 0 END) AS Aug,SUM(CASE WHEN cmonth = '9' THEN camount-ccost ELSE 0 END) AS Sep,SUM(CASE WHEN cmonth = '10' THEN camount-ccost ELSE 0 END) AS Oct,SUM(CASE WHEN cmonth = '11' THEN camount-ccost ELSE 0 END) AS Nov,SUM(CASE WHEN cmonth = '12' THEN camount-ccost ELSE 0 END) AS [Dec]from #temp01where cyear=@yeargroup by cyearunion all/*今年的本年利润：会计科目各期汇总档ACTLE 4103本年利润科目 */SELECT 'Profit'+convert(nvarchar(4),LE002) as item,SUM(CASE WHEN LE003 ='01' THEN LE017 ELSE 0 END) AS Jan,SUM(CASE WHEN LE003 = '02' THEN LE017 ELSE 0 END) AS Feb,SUM(CASE WHEN LE003 = '03' THEN LE017 ELSE 0 END) AS Mar,SUM(CASE WHEN LE003 = '04' THEN LE017 ELSE 0 END) AS Apr,SUM(CASE WHEN LE003 = '05' THEN LE017 ELSE 0 END) AS May,SUM(CASE WHEN LE003 = '06' THEN LE017 ELSE 0 END) AS Jun,SUM(CASE WHEN LE003 = '07' THEN LE017 ELSE 0 END) AS Jul,SUM(CASE WHEN LE003 = '08' THEN LE017 ELSE 0 END) AS Aug,SUM(CASE WHEN LE003 = '09' THEN LE017 ELSE 0 END) AS Sep,SUM(CASE WHEN LE003 = '10' THEN LE017 ELSE 0 END) AS Oct,SUM(CASE WHEN LE003 = '11' THEN LE017 ELSE 0 END) AS Nov,SUM(CASE WHEN LE003 = '12' THEN LE017 ELSE 0 END) AS [Dec]from ACTLEwhere LE002=@year AND LE001='4103'group by LE002union all/* 毛利润率*/select 'Profit Margin'+convert(nvarchar(4),cyear) as item,case when SUM(CASE WHEN cmonth = '1' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '1' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '1' THEN camount ELSE 0 END)*100 else 0 END AS Jan, case when SUM(CASE WHEN cmonth = '2' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '2' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '2' THEN camount ELSE 0 END)*100  ELSE 0 END AS Feb, case when SUM(CASE WHEN cmonth = '3' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '3' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '3' THEN camount ELSE 0 END)*100  ELSE 0 END AS Mar,case when SUM(CASE WHEN cmonth = '4' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '4' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '4' THEN camount ELSE 0 END)*100  ELSE 0 END AS Apr,case when SUM(CASE WHEN cmonth = '5' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '5' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '5' THEN camount ELSE 0 END)*100  ELSE 0 END AS May,case when SUM(CASE WHEN cmonth = '16' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '6' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '6' THEN camount ELSE 0 END)*100  ELSE 0 END AS Jun,case when SUM(CASE WHEN cmonth = '7' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '7' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '7' THEN camount ELSE 0 END)*100  ELSE 0 END AS Jul,case when SUM(CASE WHEN cmonth = '8' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '8' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '8' THEN camount ELSE 0 END)*100  ELSE 0 END AS Aug,case when SUM(CASE WHEN cmonth = '9' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '9' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '9' THEN camount ELSE 0 END)*100  ELSE 0 END AS Sep,case when SUM(CASE WHEN cmonth = '10' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '10' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '10' THEN camount ELSE 0 END)*100  ELSE 0 END AS Oct,case when SUM(CASE WHEN cmonth = '11' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '11' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '11' THEN camount ELSE 0 END)*100  ELSE 0 END AS Nov,case when SUM(CASE WHEN cmonth = '12' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '12' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '12' THEN camount ELSE 0 END)*100  ELSE 0 END AS [Dec]from #temp01where cyear=@yeargroup by cyearunion all/*去年数据*/select 'Sales Revenue'+convert(nvarchar(4),cyear) as item,SUM(CASE WHEN cmonth = '1' THEN camount ELSE 0 END) AS Jan,SUM(CASE WHEN cmonth = '2' THEN camount ELSE 0 END) AS Feb,SUM(CASE WHEN cmonth = '3' THEN camount ELSE 0 END) AS Mar,SUM(CASE WHEN cmonth = '4' THEN camount ELSE 0 END) AS Apr,SUM(CASE WHEN cmonth = '5' THEN camount ELSE 0 END) AS May,SUM(CASE WHEN cmonth = '6' THEN camount ELSE 0 END) AS Jun,SUM(CASE WHEN cmonth = '7' THEN camount ELSE 0 END) AS Jul,SUM(CASE WHEN cmonth = '8' THEN camount ELSE 0 END) AS Aug,SUM(CASE WHEN cmonth = '9' THEN camount ELSE 0 END) AS Sep,SUM(CASE WHEN cmonth = '10' THEN camount ELSE 0 END) AS Oct,SUM(CASE WHEN cmonth = '11' THEN camount ELSE 0 END) AS Nov,SUM(CASE WHEN cmonth = '12' THEN camount ELSE 0 END) AS [Dec]from #temp01where cyear=@lastyeargroup by cyearunion all/*cost of sales*/select 'Cost of Sales'+convert(nvarchar(4),cyear) as item,SUM(CASE WHEN cmonth = '1' THEN ccost ELSE 0 END) AS Jan,SUM(CASE WHEN cmonth = '2' THEN ccost ELSE 0 END) AS Feb,SUM(CASE WHEN cmonth = '3' THEN ccost ELSE 0 END) AS Mar,SUM(CASE WHEN cmonth = '4' THEN ccost ELSE 0 END) AS Apr,SUM(CASE WHEN cmonth = '5' THEN ccost ELSE 0 END) AS May,SUM(CASE WHEN cmonth = '6' THEN ccost ELSE 0 END) AS Jun,SUM(CASE WHEN cmonth = '7' THEN ccost ELSE 0 END) AS Jul,SUM(CASE WHEN cmonth = '8' THEN ccost ELSE 0 END) AS Aug,SUM(CASE WHEN cmonth = '9' THEN ccost ELSE 0 END) AS Sep,SUM(CASE WHEN cmonth = '10' THEN ccost ELSE 0 END) AS Oct,SUM(CASE WHEN cmonth = '11' THEN ccost ELSE 0 END) AS Nov,SUM(CASE WHEN cmonth = '12' THEN ccost ELSE 0 END) AS [Dec]from #temp01where cyear=@lastyeargroup by cyearunion all/*毛利润*/select 'Gross Profit'+convert(nvarchar(4),cyear) as item,SUM(CASE WHEN cmonth = '1' THEN camount-ccost ELSE 0 END) AS Jan,SUM(CASE WHEN cmonth = '2' THEN camount-ccost ELSE 0 END) AS Feb,SUM(CASE WHEN cmonth = '3' THEN camount-ccost ELSE 0 END) AS Mar,SUM(CASE WHEN cmonth = '4' THEN camount-ccost ELSE 0 END) AS Apr,SUM(CASE WHEN cmonth = '5' THEN camount-ccost ELSE 0 END) AS May,SUM(CASE WHEN cmonth = '6' THEN camount-ccost ELSE 0 END) AS Jun,SUM(CASE WHEN cmonth = '7' THEN camount-ccost ELSE 0 END) AS Jul,SUM(CASE WHEN cmonth = '8' THEN camount-ccost ELSE 0 END) AS Aug,SUM(CASE WHEN cmonth = '9' THEN camount-ccost ELSE 0 END) AS Sep,SUM(CASE WHEN cmonth = '10' THEN camount-ccost ELSE 0 END) AS Oct,SUM(CASE WHEN cmonth = '11' THEN camount-ccost ELSE 0 END) AS Nov,SUM(CASE WHEN cmonth = '12' THEN camount-ccost ELSE 0 END) AS [Dec]from #temp01where cyear=@lastyeargroup by cyearunion all/*本年利润：会计科目各期汇总档ACTLE 4103本年利润科目 */SELECT 'Profit'+convert(nvarchar(4),LE002) as item,SUM(CASE WHEN LE003 ='01' THEN LE017 ELSE 0 END) AS Jan,SUM(CASE WHEN LE003 = '02' THEN LE017 ELSE 0 END) AS Feb,SUM(CASE WHEN LE003 = '03' THEN LE017 ELSE 0 END) AS Mar,SUM(CASE WHEN LE003 = '04' THEN LE017 ELSE 0 END) AS Apr,SUM(CASE WHEN LE003 = '05' THEN LE017 ELSE 0 END) AS May,SUM(CASE WHEN LE003 = '06' THEN LE017 ELSE 0 END) AS Jun,SUM(CASE WHEN LE003 = '07' THEN LE017 ELSE 0 END) AS Jul,SUM(CASE WHEN LE003 = '08' THEN LE017 ELSE 0 END) AS Aug,SUM(CASE WHEN LE003 = '09' THEN LE017 ELSE 0 END) AS Sep,SUM(CASE WHEN LE003 = '10' THEN LE017 ELSE 0 END) AS Oct,SUM(CASE WHEN LE003 = '11' THEN LE017 ELSE 0 END) AS Nov,SUM(CASE WHEN LE003 = '12' THEN LE017 ELSE 0 END) AS [Dec]from ACTLEwhere LE002=@lastyear AND LE001='4103'group by LE002union all/* 毛利润率*/select 'Profit Margin'+convert(nvarchar(4),cyear) as item,case when SUM(CASE WHEN cmonth = '1' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '1' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '1' THEN camount ELSE 0 END)*100 else 0 END AS Jan, case when SUM(CASE WHEN cmonth = '2' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '2' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '2' THEN camount ELSE 0 END)*100  ELSE 0 END AS Feb, case when SUM(CASE WHEN cmonth = '3' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '3' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '3' THEN camount ELSE 0 END)*100  ELSE 0 END AS Mar,case when SUM(CASE WHEN cmonth = '4' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '4' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '4' THEN camount ELSE 0 END)*100  ELSE 0 END AS Apr,case when SUM(CASE WHEN cmonth = '5' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '5' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '5' THEN camount ELSE 0 END)*100  ELSE 0 END AS May,case when SUM(CASE WHEN cmonth = '6' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '6' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '6' THEN camount ELSE 0 END)*100  ELSE 0 END AS Jun,case when SUM(CASE WHEN cmonth = '7' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '7' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '7' THEN camount ELSE 0 END)*100  ELSE 0 END AS Jul,case when SUM(CASE WHEN cmonth = '8' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '8' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '8' THEN camount ELSE 0 END)*100  ELSE 0 END AS Aug,case when SUM(CASE WHEN cmonth = '9' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '9' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '9' THEN camount ELSE 0 END)*100  ELSE 0 END AS Sep,case when SUM(CASE WHEN cmonth = '10' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '10' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '10' THEN camount ELSE 0 END)*100  ELSE 0 END AS Oct,case when SUM(CASE WHEN cmonth = '11' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '11' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '11' THEN camount ELSE 0 END)*100  ELSE 0 END AS Nov,case when SUM(CASE WHEN cmonth = '12' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '12' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '12' THEN camount ELSE 0 END)*100  ELSE 0 END AS [Dec]from #temp01where cyear=@lastyeargroup by cyear) a/*年度对比分析*/select * from #temp02/*收入柱状图*/select * from #temp02where item like '%Sales Revenue%' or item like '%Profit Margin%'select * from #temp02where item in('Sales Revenue'+convert(nvarchar(4),@year),'Sales Revenue'+convert(nvarchar(4),@lastyear)) or      item in('Cost of Sales'+convert(nvarchar(4),@year),'Cost of Sales'+convert(nvarchar(4),@lastyear)) or      item in('Gross Profit'+convert(nvarchar(4),@year),'Gross Profit'+convert(nvarchar(4),@lastyear)) or      item in('Profit'+convert(nvarchar(4),@year),'Profit'+convert(nvarchar(4),@lastyear)) order by item asc       ---------------------------去年内外销结构----------------------------select * into #temp03 from(select 'sales revenue' as item,SUM(camount)  AS 'lastyear'from #temp01where cyear=@lastyear union allselect 'sales Domestic' as item,SUM(camount)  AS 'lastyear'from #temp01where cyear=@lastyear and csaletype='内销'union allselect 'cost Domestic' as item,SUM(ccost)  AS 'lastyear'from #temp01where cyear=@lastyear and csaletype='内销'union allselect 'profit Domestic' as item,SUM(camount-ccost)  AS 'lastyear'from #temp01where cyear=@lastyear and csaletype='内销'union allselect 'sales Export' as item,SUM(camount)  AS 'lastyear'from #temp01where cyear=@lastyear and csaletype='外销'union allselect 'cost Export' as item,SUM(ccost)  AS 'lastyear'from #temp01where cyear=@lastyear and csaletype='外销'union allselect 'profit Export' as item,SUM(camount-ccost)  AS 'lastyear'from #temp01where cyear=@lastyear and csaletype='外销'union allselect 'profit margin Domestic' as item,SUM(camount-ccost)/SUM(camount)*100 AS 'lastyear'  from #temp01where cyear=@lastyear and csaletype='内销'union allselect 'profit margin Export' as item,SUM(camount-ccost)/SUM(camount)*100 AS 'lastyear'  from #temp01where cyear=@lastyear and csaletype='外销')c----------------------今年分月内外销结构------------------------------select * into #temp04 from(/*内销收入*/select 'sales revenue' as item,SUM(CASE WHEN cmonth = '1' THEN camount ELSE 0 END) AS Jan,SUM(CASE WHEN cmonth = '2' THEN camount ELSE 0 END) AS Feb,SUM(CASE WHEN cmonth = '3' THEN camount ELSE 0 END) AS Mar,SUM(CASE WHEN cmonth = '4' THEN camount ELSE 0 END) AS Apr,SUM(CASE WHEN cmonth = '5' THEN camount ELSE 0 END) AS May,SUM(CASE WHEN cmonth = '6' THEN camount ELSE 0 END) AS Jun,SUM(CASE WHEN cmonth = '7' THEN camount ELSE 0 END) AS Jul,SUM(CASE WHEN cmonth = '8' THEN camount ELSE 0 END) AS Aug,SUM(CASE WHEN cmonth = '9' THEN camount ELSE 0 END) AS Sep,SUM(CASE WHEN cmonth = '10' THEN camount ELSE 0 END) AS Oct,SUM(CASE WHEN cmonth = '11' THEN camount ELSE 0 END) AS Nov,SUM(CASE WHEN cmonth = '12' THEN camount ELSE 0 END) AS [Dec],case when SUM(camount)<>0 then SUM(camount)  ELSE 0 END AS [total]from #temp01where cyear=@year union all/*内销收入*/select 'sales Domestic' as item,SUM(CASE WHEN cmonth = '1' THEN camount ELSE 0 END) AS Jan,SUM(CASE WHEN cmonth = '2' THEN camount ELSE 0 END) AS Feb,SUM(CASE WHEN cmonth = '3' THEN camount ELSE 0 END) AS Mar,SUM(CASE WHEN cmonth = '4' THEN camount ELSE 0 END) AS Apr,SUM(CASE WHEN cmonth = '5' THEN camount ELSE 0 END) AS May,SUM(CASE WHEN cmonth = '6' THEN camount ELSE 0 END) AS Jun,SUM(CASE WHEN cmonth = '7' THEN camount ELSE 0 END) AS Jul,SUM(CASE WHEN cmonth = '8' THEN camount ELSE 0 END) AS Aug,SUM(CASE WHEN cmonth = '9' THEN camount ELSE 0 END) AS Sep,SUM(CASE WHEN cmonth = '10' THEN camount ELSE 0 END) AS Oct,SUM(CASE WHEN cmonth = '11' THEN camount ELSE 0 END) AS Nov,SUM(CASE WHEN cmonth = '12' THEN camount ELSE 0 END) AS [Dec],case when SUM(camount)<>0 then SUM(camount)  ELSE 0 END AS [total]from #temp01where cyear=@year and csaletype='内销'union all/*内销成本*/select  'cost Domestic' as item,SUM(CASE WHEN cmonth = '1' THEN ccost ELSE 0 END) AS Jan,SUM(CASE WHEN cmonth = '2' THEN ccost ELSE 0 END) AS Feb,SUM(CASE WHEN cmonth = '3' THEN ccost ELSE 0 END) AS Mar,SUM(CASE WHEN cmonth = '4' THEN ccost ELSE 0 END) AS Apr,SUM(CASE WHEN cmonth = '5' THEN ccost ELSE 0 END) AS May,SUM(CASE WHEN cmonth = '6' THEN ccost ELSE 0 END) AS Jun,SUM(CASE WHEN cmonth = '7' THEN ccost ELSE 0 END) AS Jul,SUM(CASE WHEN cmonth = '8' THEN ccost ELSE 0 END) AS Aug,SUM(CASE WHEN cmonth = '9' THEN ccost ELSE 0 END) AS Sep,SUM(CASE WHEN cmonth = '10' THEN ccost ELSE 0 END) AS Oct,SUM(CASE WHEN cmonth = '11' THEN ccost ELSE 0 END) AS Nov,SUM(CASE WHEN cmonth = '12' THEN ccost ELSE 0 END) AS [Dec],case when SUM(ccost)<>0 then SUM(ccost)  ELSE 0 END AS [total]from #temp01where cyear=@year and csaletype='内销'union all/*内销毛利润*/select 'profit Domestic' as item,SUM(CASE WHEN cmonth = '1' THEN camount-ccost ELSE 0 END) AS Jan,SUM(CASE WHEN cmonth = '2' THEN camount-ccost ELSE 0 END) AS Feb,SUM(CASE WHEN cmonth = '3' THEN camount-ccost ELSE 0 END) AS Mar,SUM(CASE WHEN cmonth = '4' THEN camount-ccost ELSE 0 END) AS Apr,SUM(CASE WHEN cmonth = '5' THEN camount-ccost ELSE 0 END) AS May,SUM(CASE WHEN cmonth = '6' THEN camount-ccost ELSE 0 END) AS Jun,SUM(CASE WHEN cmonth = '7' THEN camount-ccost ELSE 0 END) AS Jul,SUM(CASE WHEN cmonth = '8' THEN camount-ccost ELSE 0 END) AS Aug,SUM(CASE WHEN cmonth = '9' THEN camount-ccost ELSE 0 END) AS Sep,SUM(CASE WHEN cmonth = '10' THEN camount-ccost ELSE 0 END) AS Oct,SUM(CASE WHEN cmonth = '11' THEN camount-ccost ELSE 0 END) AS Nov,SUM(CASE WHEN cmonth = '12' THEN camount-ccost ELSE 0 END) AS [Dec],case when SUM(camount-ccost)<>0 then SUM(camount-ccost)  ELSE 0 END AS [total]from #temp01 where cyear=@year and  csaletype='内销'union all/*外销收入*/select 'sales Export' as item,SUM(CASE WHEN cmonth = '1' THEN camount ELSE 0 END) AS Jan,SUM(CASE WHEN cmonth = '2' THEN camount ELSE 0 END) AS Feb,SUM(CASE WHEN cmonth = '3' THEN camount ELSE 0 END) AS Mar,SUM(CASE WHEN cmonth = '4' THEN camount ELSE 0 END) AS Apr,SUM(CASE WHEN cmonth = '5' THEN camount ELSE 0 END) AS May,SUM(CASE WHEN cmonth = '6' THEN camount ELSE 0 END) AS Jun,SUM(CASE WHEN cmonth = '7' THEN camount ELSE 0 END) AS Jul,SUM(CASE WHEN cmonth = '8' THEN camount ELSE 0 END) AS Aug,SUM(CASE WHEN cmonth = '9' THEN camount ELSE 0 END) AS Sep,SUM(CASE WHEN cmonth = '10' THEN camount ELSE 0 END) AS Oct,SUM(CASE WHEN cmonth = '11' THEN camount ELSE 0 END) AS Nov,SUM(CASE WHEN cmonth = '12' THEN camount ELSE 0 END) AS [Dec],case when SUM(camount)<>0 then SUM(camount) ELSE 0 END AS [total]from #temp01where cyear=@year and csaletype='外销'union all/*外销成本*/select  'cost Export' as item,SUM(CASE WHEN cmonth = '1' THEN ccost ELSE 0 END) AS Jan,SUM(CASE WHEN cmonth = '2' THEN ccost ELSE 0 END) AS Feb,SUM(CASE WHEN cmonth = '3' THEN ccost ELSE 0 END) AS Mar,SUM(CASE WHEN cmonth = '4' THEN ccost ELSE 0 END) AS Apr,SUM(CASE WHEN cmonth = '5' THEN ccost ELSE 0 END) AS May,SUM(CASE WHEN cmonth = '6' THEN ccost ELSE 0 END) AS Jun,SUM(CASE WHEN cmonth = '7' THEN ccost ELSE 0 END) AS Jul,SUM(CASE WHEN cmonth = '8' THEN ccost ELSE 0 END) AS Aug,SUM(CASE WHEN cmonth = '9' THEN ccost ELSE 0 END) AS Sep,SUM(CASE WHEN cmonth = '10' THEN ccost ELSE 0 END) AS Oct,SUM(CASE WHEN cmonth = '11' THEN ccost ELSE 0 END) AS Nov,SUM(CASE WHEN cmonth = '12' THEN ccost ELSE 0 END) AS [Dec],case when SUM(ccost)<>0 then SUM(ccost) ELSE 0 END AS [total]from #temp01where cyear=@year and csaletype='外销'union all/*外销毛利润*/select 'profit Export' as item,SUM(CASE WHEN cmonth = '1' THEN camount-ccost ELSE 0 END) AS Jan,SUM(CASE WHEN cmonth = '2' THEN camount-ccost ELSE 0 END) AS Feb,SUM(CASE WHEN cmonth = '3' THEN camount-ccost ELSE 0 END) AS Mar,SUM(CASE WHEN cmonth = '4' THEN camount-ccost ELSE 0 END) AS Apr,SUM(CASE WHEN cmonth = '5' THEN camount-ccost ELSE 0 END) AS May,SUM(CASE WHEN cmonth = '6' THEN camount-ccost ELSE 0 END) AS Jun,SUM(CASE WHEN cmonth = '7' THEN camount-ccost ELSE 0 END) AS Jul,SUM(CASE WHEN cmonth = '8' THEN camount-ccost ELSE 0 END) AS Aug,SUM(CASE WHEN cmonth = '9' THEN camount-ccost ELSE 0 END) AS Sep,SUM(CASE WHEN cmonth = '10' THEN camount-ccost ELSE 0 END) AS Oct,SUM(CASE WHEN cmonth = '11' THEN camount-ccost ELSE 0 END) AS Nov,SUM(CASE WHEN cmonth = '12' THEN camount-ccost ELSE 0 END) AS [Dec],case when SUM(camount-ccost)<>0 then SUM(camount-ccost)  ELSE 0 END AS [total]from #temp01 where cyear=@year and  csaletype='外销'union all/*内销毛利率*/select 'profit margin Domestic' as item,case when SUM(CASE WHEN cmonth = '1' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '1' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '1' THEN camount ELSE 0 END)*100 else 0 END AS Jan, case when SUM(CASE WHEN cmonth = '2' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '2' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '2' THEN camount ELSE 0 END)*100  ELSE 0 END AS Feb, case when SUM(CASE WHEN cmonth = '3' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '3' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '3' THEN camount ELSE 0 END)*100  ELSE 0 END AS Mar,case when SUM(CASE WHEN cmonth = '4' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '4' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '4' THEN camount ELSE 0 END)*100  ELSE 0 END AS Apr,case when SUM(CASE WHEN cmonth = '5' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '5' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '5' THEN camount ELSE 0 END)*100  ELSE 0 END AS May,case when SUM(CASE WHEN cmonth = '6' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '6' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '6' THEN camount ELSE 0 END)*100  ELSE 0 END AS Jun,case when SUM(CASE WHEN cmonth = '7' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '7' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '7' THEN camount ELSE 0 END)*100  ELSE 0 END AS Jul,case when SUM(CASE WHEN cmonth = '8' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '8' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '8' THEN camount ELSE 0 END)*100  ELSE 0 END AS Aug,case when SUM(CASE WHEN cmonth = '9' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '9' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '9' THEN camount ELSE 0 END)*100  ELSE 0 END AS Sep,case when SUM(CASE WHEN cmonth = '10' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '10' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '10' THEN camount ELSE 0 END)*100  ELSE 0 END AS Oct,case when SUM(CASE WHEN cmonth = '11' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '11' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '11' THEN camount ELSE 0 END)*100  ELSE 0 END AS Nov,case when SUM(CASE WHEN cmonth = '12' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '12' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '12' THEN camount ELSE 0 END)*100  ELSE 0 END AS [Dec],case when SUM(camount)<>0 then SUM(camount-ccost)/SUM(camount)*100  ELSE 0 END AS [total]from #temp01where cyear=@year and csaletype='内销'union all/*内销毛利率*/select 'profit margin Export' as item,case when SUM(CASE WHEN cmonth = '1' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '1' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '1' THEN camount ELSE 0 END)*100 else 0 END AS Jan, case when SUM(CASE WHEN cmonth = '2' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '2' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '2' THEN camount ELSE 0 END)*100  ELSE 0 END AS Feb, case when SUM(CASE WHEN cmonth = '3' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '3' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '3' THEN camount ELSE 0 END)*100  ELSE 0 END AS Mar,case when SUM(CASE WHEN cmonth = '4' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '4' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '4' THEN camount ELSE 0 END)*100  ELSE 0 END AS Apr,case when SUM(CASE WHEN cmonth = '5' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '5' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '5' THEN camount ELSE 0 END)*100  ELSE 0 END AS May,case when SUM(CASE WHEN cmonth = '6' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '6' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '6' THEN camount ELSE 0 END)*100  ELSE 0 END AS Jun,case when SUM(CASE WHEN cmonth = '7' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '7' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '7' THEN camount ELSE 0 END)*100  ELSE 0 END AS Jul,case when SUM(CASE WHEN cmonth = '8' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '8' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '8' THEN camount ELSE 0 END)*100  ELSE 0 END AS Aug,case when SUM(CASE WHEN cmonth = '9' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '9' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '9' THEN camount ELSE 0 END)*100  ELSE 0 END AS Sep,case when SUM(CASE WHEN cmonth = '10' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '10' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '10' THEN camount ELSE 0 END)*100  ELSE 0 END AS Oct,case when SUM(CASE WHEN cmonth = '11' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '11' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '11' THEN camount ELSE 0 END)*100  ELSE 0 END AS Nov,case when SUM(CASE WHEN cmonth = '12' THEN camount ELSE 0 END)<>0 then SUM(CASE WHEN cmonth = '12' THEN camount-ccost ELSE 0 END)/SUM(CASE WHEN cmonth = '12' THEN camount ELSE 0 END)*100  ELSE 0 END AS [Dec],case when SUM(camount)<>0 then SUM(camount-ccost)/SUM(camount)*100  ELSE 0 END AS [total]from #temp01where cyear=@year and csaletype='外销')d/*内外销结构分析*/selecte.item,e.lastyear,f.Jan,f.Feb,f.Mar,f.Apr,f.May,f.Jun,f.Jul,f.Aug,f.Sep,f.Oct,f.Nov,f.[Dec],f.[total]from #temp03 as e left join #temp04 as f on e.item=f.item/*净利润走势百分比*/select xxx.item,xxx.lastyear,yyy.Jan,yyy.Feb,yyy.Mar,yyy.Apr,yyy.May,yyy.Jun,yyy.Jul,yyy.Aug,yyy.Sep,yyy.Oct,yyy.Nov,yyy.[Dec] from(SELECT x.item,convert(decimal(10,2),x.lastyear/y.lastyear)  as lastyear  from(SELECT 'Profit' as item, sum(LE017) as 'lastyear'from ACTLEwhere LE002=@lastyear AND LE001='4103' and LE003<>'00') xinner join (select 'Profit' as item,SUM(camount) as 'lastyear'from #temp01where cyear=@lastyear) y on x.item=y.item)xxxinner join (select xx.item,case when yy.Jan<>0  then convert(decimal(10,2),xx.Jan/yy.Jan) else 0 end as Jan,case when yy.Feb<>0  then convert(decimal(10,2),xx.Feb/yy.Feb) else 0 end as Feb,case when yy.Mar<>0  then convert(decimal(10,2),xx.Mar/yy.Mar) else 0 end as Mar,case when yy.Apr<>0  then convert(decimal(10,2),xx.Apr/yy.Apr) else 0 end as Apr,case when yy.May<>0  then convert(decimal(10,2),xx.May/yy.May) else 0 end as May,case when yy.Jun<>0  then convert(decimal(10,2),xx.Jun/yy.Jun) else 0 end as Jun,case when yy.Jul<>0  then convert(decimal(10,2),xx.Jul/yy.Jul) else 0 end as Jul,case when yy.Aug<>0  then convert(decimal(10,2),xx.Aug/yy.Aug) else 0 end as Aug,case when yy.Sep<>0  then convert(decimal(10,2),xx.Sep/yy.Sep) else 0 end as Sep,case when yy.Oct<>0  then convert(decimal(10,2),xx.Oct/yy.Oct) else 0 end as Oct,case when yy.Nov<>0  then convert(decimal(10,2),xx.Nov/yy.Nov) else 0 end as Nov,case when yy.[Dec]<>0  then convert(decimal(10,2),xx.[Dec]/yy.[Dec]) else 0 end as [Dec],case when yy.total<>0  then convert(decimal(10,2),xx.total/yy.total) else 0 end as totalfrom (/*本年利润：会计科目各期汇总档ACTLE 4103本年利润科目 */SELECT 'Profit' as item,SUM(CASE WHEN LE003 ='01' THEN LE017 ELSE 0 END) AS Jan,SUM(CASE WHEN LE003 = '02' THEN LE017 ELSE 0 END) AS Feb,SUM(CASE WHEN LE003 = '03' THEN LE017 ELSE 0 END) AS Mar,SUM(CASE WHEN LE003 = '04' THEN LE017 ELSE 0 END) AS Apr,SUM(CASE WHEN LE003 = '05' THEN LE017 ELSE 0 END) AS May,SUM(CASE WHEN LE003 = '06' THEN LE017 ELSE 0 END) AS Jun,SUM(CASE WHEN LE003 = '07' THEN LE017 ELSE 0 END) AS Jul,SUM(CASE WHEN LE003 = '08' THEN LE017 ELSE 0 END) AS Aug,SUM(CASE WHEN LE003 = '09' THEN LE017 ELSE 0 END) AS Sep,SUM(CASE WHEN LE003 = '10' THEN LE017 ELSE 0 END) AS Oct,SUM(CASE WHEN LE003 = '11' THEN LE017 ELSE 0 END) AS Nov,SUM(CASE WHEN LE003 = '12' THEN LE017 ELSE 0 END) AS [Dec],sum(LE017) as totalfrom ACTLEwhere LE002=@year AND LE001='4103' )xxinner join (select 'Profit' as item,SUM(CASE WHEN cmonth = '1' THEN camount ELSE 0 END) AS Jan,SUM(CASE WHEN cmonth = '2' THEN camount ELSE 0 END) AS Feb,SUM(CASE WHEN cmonth = '3' THEN camount ELSE 0 END) AS Mar,SUM(CASE WHEN cmonth = '4' THEN camount ELSE 0 END) AS Apr,SUM(CASE WHEN cmonth = '5' THEN camount ELSE 0 END) AS May,SUM(CASE WHEN cmonth = '6' THEN camount ELSE 0 END) AS Jun,SUM(CASE WHEN cmonth = '7' THEN camount ELSE 0 END) AS Jul,SUM(CASE WHEN cmonth = '8' THEN camount ELSE 0 END) AS Aug,SUM(CASE WHEN cmonth = '9' THEN camount ELSE 0 END) AS Sep,SUM(CASE WHEN cmonth = '10' THEN camount ELSE 0 END) AS Oct,SUM(CASE WHEN cmonth = '11' THEN camount ELSE 0 END) AS Nov,SUM(CASE WHEN cmonth = '12' THEN camount ELSE 0 END) AS [Dec],sum(camount) as totalfrom #temp01where cyear=@year)yy on xx.item=yy.item)yyy on xxx.item=yyy.item/*毛利率走势百分比*/--去年总毛利率/* 毛利润率*/select mm.item,mm.lastyear,nn.Jan,nn.Feb,nn.Mar,nn.Apr,nn.May,nn.Jun,nn.Jul,nn.Aug,nn.Sep,nn.Oct,nn.Nov,nn.[Dec]from(select 'Profit' as item,convert(decimal(10,2),SUM(camount-ccost)/SUM(camount))*100 AS lastyearfrom #temp01where cyear=@lastyear)mminner join (--今年分期毛利率select 'Profit' item,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,[Dec] from #temp02where item='Profit Margin'+@year)nn on mm.item=nn.itemdrop table #temp01drop table #temp02drop table #temp03drop table #temp04/*获取收入 应收 应付 库存*/SELECT * FROM UF_SAAI/*Sales revenue&Account receivable*/SELECT * FROM UF_SAAIwhere item like '%Sales revenue%' or item like '%Account receivable%'/*Sales revenue&Account payable*/SELECT * FROM UF_SAAIwhere item like '%Sales revenue%' or item like '%Account payable%'/*Sales revenue&Inventories*/SELECT * FROM UF_SAAIwhere item like '%Sales revenue%' or item like '%Inventories%'End

中间表：

CREATE TABLE [dbo].[UF_SAAI]([item] [nchar](30) NULL,[Jan] [decimal](18, 2) NULL,[Feb] [decimal](18, 2) NULL,[Mar] [decimal](18, 2) NULL,[Apr] [decimal](18, 2) NULL,[May] [decimal](18, 2) NULL,[Jun] [decimal](18, 2) NULL,[Jul] [decimal](18, 2) NULL,[Aug] [decimal](18, 2) NULL,[Sep] [decimal](18, 2) NULL,[Oct] [decimal](18, 2) NULL,[Nov] [decimal](18, 2) NULL,[Dec] [decimal](18, 2) NULL) ON [PRIMARY]

每月月结后运行：

GO/****** Object:  StoredProcedure [dbo].[UP_PullDateForUF_SAAI]    Script Date: 04/19/2012 10:50:29 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO--============================================================--用途:年度收入、应收账款、应付账款、库存账款 填充数据--作者:龚德辉--日期:2012-04-18--============================================================ALTER  PROCEDURE [dbo].[UP_PullDateForUF_SAAI]asbegin/*年度收入、应收账款、应付账款、库存账款 *1.收入账款科目:6001 贷方*2.应付账款科目:2202 余额-借方+贷方*3.应收账款科目:1122 余额+借方-贷方*4.库存账款科目：原材料料借方1403+库存商品借方1405+发出商品借方1406-发出商品贷方1406-存货跌价准备(贷方)1471+生产成本借方(基本生产成本)500101* LE014 借方 LE017 贷方*/--SQL:测试2011年--今年与去年年份declare@year as nchar(4),@lastyear as nchar(4)set @year=datepart(year,getdate()) set @lastyear=datepart(year,dateadd(year,-1,getdate()))  /*收入*/select * into #tb from (SELECT 'Sales revenue'+LE002  as item , SUM(LE017) amount,'Jan' [month]   from ACTLE where LE002 in (@year,@lastyear) AND LEFT(LE001,4)='6001' AND LE003 IN('00','01')  group by LE002union allSELECT 'Sales revenue'+LE002 as item, SUM(LE017) amount,'Feb' [month]   from ACTLE where LE002 in (@year,@lastyear)AND LEFT(LE001,4)='6001' AND LE003 IN('00','02')  group by LE002union allSELECT 'Sales revenue'+LE002 as item, SUM(LE017) amount,'Mar' [month]  from ACTLE where LE002 in (@year,@lastyear) AND LEFT(LE001,4)='6001' AND LE003 IN('00','03')  group by LE002union allSELECT 'Sales revenue'+LE002 as item, SUM(LE017) amount,'Apr' [month]  from ACTLE where LE002 in (@year,@lastyear) AND LEFT(LE001,4)='6001' AND LE003 IN('00','04')  group by LE002union allSELECT 'Sales revenue'+LE002 as item, SUM(LE017) amount,'May' [month]  from ACTLE where LE002 in (@year,@lastyear) AND LEFT(LE001,4)='6001' AND LE003 IN('00','05')  group by LE002union allSELECT 'Sales revenue'+LE002 as item, SUM(LE017) amount,'Jun' [month]  from ACTLE where LE002 in (@year,@lastyear) AND LEFT(LE001,4)='6001' AND LE003 IN('00','06')  group by LE002union allSELECT 'Sales revenue'+LE002 as item, SUM(LE017) amount,'Jul' [month]  from ACTLE where LE002 in (@year,@lastyear) AND LEFT(LE001,4)='6001' AND LE003 IN('00','07')  group by LE002union allSELECT 'Sales revenue'+LE002 as item, SUM(LE017) amount,'Aug' [month]  from ACTLE where LE002 in (@year,@lastyear) AND LEFT(LE001,4)='6001' AND LE003 IN('00','08')  group by LE002union allSELECT 'Sales revenue'+LE002 as item, SUM(LE017) amount,'Sep' [month]  from ACTLE where LE002 in (@year,@lastyear) AND LEFT(LE001,4)='6001' AND LE003 IN('00','09')  group by LE002union allSELECT 'Sales revenue'+LE002 as item, SUM(LE017) amount,'Oct' [month]  from ACTLE where LE002 in (@year,@lastyear) AND LEFT(LE001,4)='6001' AND LE003 IN('00','10')   group by LE002union allSELECT 'Sales revenue'+LE002 as item, SUM(LE017)   amount, 'Nov' [month]  from ACTLE where LE002 in (@year,@lastyear) AND LEFT(LE001,4)='6001' AND LE003 IN('00','11')   group by LE002union allSELECT 'Sales revenue'+LE002 as item, SUM(LE017) amount,'Dec' [month]  from ACTLE where LE002 in (@year,@lastyear) AND LEFT(LE001,4)='6001' AND LE003 IN('00','12')   group by LE002)aa/*应付账款*/select * into #tb1 from (SELECT 'Account payable'+LE002 as item, SUM(LE017)-SUM(LE014) amount,'Jan' [month]   from ACTLE where LE002 in (@year,@lastyear) AND LEFT(LE001,4)='2202' AND LE003<='01'   group by LE002  union allSELECT 'Account payable'+LE002 as item , SUM(LE017)-SUM(LE014) amount,'Feb' [month]   from ACTLE where LE002 in (@year,@lastyear)  AND LEFT(LE001,4)='2202' AND LE003 <='02'   group by LE002union allSELECT 'Account payable'+LE002 as item, SUM(LE017)-SUM(LE014) amount,'Mar' [month]  from ACTLE where LE002 in (@year,@lastyear)  AND LEFT(LE001,4)='2202' AND LE003 <='03'   group by LE002union allSELECT 'Account payable'+LE002 as item, SUM(LE017)-SUM(LE014) amount,'Apr' [month]  from ACTLE where LE002 in (@year,@lastyear)  AND LEFT(LE001,4)='2202' AND LE003 <='04'   group by LE002union allSELECT 'Account payable'+LE002 as item, SUM(LE017)-SUM(LE014) amount,'May' [month]  from ACTLE where LE002 in (@year,@lastyear)  AND LEFT(LE001,4)='2202' AND LE003 <='05'   group by LE002union allSELECT 'Account payable'+LE002 as item, SUM(LE017)-SUM(LE014) amount,'Jun' [month]  from ACTLE where LE002 in (@year,@lastyear)  AND LEFT(LE001,4)='2202' AND LE003 <='06'   group by LE002union allSELECT 'Account payable'+LE002 as item, SUM(LE017)-SUM(LE014) amount,'Jul' [month]  from ACTLE where LE002 in (@year,@lastyear)  AND LEFT(LE001,4)='2202' AND LE003 <='07'   group by LE002union allSELECT 'Account payable'+LE002 as item, SUM(LE017)-SUM(LE014) amount,'Aug' [month]  from ACTLE where LE002 in (@year,@lastyear)  AND LEFT(LE001,4)='2202' AND LE003 <='08'   group by LE002union allSELECT 'Account payable'+LE002 as item, SUM(LE017)-SUM(LE014) amount,'Sep' [month]  from ACTLE where LE002 in (@year,@lastyear)  AND LEFT(LE001,4)='2202' AND LE003 <='09'   group by LE002union allSELECT 'Account payable'+LE002 as item, SUM(LE017)-SUM(LE014) amount,'Oct' [month]  from ACTLE where LE002 in (@year,@lastyear) AND LEFT(LE001,4)='2202' AND LE003 <='10'   group by LE002union allSELECT 'Account payable'+LE002 as item, SUM(LE017)-SUM(LE014)   amount, 'Nov' [month]  from ACTLE where LE002 in (@year,@lastyear)  AND LEFT(LE001,4)='2202' AND LE003 <='11'   group by LE002union allSELECT 'Account payable'+LE002 as item, SUM(LE017)-SUM(LE014) amount,'Dec' [month]  from ACTLE where LE002 in (@year,@lastyear)  AND LEFT(LE001,4)='2202' AND LE003 <='12'   group by LE002)bb/*应收账款*/select * into #tb2 from (SELECT 'Account receivable'+LE002 as item, SUM(LE014)-SUM(LE017) amount,'Feb' [month]   from ACTLE where LE002 in (@year,@lastyear) AND LEFT(LE001,4)='1122' AND LE003<='01'   group by LE002  union allSELECT 'Account receivable'+LE002 as item , SUM(LE014)-SUM(LE017) amount,'Jan' [month]   from ACTLE where LE002 in (@year,@lastyear) AND LEFT(LE001,4)='1122' AND LE003 <='02'   group by LE002union allSELECT 'Account receivable'+LE002 as item, SUM(LE014)-SUM(LE017) amount,'Feb' [month]   from ACTLE where LE002 in (@year,@lastyear) AND LEFT(LE001,4)='1122' AND LE003 <='03'   group by LE002union allSELECT 'Account receivable'+LE002 as item, SUM(LE014)-SUM(LE017) amount,'Mar' [month]  from ACTLE where LE002 in (@year,@lastyear) AND LEFT(LE001,4)='1122' AND LE003 <='03'   group by LE002union allSELECT 'Account receivable'+LE002 as item, SUM(LE014)-SUM(LE017) amount,'Apr' [month]  from ACTLE where LE002 in (@year,@lastyear) AND LEFT(LE001,4)='1122' AND LE003 <='04'   group by LE002union allSELECT 'Account receivable'+LE002 as item, SUM(LE014)-SUM(LE017) amount,'May' [month]  from ACTLE where LE002 in (@year,@lastyear) AND LEFT(LE001,4)='1122' AND LE003 <='05'   group by LE002union allSELECT 'Account receivable'+LE002 as item, SUM(LE014)-SUM(LE017) amount,'Jun' [month]  from ACTLE where LE002 in (@year,@lastyear) AND LEFT(LE001,4)='1122' AND LE003 <='06'   group by LE002union allSELECT 'Account receivable'+LE002 as item, SUM(LE014)-SUM(LE017) amount,'Jul' [month]  from ACTLE where LE002 in (@year,@lastyear) AND LEFT(LE001,4)='1122' AND LE003 <='07'   group by LE002union allSELECT 'Account receivable'+LE002 as item, SUM(LE014)-SUM(LE017) amount,'Aug' [month]  from ACTLE where LE002 in (@year,@lastyear) AND LEFT(LE001,4)='1122' AND LE003 <='08'   group by LE002union allSELECT 'Account receivable'+LE002 as item, SUM(LE014)-SUM(LE017) amount,'Sep' [month]  from ACTLE where LE002 in (@year,@lastyear) AND LEFT(LE001,4)='1122' AND LE003 <='09'   group by LE002union allSELECT 'Account receivable'+LE002 as item, SUM(LE014)-SUM(LE017)amount,'Oct' [month]  from ACTLE where LE002 in (@year,@lastyear) AND LEFT(LE001,4)='1122' AND LE003 <='10'   group by LE002union allSELECT 'Account receivable'+LE002 as item, SUM(LE014)-SUM(LE017)  amount, 'Nov' [month]  from ACTLE where LE002 in (@year,@lastyear) AND LEFT(LE001,4)='1122' AND LE003 <='11'   group by LE002union allSELECT 'Account receivable'+LE002 as item, SUM(LE014)-SUM(LE017) amount,'Dec' [month]  from ACTLE where LE002 in (@year,@lastyear) AND LEFT(LE001,4)='1122' AND LE003 <='12'   group by LE002)cc/*库存账款*/select * into #tb3from(      select 'Inventories'+LE002 as item, sum(A+B+C+D) amount,'Jan' as [month] from (SELECT   LE002,case when LEFT(LE001,4)='1403' then LE014-LE017 ELSE 0 END AS A,case when LEFT(LE001,6) in ('140501','140502','140503') then LE014-LE017  ELSE 0 END AS B,case when LEFT(LE001,4)='1471' then LE014-LE017 ELSE 0 END AS C,case when LEFT(LE001,6)='500101' then LE014-LE017 ELSE 0 END AS Dfrom ACTLE where (LE002 in (@year,@lastyear) AND LEFT(LE001,4) in ('1403','1471') AND LE003 in ('00','01'))       or (LE002 in (@year,@lastyear) AND LEFT(LE001,6)in('500101','140502','140501','140503') AND LE003 in ('00','01'))  )d group by d.LE002union allselect 'Inventories'+LE002 as item,sum(A+B+C+D) amount,'Feb'   from (SELECT  LE002,case when LEFT(LE001,4)='1403' then LE014-LE017 ELSE 0 END AS A,case when LEFT(LE001,6) in ('140501','140502','140503') then LE014-LE017  ELSE 0 END AS B,case when LEFT(LE001,4)='1471' then LE014-LE017 ELSE 0 END AS C,case when LEFT(LE001,6)='500101' then LE014-LE017 ELSE 0 END AS Dfrom ACTLE where (LE002 in (@year,@lastyear) AND LEFT(LE001,4) in ('1403','1471') AND LE003<='02')       or (LE002 in (@year,@lastyear) AND LEFT(LE001,6)in('500101','140502','140501','140503') AND LE003<='02') )d  group by d.LE002 union allselect 'Inventories'+LE002 as item, sum(A+B+C+D) amount,'Mar'  from (SELECT  LE002,case when LEFT(LE001,4)='1403' then LE014-LE017 ELSE 0 END AS A,case when LEFT(LE001,6) in ('140501','140502','140503') then LE014-LE017  ELSE 0 END AS B,case when LEFT(LE001,4)='1471' then LE014-LE017 ELSE 0 END AS C,case when LEFT(LE001,6)='500101' then LE014-LE017 ELSE 0 END AS Dfrom ACTLE where (LE002 in (@year,@lastyear) AND LEFT(LE001,4) in ('1403','1471') AND LE003<='03')       or (LE002 in (@year,@lastyear) AND LEFT(LE001,6)in('500101','140502','140501','140503') AND LE003<='03') )d  group by d.LE002union allselect 'Inventories'+LE002 as item,sum(A+B+C+D) amount,'Apr'  from (SELECT  LE002,case when LEFT(LE001,4)='1403' then LE014-LE017 ELSE 0 END AS A,case when LEFT(LE001,6) in ('140501','140502','140503') then LE014-LE017  ELSE 0 END AS B,case when LEFT(LE001,4)='1471' then LE014-LE017 ELSE 0 END AS C,case when LEFT(LE001,6)='500101' then LE014-LE017 ELSE 0 END AS Dfrom ACTLE where (LE002 in (@year,@lastyear) AND LEFT(LE001,4) in ('1403','1471') AND LE003<='04')       or (LE002 in (@year,@lastyear) AND LEFT(LE001,6)in('500101','140502','140501','140503') AND LE003<='04') )d  group by d.LE002union allselect 'Inventories'+LE002 as item,sum(A+B+C+D) amount,'May'  from (SELECT  LE002,case when LEFT(LE001,4)='1403' then LE014-LE017 ELSE 0 END AS A,case when LEFT(LE001,6) in ('140501','140502','140503') then LE014-LE017  ELSE 0 END AS B,case when LEFT(LE001,4)='1471' then LE014-LE017 ELSE 0 END AS C,case when LEFT(LE001,6)='500101' then LE014-LE017 ELSE 0 END AS Dfrom ACTLE where (LE002 in (@year,@lastyear) AND LEFT(LE001,4) in ('1403','1471') AND LE003<='05')       or (LE002 in (@year,@lastyear) AND LEFT(LE001,6)in('500101','140502','140501','140503') AND LE003<='05') )d  group by d.LE002 union allselect 'Inventories'+LE002 as item,sum(A+B+C+D) amount,'Jun'  from (SELECT  LE002,case when LEFT(LE001,4)='1403' then LE014-LE017 ELSE 0 END AS A,case when LEFT(LE001,6) in ('140501','140502','140503') then LE014-LE017  ELSE 0 END AS B,case when LEFT(LE001,4)='1471' then LE014-LE017 ELSE 0 END AS C,case when LEFT(LE001,6)='500101' then LE014-LE017 ELSE 0 END AS Dfrom ACTLE where (LE002 in (@year,@lastyear) AND LEFT(LE001,4) in ('1403','1471') AND LE003<='06')       or (LE002 in (@year,@lastyear) AND LEFT(LE001,6)in('500101','140502','140501','140503') AND LE003<='06') )d  group by d.LE002 union allselect 'Inventories'+LE002 as item,sum(A+B+C+D) amount,'Jul'  from (SELECT  LE002,case when LEFT(LE001,4)='1403' then LE014-LE017 ELSE 0 END AS A,case when LEFT(LE001,6) in ('140501','140502','140503') then LE014-LE017  ELSE 0 END AS B,case when LEFT(LE001,4)='1471' then LE014-LE017 ELSE 0 END AS C,case when LEFT(LE001,6)='500101' then LE014-LE017 ELSE 0 END AS Dfrom ACTLE where (LE002 in (@year,@lastyear) AND LEFT(LE001,4) in ('1403','1471') AND LE003<='07')       or (LE002 in (@year,@lastyear) AND LEFT(LE001,6)in('500101','140502','140501','140503') AND LE003<='07') )d  group by d.LE002 union allselect 'Inventories'+LE002 as item,sum(A+B+C+D) amount,'Aug'  from (SELECT  LE002,case when LEFT(LE001,4)='1403' then LE014-LE017 ELSE 0 END AS A,case when LEFT(LE001,6) in ('140501','140502','140503') then LE014-LE017  ELSE 0 END AS B,case when LEFT(LE001,4)='1471' then LE014-LE017 ELSE 0 END AS C,case when LEFT(LE001,6)='500101' then LE014-LE017 ELSE 0 END AS Dfrom ACTLE where (LE002 in (@year,@lastyear) AND LEFT(LE001,4) in ('1403','1471') AND LE003<='08')       or (LE002 in (@year,@lastyear) AND LEFT(LE001,6)in('500101','140502','140501','140503') AND LE003<='08') )d  group by d.LE002 union allselect 'Inventories'+LE002 as item,sum(A+B+C+D) amount,'Sep'  from (SELECT  LE002,case when LEFT(LE001,4)='1403' then LE014-LE017 ELSE 0 END AS A,case when LEFT(LE001,6) in ('140501','140502','140503') then LE014-LE017  ELSE 0 END AS B,case when LEFT(LE001,4)='1471' then LE014-LE017 ELSE 0 END AS C,case when LEFT(LE001,6)='500101' then LE014-LE017 ELSE 0 END AS Dfrom ACTLE where (LE002 in (@year,@lastyear) AND LEFT(LE001,4) in ('1403','1471') AND LE003<='09')       or (LE002 in (@year,@lastyear) AND LEFT(LE001,6)in('500101','140502','140501','140503') AND LE003<='09') )d  group by d.LE002 union allselect 'Inventories'+LE002 as item,sum(A+B+C+D) amount,'Oct'  from (SELECT  LE002,case when LEFT(LE001,4)='1403' then LE014-LE017 ELSE 0 END AS A,case when LEFT(LE001,6) in ('140501','140502','140503') then LE014-LE017  ELSE 0 END AS B,case when LEFT(LE001,4)='1471' then LE014-LE017 ELSE 0 END AS C,case when LEFT(LE001,6)='500101' then LE014-LE017 ELSE 0 END AS Dfrom ACTLE where (LE002 in (@year,@lastyear) AND LEFT(LE001,4) in ('1403','1471') AND LE003<='10')       or (LE002 in (@year,@lastyear) AND LEFT(LE001,6)in('500101','140502','140501','140503') AND LE003<='10') )d  group by d.LE002 union allselect 'Inventories'+LE002 as item,sum(A+B+C+D) amount,'Nov'  from (SELECT  LE002,case when LEFT(LE001,4)='1403' then LE014-LE017 ELSE 0 END AS A,case when LEFT(LE001,6) in ('140501','140502','140503') then LE014-LE017  ELSE 0 END AS B,case when LEFT(LE001,4)='1471' then LE014-LE017 ELSE 0 END AS C,case when LEFT(LE001,6)='500101' then LE014-LE017 ELSE 0 END AS Dfrom ACTLE where (LE002 in (@year,@lastyear) AND LEFT(LE001,4) in ('1403','1471') AND LE003<='11')       or (LE002 in (@year,@lastyear) AND LEFT(LE001,6)in('500101','140502','140501','140503') AND LE003<='11') )d       group by d.LE002union allselect 'Inventories'+LE002 as item,sum(A+B+C+D) amount, 'Dec' from (SELECT  LE002,case when LEFT(LE001,4)='1403' then LE014-LE017 ELSE 0 END AS A,case when LEFT(LE001,6) in ('140501','140502','140503') then LE014-LE017  ELSE 0 END AS B,case when LEFT(LE001,4)='1471' then LE014-LE017 ELSE 0 END AS C,case when LEFT(LE001,6)='500101' then LE014-LE017 ELSE 0 END AS Dfrom ACTLE where (LE002 in (@year,@lastyear) AND LEFT(LE001,4) in ('1403','1471') AND LE003<='12')       or (LE002 in (@year,@lastyear) AND LEFT(LE001,6)in('500101','140502','140501','140503') AND LE003<='12') )d  group by d.LE002)dd/*清除数据*/delete from UF_SAAIinsert into UF_SAAI(item,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,[Dec])select * from(/*收入*/select a.item,sum(a.Jan) Jan,sum(a.Feb) Feb,sum(a.Mar) Mar,sum(a.Apr) Apr,sum(a.May) May,sum(a.Jun)Jun,sum(a.Jul)Jul,sum(a.Aug) Aug,sum(a.Sep) Sep,sum(a.Oct) Oct,sum(a.Nov) Nov,sum(a.[Dec]) [Dec]from (select item, case when [month]='Jan' then sum(amount) else 0 End as Jan,case when [month]='Feb' then sum(amount) else 0 End as Feb,case when [month]='Mar' then sum(amount) else 0 End as Mar,case when [month]='Apr' then sum(amount) else 0 End as Apr,case when [month]='May' then sum(amount) else 0 End as May,case when [month]='Jun' then sum(amount) else 0 End as Jun,case when [month]='Jul' then sum(amount) else 0 End as Jul,case when [month]='Aug' then sum(amount) else 0 End as Aug,case when [month]='Sep' then sum(amount) else 0 End as Sep,case when [month]='Oct' then sum(amount) else 0 End as Oct,case when [month]='Nov' then sum(amount) else 0 End as Nov,case when [month]='Dec' then sum(amount) else 0 End as [Dec]from #tbgroup by item,[month])agroup by a.itemunion all/*应收账款*/select a.item,sum(a.Jan) Jan,sum(a.Feb) Feb,sum(a.Mar) Mar,sum(a.Apr) Apr,sum(a.May)May,sum(a.Jun)Jun,sum(a.Jul)Jul,sum(a.Aug) Aug,sum(a.Sep) Sep,sum(a.Oct) Oct,sum(a.Nov) Nov,sum(a.[Dec]) [Dec]from (select item, case when [month]='Jan' then sum(amount) else 0 End as Jan,case when [month]='Feb' then sum(amount) else 0 End as Feb,case when [month]='Mar' then sum(amount) else 0 End as Mar,case when [month]='Apr' then sum(amount) else 0 End as Apr,case when [month]='May' then sum(amount) else 0 End as May,case when [month]='Jun' then sum(amount) else 0 End as Jun,case when [month]='Jul' then sum(amount) else 0 End as Jul,case when [month]='Aug' then sum(amount) else 0 End as Aug,case when [month]='Sep' then sum(amount) else 0 End as Sep,case when [month]='Oct' then sum(amount) else 0 End as Oct,case when [month]='Nov' then sum(amount) else 0 End as Nov,case when [month]='Dec' then sum(amount) else 0 End as [Dec]from #tb2group by item,[month])agroup by a.itemunion all/*应付账款*/select a.item,sum(a.Jan) Jan,sum(a.Feb) Feb,sum(a.Mar) Mar,sum(a.Apr) Apr,sum(a.May)May,sum(a.Jun)Jun,sum(a.Jul)Jul,sum(a.Aug) Aug,sum(a.Sep) Sep,sum(a.Oct) Oct,sum(a.Nov) Nov,sum(a.[Dec]) [Dec]from (select item, case when [month]='Jan' then sum(amount) else 0 End as Jan,case when [month]='Feb' then sum(amount) else 0 End as Feb,case when [month]='Mar' then sum(amount) else 0 End as Mar,case when [month]='Apr' then sum(amount) else 0 End as Apr,case when [month]='May' then sum(amount) else 0 End as May,case when [month]='Jun' then sum(amount) else 0 End as Jun,case when [month]='Jul' then sum(amount) else 0 End as Jul,case when [month]='Aug' then sum(amount) else 0 End as Aug,case when [month]='Sep' then sum(amount) else 0 End as Sep,case when [month]='Oct' then sum(amount) else 0 End as Oct,case when [month]='Nov' then sum(amount) else 0 End as Nov,case when [month]='Dec' then sum(amount) else 0 End as [Dec]from #tb1group by item,[month])agroup by a.itemunion all/*库存账款*/select a.item,sum(a.Jan) Jan,sum(a.Feb) Feb,sum(a.Mar) Mar,sum(a.Apr) Apr,sum(a.May)May,sum(a.Jun)Jun,sum(a.Jul)Jul,sum(a.Aug) Aug,sum(a.Sep) Sep,sum(a.Oct) Oct,sum(a.Nov) Nov,sum(a.[Dec]) [Dec]from (select item, case when [month]='Jan' then sum(amount) else 0 End as Jan,case when [month]='Feb' then sum(amount) else 0 End as Feb,case when [month]='Mar' then sum(amount) else 0 End as Mar,case when [month]='Apr' then sum(amount) else 0 End as Apr,case when [month]='May' then sum(amount) else 0 End as May,case when [month]='Jun' then sum(amount) else 0 End as Jun,case when [month]='Jul' then sum(amount) else 0 End as Jul,case when [month]='Aug' then sum(amount) else 0 End as Aug,case when [month]='Sep' then sum(amount) else 0 End as Sep,case when [month]='Oct' then sum(amount) else 0 End as Oct,case when [month]='Nov' then sum(amount) else 0 End as Nov,case when [month]='Dec' then sum(amount) else 0 End as [Dec]from #tb3group by item,[month])agroup by a.item)mmdrop table #tbdrop table #tb1drop table #tb2drop table #tb3end