《案例精解企业级网络构建》赠书活动开始啦！　最新活动：“Web安全大家谈”有奖征文

使用动态SQL语句实现Sbo的客户物料组销售二维分析

2008-06-23 16:50:15

朋友希望我帮他完成在Sbo中实现按照不同的物料组和不同的客户之间的二维分析。

应该说实现方式还是多种的，最简单的实现方式就是使用动态化的存储过程的方式。研究了一下，实现的存储过程如下：

alter procedure up_TTLSaleInfo4CardAndItemGroup
@sd smalldatetime, @ed smalldatetime
with Encryption
as
begin
declare @sql varchar(8000)

select ItmsGrpCod, ItmsGrpNam, CardCode, sum(qty) qty, sum(amt) amt
INTO #tmp
FROM
  (
  select c.ItmsGrpCod, d.ItmsGrpNam, b.CardCode, sum(Quantity) qty, sum(LineTotal) amt
   FROM INV1 a
   inner join OINV b on a.docEntry=b.docEntry
   inner join OITM c on a.ItemCode=c.ItemCode
   inner join OITB d on c.ItmsGrpCod=d.ItmsGrpCod
   WHERE b.docDate between IsNull(@sd, '2006-1-1') and IsNull(@ed, getdate())
   group by c.ItmsGrpCod, b.CardCode, d.ItmsGrpNam
  union all
  select c.ItmsGrpCod, d.ItmsGrpNam, b.CardCode, sum(Quantity) qty, sum(LineTotal) amt
   FROM RIN1 a
   inner join ORIN b on a.docEntry=b.docEntry
   inner join OITM c on a.ItemCode=c.ItemCode
   inner join OITB d on c.ItmsGrpCod=d.ItmsGrpCod
   WHERE b.docDate between IsNull(@sd, '2006-1-1') and IsNull(@ed, getdate())
   group by c.ItmsGrpCod, b.CardCode, d.ItmsGrpNam
  ) t
group by ItmsGrpCod, CardCode, ItmsGrpNam

select @sql=isnull(@sql,'')+',['+cast(ItmsGrpNam as nvarchar(10))+'(数量)]=sum(case ItmsGrpNam when '''+ ItmsGrpNam + ''' then qty else 0 end ),['+cast(ItmsGrpNam as nvarchar(10))+'(金额)]=sum(case ItmsGrpNam when '''+ ItmsGrpNam + ''' then amt else 0 end )'
from #tmp group by ItmsGrpNam

select @sql = 'select a.CardCode 客户代码, b.CardName 客户名称' + @sql + ', sum(qty) 合计数量, sum(amt) 合计金额 from #tmp a inner join OCRD b on a.CardCode=b.CardCode group by a.CardCode, b.CardName'
-- print @sql
exec ( @sql )
end

执行此存储过程的SQL Server语句如下：exec up_TTLSaleInfo4CardAndItemGroup '2008-6-1', '2008-6-2'

执行结果如下图所示，显然达到了预期目的。稍微做一下改变，就可以使用在Sbo的查询接口中。

本文出自 “富盛软件工作室” 博客，请务必保留此出处http://foresun.blog.51cto.com/221037/83639

上一篇实现Sbo系统与用户菜单自定义　　下一篇富盛软件优惠酬宾

类别：ERP&SAP开发 ┆ 技术圈() ┆ 阅读() ┆ 评论() ┆推送到技术圈 ┆返回首页

相关文章

文章评论

昵称：
验证码：		点击图片可刷新验证码　　博客过2级，无需填写验证码
内容：