统计主要供应商供货数量和金额的比例二

最新推荐文章于 2022-05-16 20:40:21 发布

转载最新推荐文章于 2022-05-16 20:40:21 发布 · 2.1k 阅读

文章标签：

#table #insert #delete #报表 #存储 #2010

金蝶K3二次开发专栏收录该内容

22 篇文章

订阅专栏

本文介绍了一种使用金蝶系统的存储过程来分析特定供应商在一年内的每周供货数量及金额的方法，并将其比例化处理，以便更好地理解供应商的表现。

-- 如何通过金蝶分析一年内各周（主要）各供应商供货数量和金额占（主要）供应商总数量和总金额的比例。

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[Fls_StockIN]
@flag int
AS
BEGIN
set datefirst 5

select year(a.fdate) 年,datepart(week,a.fdate) 周,c.fshortname 供应商,sum(fqty) 数量,sum(famount) 金额 into #RD
from icstockbill a,icstockbillentry b,t_supplier c
where a.finterid=b.finterid and a.fsupplyid=c.fitemid
and c.fnumber in ('A供应商代码','B供应商代码','C供应商代码',)
and a.ftrantype=1 and a.fdate>=dateadd(yy,-1,getdate())
group by year(a.fdate),datepart(week,a.fdate),c.fshortname
order by year(a.fdate),datepart(week,a.fdate)
--取出年，周，供应商名字，数量，金额，插入临行表 #RD
-- create table FLS_STOCK_IN_QTY(
-- 年 int,
-- 周 int,
-- A供应商 money,
-- B供应商 money,
-- C供应商 money,
--汇总 money
-- )
-- create table FLS_STOCK_IN_AMOUNT(
-- 年 int,
-- 周 int,
-- A供应商 money,
-- B供应商 money,
-- C供应商 money
--汇总 money
-- )
--这两段主要是创建了存储数量和金额的实体表，

set datefirst 7
DELETE FLS_STOCK_IN_QTY
DELETE FLS_STOCK_IN_AMOUNT

declare @oYear int
declare @oWeek int
declare @YEAR INT
declare @WEEK int
set @oYear=0
set @oWeek=0
set @Year=0
set @Week=0
declare @SUP varchar(20)
declare @QTY money
declare @AMOUNT MONEY
declare TR cursor for select 年,周,供应商,数量,金额 from #RD order by 年,周,供应商
open TR
fetch next from TR into @YEAR,@WEEK,@SUP,@QTY,@AMOUNT
WHILE @@FETCH_STATUS = 0
BEGIN
  declare @StrSql varchar(2000)
  if @oYear<>@year or @oWeek<>@week
  begin
   set @oYear=@Year
   set @oWeek=@Week
   insert into FLS_STOCK_IN_QTY values(@year,@week,0,0,0,0)
   insert into FLS_STOCK_IN_Amount values(@year,@week,0,0,0,0)
  end

   set @StrSql='Update FLS_STOCK_IN_QTY set ['+@SUP+']=['+@SUP+']+'+cast(@QTY as varchar(20)) + ',[汇总]=[汇总]+'+cast(@QTY AS VARCHAR(20))+' where 年='+cast(@Year as varchar(4))+' and 周='+cast(@week as varchar(2))
   exec (@StrSql)
   set @StrSql='Update FLS_STOCK_IN_AMOUNT set ['+@SUP+']=['+@SUP+']+'+cast(@AMOUNT as varchar(20)) + ',[汇总]=[汇总]+'+cast(@Amount AS VARCHAR(20))+' where 年='+cast(@Year as varchar(4))+' and 周='+cast(@week as varchar(2))
   exec (@StrSql)
   fetch next from TR into @YEAR,@WEEK,@SUP,@QTY,@AMOUNT
END
--通过游标，按照年,周,供应商分别取出数量，金额插入到两个实体表中
CLOSE TR
DEALLOCATE TR
DROP TABLE #RD
if @flag=1
  select * from FLS_STOCK_IN_QTY
else
  select * from FLS_STOCK_IN_Amount
END

--上一帖子中查询时间可以自己控制，这个帖子是根据年和周进行统计，复杂了很多，但是出的报表更直观

转自：http://community.kingdee.com/pages/jstu/blog/archive/2010/08/06/475188.aspx