[易飞]财务表之销售分析

原创 2012年03月31日 08:36:22

存储过程:

USE [ZM]
GO
/****** Object:  StoredProcedure [dbo].[UP_SalesAnalyis]    Script Date: 03/31/2012 08:27:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================   
-- Author: <David Gang>   
-- Create date: <2012-03-30>    
-- Description: <销售分析按区域客户产品>   
-- ============================================= 

ALTER Procedure [dbo].[UP_SalesAnalyis]
as
begin

declare @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=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
 					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 * into #basetotal from (
select Customers,Quantities,Amount,[Cost of Sales],[Gross Margin] from #base
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 #base
)a


declare @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
--销售构成
select
Customers,
convert(decimal(10,2),Amount/@totalAmount*100) as [Sales%]
from #basetotal
where Customers<>'total'
--利润构成
select
Customers,
convert(decimal(10,2),[Gross Margin]/@totalGrossMargin*100) as [GM%] 
from #basetotal
where 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=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,6)=@yyyymm  AND TB004 in ('1','2')  and TA025='Y'
		) b
group by Customers
order by Amount desc

--汇总前十+小计
select * into #totaltop10 from (
select Customers,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


--前十大数据表
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 #totaltop10
where 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=TB002
					inner JOIN INVLA ON LA006=TB005 AND LA007=TB006 AND LA008=TB007
		WHERE left(TA003,6)=@yyyymm  AND TB004 in ('1','2')  and TA025='Y'
		) d
group by Product
order by Amount desc

declare @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 #ProductTop10
union all
select 'SubTotal' as Product,sum(Quantities) Quantities,sum(Amount) Amount,sum([Cost of Sales]) [Cost of Sales],sum([Gross Margin]) [Gross Margin] 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  ACRTB left JOIN ACRTA ON TA001=TB001 AND TA002=TB002
			inner JOIN INVLA ON 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)) 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=TB002
			inner JOIN INVLA ON LA006=TB005 AND LA007=TB006 AND LA008=TB007
WHERE 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 #totalProducttop10

drop table #base
drop table #basetotal
drop table #totaltop10
drop table #basetop10
drop table  #ProductTop10 
drop table #totalProducttop10
end

图:

版权声明:本文为博主原创文章,未经博主允许不得转载。 举报

相关文章推荐

[易飞]财务表之销售分析

存储过程: USE [ZM] GO /****** Object: StoredProcedure [dbo].[UP_SalesAnalyis] Script Date: 03/31/20...

[易飞]销售成本分析

抓已开票的销货单和退货单 关联INVLA抓取相关的数据   /* *用途:统计销售实际 已开票抓INVLA档抓成本 *作者:龚德辉 *日期:2010-05--23 */ CREATE...

精选:深入理解 Docker 内部原理及网络配置

网络绝对是任何系统的核心,对于容器而言也是如此。Docker 作为目前最火的轻量级容器技术,有很多令人称道的功能,如 Docker 的镜像管理。然而,Docker的网络一直以来都比较薄弱,所以我们有必要深入了解Docker的网络知识,以满足更高的网络需求。

[易飞]财务要求和系统一致 不管是否有销售发票

那更简单了 /* *用途:统计销售实际 已开票抓INVLA档抓成本 *作者:龚德辉 *日期:2010-05--23 */ CREATE Procedure UP_SALRealyCos...

Web报表系统葡萄城报表:财务报表分析

财务报表分析是以企业基本活动为对象、以财务报表为主要信息来源、以分析和综合为主要方法的系统认识企业的过程,其目的是了解过去、评价现在和预测未来,以帮助报表使用人改善决策。但随着科技的发展,用户逐渐开始...

采畅云服务——销售易

您好! 您是否还在为销售人员业绩不佳而烦恼? 您是否还在为难以时刻掌握销售动态而冥思苦想? 您是否还在为将收集来的一张张名片导入手机而消耗大量时间? 您是否还在为统计每周、每月、每季度的销售数...

销售报表

销售报表
  • Lg632
  • Lg632
  • 2015-12-09 15:32
  • 162

销售数据分析模型

销售数据分析的重要性已无需赘言,只有通过对销售数据的准确分析我们才有可能真正找准数据变动(增长或下滑)的根本原因,营销专家刘杰称之为“动因”。找准了“动因”也就发现了真正的问题所在,解决问题、发现新的...

销售行业ERP数据统计分析都有哪些维度?

场景描述 当前的企业信息化建设主要包括ERP系统、OA系统等。企业希望实现信息系统数据的整合,对企业资源进行分析汇总,方便对企业相关数据的掌控从而便于对业务流程进行及时调整监控。但是由于系统间数据的组...

[易飞]主营业务分析图

上图 SQL: /****** Object: StoredProcedure [dbo].[UP_MainBusinessChart] Script Date: 04/19/2012...

[易飞]主营业务分析

SQL代码: USE [ZE] GO /****** Object: StoredProcedure [dbo].[UP_MainBusiness] Script Date: 04/11...
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)