获取某个时间段内某仓库的某个编号的产品入库情况汇总

  declare @storeid nvarchar(2)
  declare @prdtid nvarchar(10)
  declare @start nvarchar(20)
  declare @end nvarchar(20)
  declare @inno nvarchar(20)
  declare @strSql nvarchar(4000)
  set @storeid='01'
  set @prdtid='0230157'
  set @start='2012-03-15'
  set @end='2012-03-24'
  declare innoCursor cursor for
 select siid from [szjdw_log].[dbo].[mf_prdtin] where  createdt>@start and createdt<@end and storeid=@storeid
  open innoCursor
  fetch innoCursor into @inno
  while @@FETCH_STATUS=0
  begin
 if @strSql is null
 begin
  set @strSql=N'select * from mf_prdtin_de where siid='''+ltrim(RTRIM(@inno))+''''
 end
 else
 begin
  set @strSql=@strSql+' union '+'select * from mf_prdtin_de where siid='''+ltrim(RTRIM(@inno))+''''
 end
  fetch next from innoCursor into @inno
  end
  close innoCursor
  deallocate innoCursor
  --select @strSql
  IF OBJECT_ID('Tempdb.dbo.#S') Is Not Null
  begin
 print 'Exists Table Tempdb.dbo.#S'
 drop table #S
  end
  Else
  begin
 print 'Not Exists Table Tempdb.dbo.#S'
 create table #S(
  [siid] [char](10) NOT NULL,
  [prdtid] [char](7) NOT NULL,
  [num] [float] NULL,
  [price] [float] NULL,
  [seq] [int] NULL
 )
  end
  delete from #S
  insert into #S exec(@strSql)
  select * from #S
  go
 

 

 

该仓库对应的该编号产品的出库在这个时间段的出库汇总是：

  declare @storeid nvarchar(2)
  declare @prdtid nvarchar(10)
  declare @start nvarchar(20)
  declare @end nvarchar(20)
  declare @outno nvarchar(20)
  declare @strSql nvarchar(4000)
  set @storeid='01'
  set @prdtid='0230157'
  --set @start='2012-03-15'
  set @start=CONVERT(varchar(20),'2012-03-21 17:06:12',109)
  set @end=CONVERT(varchar(20),'2012-03-24')
  --select siid from [szjdw_log].[dbo].[mf_prdtin] where  createdt>CONVERT(datetime,@start) and createdt< CONVERT(datetime,@end) and storeid=@storeid
  declare outnoCursor cursor for
 select soid from [szjdw_log].[dbo].[mf_prdtout] where  createdt>CONVERT(datetime,@start) and createdt< CONVERT(datetime,@end) and storeid=@storeid
  open outnoCursor
  fetch outnoCursor into @outno
  while @@FETCH_STATUS=0
  begin
 if @strSql is null
 begin
  set @strSql=N'select * from mf_prdtout_de where soid='''+ltrim(RTRIM(@outno))+''''
 end
 else
 begin
  set @strSql=@strSql+' union '+'select * from mf_prdtout_de where soid='''+ltrim(RTRIM(@outno))+''''
 end
  fetch next from outnoCursor into @outno
  end
  close outnoCursor
  deallocate outnoCursor
  --select @strSql
  IF OBJECT_ID('Tempdb.dbo.#O') Is Not Null
  begin
 print 'Exists Table Tempdb.dbo.#S'
 drop table #O
  end
  Else
  begin
 print 'Not Exists Table Tempdb.dbo.#S'
 create table #O(
  [soid] [char](12) NOT NULL,
  [prdtid] [char](7) NOT NULL,
  [num] [float] NULL,
  [price] [float] NULL,
  [seq] [int] NULL
 )
  end
  delete from #O
  --select @strSql
  insert into #O exec(@strSql)
  select * from #O
  go