[Easy to fly] financial statement of the sales analysis

Stored procedures:

Use [ZM] go/****** object:storedprocedure [dbo].    [Up_salesanalyis] Script date:03/31/2012 08:27:40 ******/set ANSI_NULLS on Go Set QUOTED_IDENTIFIER in Go--============================= ================--Author: <david gang>--Create Date: <2012-03-30>-Description: < Sales analysis by region customer Product >--============================================= ALTER Procedure [dbo]. 
[Up_salesanalyis] As BEGIN declare @yyyymm char (6) Set @yyyymm =datepart (Year,getdate ()) *100+datepart (Month,getdate ())
------------------------------Sales Area--------------------------------Select Customers, sum (quantities) quantities,  SUM (Amount) Amount, sum ([cost ' sales] as [cost ' sales], sum (Amount)-sum ([cost ' sales]) as [Gross Margin] into #base From (SELECT LTrim (MR003) Customers, convert (Decimal (15,2), TB022) as quantities, TB019 as Amount, convert (DE Cimal (15,2), tb022*la012) as [cost ' Sales] from ACRTB left join Acrta on ta001=tb001 and ta002=tb002 left join CO Pma On ta004=ma001 left join INVMB in tb039=mb001 inner 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.mr002 where left (ta003,6) = @yyyymm and TB004 In (' 1 ', ' 2 ") and ta025= ' Y ') a group by Customers ORDER by Customers SELECT * To #basetotal from (select Customer S,quantities,amount,[cost of Sales],[gross Margin] from #base UNION ALL select ' Total ' as customers,sum (quantities) quanti Ties,sum (Amount) amount,sum ([Cost of Sales]) [Cost of Sales],sum ([gross Margin]) [gross Margin] from #base) a declare @t Otalamount as decimal (10,2) declare @totalGrossMargin as decimal (10,2) Select @totalAmount =sum (Amount), @ Totalgrossmargin=sum ([Gross Margin]) from #base-Sales monthly by Region Summary 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-Sales constitute select Customers, convert (Decimal (10,2), amount/@totalAmount *100) as [S Ales%] from #basetotal where customers<> ' total '--profit constitutes select Customers, convert (Decimal (10,2), [Gross margin]/@ totalgrossmargin*100) as [GM%] from #basetotal where customers<> ' total '------------------------------ Statistics Top 10 Customers--------------------------------Select Customers, sum (quantities) quantities, sum (Amount) Amount, sum ([ Cost ' sales] as [cost ' sales], sum (Amount)-sum ([cost ' sales]) as [Gross Margin] into #basetop10 from (SEL ECT COPMA.  MA002 as Customers, convert (Decimal (15,2), TB022) as quantities, TB019 as Amount, convert (Decimal (15,2), tb022*la012)
					As [cost ' Sales] from 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 in la006=tb005 and la007=tb006 and la008=tb007 WHERE left (ta003,6) = @yyyymm and TB004 in (' 1 ', ' 2') and ta025= ' Y ') b group by Customers ORDER BY Amount Desc--Summary of the top ten + Subtotal select * Into #totaltop10 from (select Custome  Rs,quantities,amount,[cost of Sales],[gross Margin] from #basetop10 UNION ALL select ' Total ' as customers,sum (quantities)


Quantities,sum (Amount) amount,sum ([Cost of Sales]) [Cost of Sales],sum ([gross Margin]) [gross Margin] from #basetop10) c --Top Ten data tables 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--Top ten customer Data graph data select * from #totaltop10 where customers<> ' total '-
The top ten--------------------------------------sales--------------------------------------------Select the product, sum (quantities) quantities, sum (Amount) Amount, sum ([cost ' sales]) as [cost ' 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 in ta001=tb001 and ta002=tb002 inner join Invla on la006=tb005 and la007=tb00 6 and la008=tb007 WHERE Left (ta003,6) = @yyyymm and TB004 in (' 1 ', ' 2 ') and ta025= ' Y ") d Group by Product ORDER by Amo UNT desc DECLARE @subtotalAmount as decimal (10,2) declare @subtotalQuantities as decimal (10,2) declare @subtotalCost as D Ecimal (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-Summary Subtotal Total product totals, other select * Into #totalProducttop10 from (select Product,quantities,amount,[cost of sales],[ Gross Margin] From #ProductTop10 UNION ALL select ' SubTotal ' as product,sum (quantities) quantities,sum (Amount) amount,sum ([Cost of Sales]) [Cost of Sales],sum ([gross Margin]) [grossMargin] from #ProductTop10 UNION ALL SELECT ' 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 the ACRTB left JOIN Acrta on ta001=tb001 and ta002=tb002 inner JOIN Invla in la006=tb005 and la007=tb006 and la008=tb007 WHERE left (ta003,6 = @yyyymm and TB004 in (' 1 ', ' 2 ") and ta025= ' Y ' UNION ALL SELECT ' total ' as Product, sum (CONVERT (Decimal (15,2), TB022) Qu Antities,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=tb002 inner JOIN Invla on LA
006=tb005 and la007=tb006 and la008=tb007 WHERE Left (ta003,6) = @yyyymm and TB004 in (' 1 ', ' 2 ') and ta025= ' Y ') C--Ten Product summary tables 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--Ten product sales revenue accounted for more than select top products, convert (decimal (10,2), amount/@totalAmount *100) as [sales%] from # TOTALPRODUCTTOP10--The top ten product margins are more than Select, Convert (Decimal (10,2), [Gross margin]/@totalGrossMargin *100) as [GM%  ] From #totalProducttop10 drop table #base drop table #basetotal drop table #totaltop10 drop table #basetop10 drop table

 #ProductTop10 drop table #totalProducttop10 End

Figure: