朋友希望我帮他完成在Sbo中实现按照不同的物料组和不同的客户之间的二维分析。
应该说实现方式还是多种的，最简单的实现方式就是使用动态化的存储过程的方式。研究了一下，实现的存储过程如下：
alter procedure up_TTLSaleInfo4CardAndItemGroup
@sd smalldatetime, @ed smalldatetime
with Encryption
as
begin
declare @sql varchar(8000)
@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
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
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
-- print @sql
exec ( @sql )
end
执行此存储过程的SQL Server语句如下：
exec up_TTLSaleInfo4CardAndItemGroup '2008-6-1', '2008-6-2'
执行结果如下图所示，显然达到了预期目的。稍微做一下改变，就可以使用在Sbo的查询接口中。
本文转自foresun 51CTO博客，原文链接：http://blog.51cto.com/foresun/83639，如需转载请自行联系原作者
