[易飛]財務表之銷售分析

預存程序:

USE [ZM]GO/****** Object:  StoredProcedure [dbo].[UP_SalesAnalyis]    Script Date: 03/31/2012 08:27:40 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================   -- Author: <David Gang>   -- Create date: <2012-03-30>    -- Description: <銷售分析按地區客戶產品>   -- ============================================= ALTER Procedure [dbo].[UP_SalesAnalyis]asbegindeclare @yyyymm char(6)set @yyyymm=datepart(year,getdate())*100+datepart(month,getdate()) ------------------------------銷售領域--------------------------------select Customers,sum(Quantities) Quantities,sum(Amount) Amount,sum([Cost of Sales]) as [Cost of Sales],sum(Amount)-sum([Cost of Sales]) as [Gross Margin]into #base from (SELECT  ltrim(MR003) Customers,convert(decimal(15,2),TB022) as Quantities,TB019 as Amount,convert(decimal(15,2),TB022*LA012) as [Cost of Sales]FROM  ACRTB left JOIN ACRTA ON TA001=TB001 AND TA002=TB002LEFT JOIN COPMA ON TA004=MA001LEFT JOIN INVMB ON TB039=MB001inner JOIN INVLA ON LA006=TB005 AND LA007=TB006 AND LA008=TB007 LEFT JOIN (SELECT MR002,MR003 from CMSMR where MR001='2') R ON MA076=R.MR002WHERE left(TA003,6)=@yyyymm  AND TB004 in ('1','2')  and TA025='Y') agroup by Customersorder by Customersselect * into #basetotal from (select Customers,Quantities,Amount,[Cost of Sales],[Gross Margin] from #baseunion allselect 'total' as Customers,sum(Quantities) Quantities,sum(Amount) Amount,sum([Cost of Sales]) [Cost of Sales],sum([Gross Margin]) [Gross Margin] from #base)adeclare @totalAmount as decimal(10,2)declare @totalGrossMargin as decimal(10,2)select @totalAmount=sum(Amount),@totalGrossMargin=sum([Gross Margin]) from #base--銷售月度按地區匯總select Customers,Quantities,Amount,[Cost of Sales],[Gross Margin],convert(decimal(10,2),[Gross Margin]/Amount*100) as GMR,convert(decimal(10,2),Amount/@totalAmount*100) as [Sales%],convert(decimal(10,2),[Gross Margin]/@totalGrossMargin*100) as [GM%] from #basetotal--銷售構成selectCustomers,convert(decimal(10,2),Amount/@totalAmount*100) as [Sales%]from #basetotalwhere Customers<>'total'--利潤構成selectCustomers,convert(decimal(10,2),[Gross Margin]/@totalGrossMargin*100) as [GM%] from #basetotalwhere Customers<>'total'------------------------------統計前10大客戶--------------------------------select top 10 Customers,sum(Quantities) Quantities,sum(Amount) Amount,sum([Cost of Sales]) as [Cost of Sales],sum(Amount)-sum([Cost of Sales]) as [Gross Margin]into #basetop10 from (        SELECT   COPMA.MA002 as  Customers,convert(decimal(15,2),TB022) as Quantities,TB019 as Amount,convert(decimal(15,2),TB022*LA012) as [Cost of Sales]FROM  ACRTB left JOIN ACRTA ON TA001=TB001 AND TA002=TB002LEFT JOIN COPMA ON TA004=MA001LEFT JOIN INVMB ON TB039=MB001inner JOIN INVLA ON LA006=TB005 AND LA007=TB006 AND LA008=TB007WHERE left(TA003,6)=@yyyymm  AND TB004 in ('1','2')  and TA025='Y') bgroup by Customersorder by Amount desc--匯總前十+小計select * into #totaltop10 from (select Customers,Quantities,Amount,[Cost of Sales],[Gross Margin] from #basetop10union allselect 'total' as Customers,sum(Quantities) Quantities,sum(Amount) Amount,sum([Cost of Sales]) [Cost of Sales],sum([Gross Margin]) [Gross Margin] from #basetop10)c--前十大資料表select Customers,Quantities,Amount,[Cost of Sales],[Gross Margin],convert(decimal(10,2),[Gross Margin]/Amount*100) as GMR,convert(decimal(10,2),Amount/@totalAmount*100) as [Sales%],convert(decimal(10,2),[Gross Margin]/@totalGrossMargin*100) as [GM%]from #totaltop10--前十大客戶資料圖表資料select * from #totaltop10where Customers<>'total'---------------------------------------前十大產品銷售--------------------------------------------select top 10 Product,sum(Quantities) Quantities,sum(Amount) Amount,sum([Cost of Sales]) as [Cost of Sales],sum(Amount)-sum([Cost of Sales]) as [Gross Margin]into #ProductTop10 from (SELECT  TB040 Product,convert(decimal(15,2),TB022) Quantities,TB019 Amount, convert(decimal(15,2),TB022*LA012)[Cost of Sales]FROM  ACRTB left JOIN ACRTA ON TA001=TB001 AND TA002=TB002inner JOIN INVLA ON LA006=TB005 AND LA007=TB006 AND LA008=TB007WHERE left(TA003,6)=@yyyymm  AND TB004 in ('1','2')  and TA025='Y') dgroup by Productorder by Amount descdeclare @subtotalAmount as decimal(10,2)declare @subtotalQuantities as decimal(10,2)declare @subtotalCost as decimal(10,2)declare @subtotalGrossMargin as decimal(10,2)select @subtotalQuantities=sum(Quantities),@subtotalAmount=sum(Amount),@subtotalCost=sum([Cost of Sales]),@subtotalGrossMargin=sum([Gross Margin]) from #ProductTop10--匯總產品總計 小計，其他select * into #totalProducttop10 from (select Product,Quantities,Amount,[Cost of Sales],[Gross Margin] from #ProductTop10union allselect 'SubTotal' as Product,sum(Quantities) Quantities,sum(Amount) Amount,sum([Cost of Sales]) [Cost of Sales],sum([Gross Margin]) [Gross Margin] from #ProductTop10union allSELECT 'Others' as Product, sum(convert(decimal(15,2),TB022))-@subtotalQuantities AS Quantities,sum(TB019)-@subtotalAmount AS  Amount, convert(decimal(15,2),sum(TB022*LA012))-@subtotalCost [Cost of Sales],sum(TB019)-convert(decimal(15,2),sum(TB022*LA012))-@subtotalGrossMargin as [Gross Margin]FROM  ACRTB left JOIN ACRTA ON TA001=TB001 AND TA002=TB002inner JOIN INVLA ON LA006=TB005 AND LA007=TB006 AND LA008=TB007WHERE left(TA003,6)=@yyyymm  AND TB004 in ('1','2')  and TA025='Y'union allSELECT 'Total' as Product, sum(convert(decimal(15,2),TB022)) Quantities,sum(TB019)  Amount, convert(decimal(15,2),sum(TB022*LA012)) [Cost of Sales],sum(TB019)-convert(decimal(15,2),sum(TB022*LA012)) as [Gross Margin]FROM  ACRTB left JOIN ACRTA ON TA001=TB001 AND TA002=TB002inner JOIN INVLA ON LA006=TB005 AND LA007=TB006 AND LA008=TB007WHERE left(TA003,6)=@yyyymm  AND TB004 in ('1','2')  and TA025='Y')c--十大產品彙總表select Product,Quantities,Amount,[Cost of Sales],[Gross Margin],convert(decimal(10,2),[Gross Margin]/Amount*100) as GMR,convert(decimal(10,2),Amount/@totalAmount*100) as [Sales%],convert(decimal(10,2),[Gross Margin]/@totalGrossMargin*100) as [GM%]from #totalProducttop10--十大產品銷售收入佔比select top 10 Product,convert(decimal(10,2),Amount/@totalAmount*100) as [Sales%]from #totalProducttop10--十大產品毛利佔比select top 10 Product,convert(decimal(10,2),[Gross Margin]/@totalGrossMargin*100) as [GM%]from #totalProducttop10drop table #basedrop table #basetotaldrop table #totaltop10drop table #basetop10drop table  #ProductTop10 drop table #totalProducttop10end

圖：