select sum(case when InStockDate is not null then Number else 0 end)-sum(case when OutStockDate is not null then Number else 0 end) as 库存 from yourtable whereMaterialCode=某种物资编号 and StorageID=某个仓库代号 and InStockDate<某个时间
and OutStockDate<某个时间
and OutStockDate<某个时间
whereMaterialCode=某种物资编号 and InStockDate<某个时间
and OutStockDate<某个时间
group by StorgeID
whereMaterialCode=某种物资编号 and InStockDate<某个时间
and OutStockDate<某个时间
group by StorgeID
and WZ_InStockBill.InStockDate<某个时间 and WZ_OutStockBill.OutStockDate<某个时间
还不行!无法解决入库表中有而出库没有!或出库有而入库没有情况!
tryselect WZ_InStockBill.MaterialCode ,sum(isnull(WZ_InStockBill.Number,0))-sum(isnull(WZ_OutStockBill.Number,0)) as 库存 from WZ_InStockBill left join WZ_OutStockBill on WZ_OutStockBill.MaterialCode=WZ_InStockBill.MaterialCode
and WZ_OutStockBill.StorageID=WZ_InStockBill.StorageID where WZ_InStockBill.StorageID=某个仓库代号 and WZ_InStockBill.InStockDate<某个时间 and WZ_OutStockBill.OutStockDate<某个时间
union
select WZ_OutStockBill.MaterialCode ,sum(isnull(WZ_InStockBill.Number,0))-sum(isnull(WZ_OutStockBill.Number,0)) as 库存 from WZ_OutStockBill left join WZ_InStockBill on WZ_OutStockBill.MaterialCode=WZ_InStockBill.MaterialCode
and WZ_OutStockBill.StorageID=WZ_InStockBill.StorageID where WZ_InStockBill.StorageID=某个仓库代号 and WZ_InStockBill.InStockDate<某个时间 and WZ_OutStockBill.OutStockDate<某个时间