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

4500人阅读 评论(0)

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

0
0

* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
个人资料
• 访问：2342390次
• 积分：34663
• 等级：
• 排名：第144名
• 原创：897篇
• 转载：491篇
• 译文：6篇
• 评论：272条
联系方式
• ERP群:68148773
• C#群: 254167627
• SQL群:446802573
• 会计群:426046886
• 安卓群:604859607
• ext群: 556270614
• Tea群: 126748158
• 德仔工作室
• 德仔淘宝店
博客专栏
 MSCHART 文章：11篇 阅读：26862
 ERP管理 文章：27篇 阅读：48019
 DevExpress 文章：18篇 阅读：73134
 Windows Server 2012 活动目录 文章：8篇 阅读：21948
 Sql Server 文章：125篇 阅读：169057
 Ext.Net 文章：43篇 阅读：138974
 易飞 文章：78篇 阅读：183414
最新评论
文章存档