自己写的,感觉有些地方处理的不是很好,计算量有点大
--说明:返回两个时间之间库存报表 包括每天出入库,期初,总入,总出,结存,及各总金额
--作者:XXX
--时间:2009-04-10CREATE PROCEDURE [DBO].[KC_MgMonthRp]
@DATA1 DATETIME,--开始时间 软件界面选择时间
@DATA2 DATETIME,--结束时间 这个时间应该是 软件界面选择时间
@SQLck VARCHAR(8000),--仓库语句 类似-1,1,2
@SQLspeciesno VARCHAR(8000), --物料类别 and ( speciesno like ..... ) 或者为空
@opid int
AS
begin transaction
declare @time1 datetime,
@time2 datetime,
@sql varchar(8000),
@a varchar(50)
set @time1=@DATA1
set @time2=@DATA2
if @@rowcount>1 goto on_error
--计算结束日期 包括结束日期 前的所有单据,单据插入到临时表#T中,最后字段 TYPE A代表入库 B代表出库
-- 2009-3-1 2009-3-23 那么应该是 @TIME<'2009-3-24'
--
set @sql =' select * into #t from ' --入库单
+' (select PRODUCTID,B.PROVIDERID,convert(varchar(8),operatedate,112) as date,sum(amount) as amount,sum(amount*price) as money,''A'' AS TYPE '+char(13)+char(10)
+' from kc_billjoinlisttab a join kc_billjoinmaintab b on a.billdocument_id=b.billdocument_id '+char(13)+char(10)
+ ' where b.validity=1 and bcptag=1 and b.auditor <>0 and b.affirmer <>0 and operatedate <'''+convert(varchar(50),@time2+1,120) +''' '+char(13)+char(10)
+ ' and productid in (select productid from p_producttab where 1=1 '+@SQLspeciesno+' ) and storehouseid in ('+@SQLck+') '+char(13)+char(10)
+' group by PRODUCTID,B.PROVIDERID,convert(varchar(8),operatedate,112) ) a '
+' union all ( ' --连接出库单
+' select PRODUCTID,PROVIDERID,date,sum(amount) as amount,sum(amount*price) as money,''B'' AS TYPE '+char(13)+char(10)
+' from ( select a.productid,dbo.GetPrivoderId(productid,batch_id) as PROVIDERID,convert(varchar(8),operatedate,112) as date, '+char(13)+char(10)
+ ' case billtype when ''A'' then -amount else amount end amount,price from kc_billoutlisttab a join kc_billoutmaintab b on a.billdocument_id=b.billdocument_id '+char(13)+char(10)
+' where b.validity=1 and b.auditor <>0 and b.affirmer <>0 and operatedate <'''+convert(varchar(50),@time2+1,120) +''' '
+ ' and productid in (select productid from p_producttab where 1=1 '+@SQLspeciesno+' ) and storehouseid in ('+@SQLck+') '+char(13)+char(10)
+' )a '
+ ' group by PRODUCTID,providerid,date ) ' +char(13)+char(10)
if @@rowcount>1 goto on_error--print @sql
--开始SQL查询语句
delete from tmp_MgMonthRp
where opid =@opidset @sql=@sql+' insert into tmp_MgMonthRp (starttime,endtime,appellation,customer,unitprice,startamount,startmoney '+char(13)+char(10)declare @i int,
@time3 datetime
set @time3=@time1
set @i=0while @time3 <=@time2
begin
set @i=@i+1
set @sql =@sql+ ',R'+convert(varchar(2),@i)+',C'+convert(varchar(2),@i)
set @time3=@time3+1
endset @sql =@sql +char(13)+char(10)+',inamount,inmoney,outamount,outmoney,sumamount,summoney,opid )'
set @sql=@sql+' select *,'+ convert(varchar(5),@opid)+'from (select '''+convert(varchar(50),@time1,120)+''' as starttime,'''+convert(varchar(50),@time2,120)+'''as endtime,dbo.appellation(productid) 物料名称,dbo.GetPrivoderNameById(providerid) as providerid, '+'case when sum(case when (type= ''A'' ) THEN AMOUNT ELSE 0 END)=0 then 0 else sum(case when (type= ''A'' ) THEN money ELSE 0 END)/sum(case when (type= ''A'' ) THEN AMOUNT ELSE 0 END) end [单价(元)],sum(case when date < '''+convert(varchar(50),@time1,120)+''''+ 'then amount else 0 end ) as [上月结存], '
--+'case when sum(amount)=0 then 0 else sum(money)/sum(amount) end [单价(元)],sum(case when date < '''+convert(varchar(50),@time1,120)+''''+ 'then amount else 0 end ) as [上月结存], '
+ 'sum(case when date < '''+convert(varchar(50),@time1,120)+''''+ 'then money else 0 end ) as [ 金额(元)] '
--sum(case when type=''A'' then amount else 0 end) --计算单价的地方速度比较慢
--sum(case when (type= ''A'' ) THEN money ELSE 0 END)
--SQL语句拼接,动态生成 月份
while @time1 <=@time2
begin
set @a=convert(varchar(8),@time1,112)
set @sql=@sql+',sum(case when date='''+@a+''' and type=''A'' then amount else 0 end) ['+right(@a,4)+'入] '+char(13)+char(10)
set @sql=@sql+',-sum(case when date='''+@a+''' and type=''B'' then amount else 0 end) ['+right(@a,4)+'出] '+char(13)+char(10)
set @time1=@time1+1
end
set @sql=@sql
+',sum(case when (type= ''A'' and date >='''+convert(varchar(50),@data1,120)+''' and date <'''+convert(varchar(50),@time2+1,120)+''') THEN amount ELSE 0 END) [入库数量],'+char(13)+char(10)
+'sum(case when (type= ''A'' and date >='''+convert(varchar(50),@data1,120)+''' and date <'''+convert(varchar(50),@time2+1,120)+''') THEN money ELSE 0 END) [入库总金额] , '+char(13)+char(10)
+'-sum(case when (type=''B'' and date >='''+convert(varchar(50),@data1,120)+''' and date <'''+convert(varchar(50),@time2+1,120)+''') THEN amount ELSE 0 END) [出库数量],'+char(13)+char(10)
+'-sum(case when (type=''B'' and date >='''+convert(varchar(50),@data1,120)+''' and date <'''+convert(varchar(50),@time2+1,120)+''') THEN money ELSE 0 END) [出库总金额],'+char(13)+char(10)+'sum(amount) as [结存数量] ,sum(money) as [总金额] from #t group by productid,providerid)a '+char(13)+char(10)
+' where [上月结存]<>0 or [入库数量] <>0 or [出库数量] <>0 ' --去掉期初为O,入库为O 出库为O数据
--print @sql
if @@rowcount>1 goto on_error
exec (@sql)
commit
return 1on_error:
rollback
return -1
GO
--说明:返回两个时间之间库存报表 包括每天出入库,期初,总入,总出,结存,及各总金额
--作者:XXX
--时间:2009-04-10CREATE PROCEDURE [DBO].[KC_MgMonthRp]
@DATA1 DATETIME,--开始时间 软件界面选择时间
@DATA2 DATETIME,--结束时间 这个时间应该是 软件界面选择时间
@SQLck VARCHAR(8000),--仓库语句 类似-1,1,2
@SQLspeciesno VARCHAR(8000), --物料类别 and ( speciesno like ..... ) 或者为空
@opid int
AS
begin transaction
declare @time1 datetime,
@time2 datetime,
@sql varchar(8000),
@a varchar(50)
set @time1=@DATA1
set @time2=@DATA2
if @@rowcount>1 goto on_error
--计算结束日期 包括结束日期 前的所有单据,单据插入到临时表#T中,最后字段 TYPE A代表入库 B代表出库
-- 2009-3-1 2009-3-23 那么应该是 @TIME<'2009-3-24'
--
set @sql =' select * into #t from ' --入库单
+' (select PRODUCTID,B.PROVIDERID,convert(varchar(8),operatedate,112) as date,sum(amount) as amount,sum(amount*price) as money,''A'' AS TYPE '+char(13)+char(10)
+' from kc_billjoinlisttab a join kc_billjoinmaintab b on a.billdocument_id=b.billdocument_id '+char(13)+char(10)
+ ' where b.validity=1 and bcptag=1 and b.auditor <>0 and b.affirmer <>0 and operatedate <'''+convert(varchar(50),@time2+1,120) +''' '+char(13)+char(10)
+ ' and productid in (select productid from p_producttab where 1=1 '+@SQLspeciesno+' ) and storehouseid in ('+@SQLck+') '+char(13)+char(10)
+' group by PRODUCTID,B.PROVIDERID,convert(varchar(8),operatedate,112) ) a '
+' union all ( ' --连接出库单
+' select PRODUCTID,PROVIDERID,date,sum(amount) as amount,sum(amount*price) as money,''B'' AS TYPE '+char(13)+char(10)
+' from ( select a.productid,dbo.GetPrivoderId(productid,batch_id) as PROVIDERID,convert(varchar(8),operatedate,112) as date, '+char(13)+char(10)
+ ' case billtype when ''A'' then -amount else amount end amount,price from kc_billoutlisttab a join kc_billoutmaintab b on a.billdocument_id=b.billdocument_id '+char(13)+char(10)
+' where b.validity=1 and b.auditor <>0 and b.affirmer <>0 and operatedate <'''+convert(varchar(50),@time2+1,120) +''' '
+ ' and productid in (select productid from p_producttab where 1=1 '+@SQLspeciesno+' ) and storehouseid in ('+@SQLck+') '+char(13)+char(10)
+' )a '
+ ' group by PRODUCTID,providerid,date ) ' +char(13)+char(10)
if @@rowcount>1 goto on_error--print @sql
--开始SQL查询语句
delete from tmp_MgMonthRp
where opid =@opidset @sql=@sql+' insert into tmp_MgMonthRp (starttime,endtime,appellation,customer,unitprice,startamount,startmoney '+char(13)+char(10)declare @i int,
@time3 datetime
set @time3=@time1
set @i=0while @time3 <=@time2
begin
set @i=@i+1
set @sql =@sql+ ',R'+convert(varchar(2),@i)+',C'+convert(varchar(2),@i)
set @time3=@time3+1
endset @sql =@sql +char(13)+char(10)+',inamount,inmoney,outamount,outmoney,sumamount,summoney,opid )'
set @sql=@sql+' select *,'+ convert(varchar(5),@opid)+'from (select '''+convert(varchar(50),@time1,120)+''' as starttime,'''+convert(varchar(50),@time2,120)+'''as endtime,dbo.appellation(productid) 物料名称,dbo.GetPrivoderNameById(providerid) as providerid, '+'case when sum(case when (type= ''A'' ) THEN AMOUNT ELSE 0 END)=0 then 0 else sum(case when (type= ''A'' ) THEN money ELSE 0 END)/sum(case when (type= ''A'' ) THEN AMOUNT ELSE 0 END) end [单价(元)],sum(case when date < '''+convert(varchar(50),@time1,120)+''''+ 'then amount else 0 end ) as [上月结存], '
--+'case when sum(amount)=0 then 0 else sum(money)/sum(amount) end [单价(元)],sum(case when date < '''+convert(varchar(50),@time1,120)+''''+ 'then amount else 0 end ) as [上月结存], '
+ 'sum(case when date < '''+convert(varchar(50),@time1,120)+''''+ 'then money else 0 end ) as [ 金额(元)] '
--sum(case when type=''A'' then amount else 0 end) --计算单价的地方速度比较慢
--sum(case when (type= ''A'' ) THEN money ELSE 0 END)
--SQL语句拼接,动态生成 月份
while @time1 <=@time2
begin
set @a=convert(varchar(8),@time1,112)
set @sql=@sql+',sum(case when date='''+@a+''' and type=''A'' then amount else 0 end) ['+right(@a,4)+'入] '+char(13)+char(10)
set @sql=@sql+',-sum(case when date='''+@a+''' and type=''B'' then amount else 0 end) ['+right(@a,4)+'出] '+char(13)+char(10)
set @time1=@time1+1
end
set @sql=@sql
+',sum(case when (type= ''A'' and date >='''+convert(varchar(50),@data1,120)+''' and date <'''+convert(varchar(50),@time2+1,120)+''') THEN amount ELSE 0 END) [入库数量],'+char(13)+char(10)
+'sum(case when (type= ''A'' and date >='''+convert(varchar(50),@data1,120)+''' and date <'''+convert(varchar(50),@time2+1,120)+''') THEN money ELSE 0 END) [入库总金额] , '+char(13)+char(10)
+'-sum(case when (type=''B'' and date >='''+convert(varchar(50),@data1,120)+''' and date <'''+convert(varchar(50),@time2+1,120)+''') THEN amount ELSE 0 END) [出库数量],'+char(13)+char(10)
+'-sum(case when (type=''B'' and date >='''+convert(varchar(50),@data1,120)+''' and date <'''+convert(varchar(50),@time2+1,120)+''') THEN money ELSE 0 END) [出库总金额],'+char(13)+char(10)+'sum(amount) as [结存数量] ,sum(money) as [总金额] from #t group by productid,providerid)a '+char(13)+char(10)
+' where [上月结存]<>0 or [入库数量] <>0 or [出库数量] <>0 ' --去掉期初为O,入库为O 出库为O数据
--print @sql
if @@rowcount>1 goto on_error
exec (@sql)
commit
return 1on_error:
rollback
return -1
GO
StringReplace(sss,'''','''''',[rfReplaceAll])
begin
set @a=convert(varchar(8),@time1,112)
set @sql=@sql+',sum(case when date='''+@a+''' and type=''A'' then amount else 0 end) ['+right(@a,4)+'入] '+char(13)+char(10)
set @sql=@sql+',-sum(case when date='''+@a+''' and type=''B'' then amount else 0 end) ['+right(@a,4)+'出] '+char(13)+char(10)
set @time1=@time1+1
end
select @a='偶达到8000字节了',@b='偶也8000字节了'
exec(@a+@b) --偶们这样也行了的说..