create procedure YouName
@StroageID int,
@BeginDate date,
@EndDate date
as
begin
declage @fInQty int
select Top 1 @fInQty=isnull(fInQty,0) from tStroageIO where fID=@StroageID and fDate>@BeginDate and fDate<@EndDate group by fDateselect fID 产品代码,@fInQty 期初 ,sum(fInQty) 入库,sum(fOutQty) 出库,@fInQty+sum(fInQty)-sum(fOutQty) 结存 from tStroageIO where fID=@StroageID and fDate>@BeginDate and fDate<@EndDate
end
@StroageID int,
@BeginDate date,
@EndDate date
as
begin
declage @fInQty int
select Top 1 @fInQty=isnull(fInQty,0) from tStroageIO where fID=@StroageID and fDate>@BeginDate and fDate<@EndDate group by fDateselect fID 产品代码,@fInQty 期初 ,sum(fInQty) 入库,sum(fOutQty) 出库,@fInQty+sum(fInQty)-sum(fOutQty) 结存 from tStroageIO where fID=@StroageID and fDate>@BeginDate and fDate<@EndDate
end
@StroageID int,
@BeginDate date,
@EndDate date
as
begin
declage @fInQty int
---结存数等于时间段内第一比的产品期初数
---我是根据你写的“产品期初数在fInQty中体现”
select Top 1 @fInQty=isnull(fInQty,0) from tStroageIO where fID=@StroageID and fDate>@BeginDate and fDate<@EndDate group by fDate
---算出结果
select fID 产品代码,@fInQty 期初 ,sum(fInQty) 入库,sum(fOutQty) 出库,@fInQty+sum(fInQty)-sum(fOutQty) 结存 from tStroageIO where fID=@StroageID and fDate>@BeginDate and fDate<@EndDate
end
--OK???
@StroageID int,
@BeginDate date,
@EndDate date
as
begin
declage @fInQty int
select @fInQty=sum(isnull(fInQty,0)-isnull(foutQty,0)) from tStroageIO where fID=@StroageID and fDate<@BeginDate select @StroageID 产品代码,@fInQty 期初 ,sum(fInQty) 入库,sum(fOutQty) 出库,@fInQty+sum(fInQty)-sum(fOutQty) 结存 from tStroageIO where fID=@StroageID and fDate>=@BeginDate and fDate<@EndDateend
服务器: 消息 8120,级别 16,状态 1,过程 pTotalStorageIO,行 63
列 'tStorageIO.fInQty' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
2.期初库存怎么得到,你没说,从表中也看不出。
建议这样见表:
ID varchar(30) 产品代码
Quantity int 数量
Out bit 出库?入库
Time datetime 发生时间
StoageID bigint 仓库编码