SQL server 有出入库表,计算每天的库存量

时间:2021-03-06 00:27:58
入库表和出库表都包含不同企业,不同商品的出入库记录,存在有的日期没有出入库记录情况,现在要求计算每日仓库的库存费用,所以我想求出仓库每日的库存数量,按不同企业,不同商品分类统计好。
出入库表我只是放了部分数据

入库表结构如下

NianDu QYBM_入库企业编码 FLBM_所属分类编码 SL_入库数量 RKRQ_入库日期
16/17 GX21 01 969.000 2017-04-05 00:00:00.000
16/17 GX03 01 0.000 2017-04-04 00:00:00.000
16/17 GX03 02 0.000 2017-04-04 00:00:00.000
16/17 GX0201 02 180.745 2017-03-31 00:00:00.000
16/17 GX03 01 19.500 2017-03-31 00:00:00.000
16/17 GX03 02 142.150 2017-03-31 00:00:00.000
16/17 GX03 01 120.700 2017-03-30 00:00:00.000
16/17 GX03 02 14.500 2017-03-30 00:00:00.000
16/17 GX0201 01 168.950 2017-03-29 00:00:00.000
16/17 GX03 01 295.000 2017-03-29 00:00:00.000
16/17 GX0201 01 189.650 2017-03-28 00:00:00.000
16/17 GX03 01 316.500 2017-03-28 00:00:00.000
16/17 GX0201 01 229.550 2017-03-27 00:00:00.000
16/17 GX03 01 40.000 2017-03-27 00:00:00.000
16/17 GX0201 01 76.750 2017-03-26 00:00:00.000
16/17 GX03 01 176.150 2017-03-26 00:00:00.000
16/17 GX04 01 85.000 2017-03-26 00:00:00.000
16/17 GX05 02 214.950 2017-03-26 00:00:00.000
16/17 GX21 01 690.000 2017-03-26 00:00:00.000
16/17 GX03 01 359.600 2017-03-25 00:00:00.000
16/17 GX03 01 381.550 2017-03-24 00:00:00.000
16/17 GX03 01 156.800 2017-03-23 00:00:00.000
16/17 GX04 01 108.000 2017-03-23 00:00:00.000
16/17 GX05 02 112.500 2017-03-23 00:00:00.000
16/17 GX1002 01 27.350 2017-03-23 00:00:00.000
16/17 GX1002 02 80.500 2017-03-23 00:00:00.000
16/17 GX05 01 99.550 2017-03-22 00:00:00.000
16/17 GX05 02 142.500 2017-03-22 00:00:00.000
16/17 GX1002 01 260.550 2017-03-22 00:00:00.000

出库表结构如下

NianDu HQBM_货权企业编码 FLBM_所属分类编码 SL_出库数量 CKRQ_出库日期
16/17 GX0401 01 52.000 2017-04-14 00:00:00.000
16/17 GX1002 01 26.000 2017-04-14 00:00:00.000
16/17 GX21 01 54.000 2017-04-14 00:00:00.000
16/17 GX03 01 32.000 2017-04-14 00:00:00.000
16/17 GX21 01 54.000 2017-04-14 00:00:00.000
16/17 GX0401 01 27.000 2017-04-14 00:00:00.000
16/17 GX05 01 -120.000 2017-04-14 00:00:00.000
16/17 GX0401 01 27.000 2017-04-14 00:00:00.000
16/17 GX05 01 26.000 2017-04-14 00:00:00.000
16/17 GX21 01 27.000 2017-04-14 00:00:00.000
16/17 GX0401 01 -27.000 2017-04-14 00:00:00.000
16/17 GX1001 01 45.000 2017-04-14 00:00:00.000
16/17 GX05 01 40.000 2017-04-14 00:00:00.000
16/17 GX05 01 26.000 2017-04-14 00:00:00.000
16/17 GX01 02 11.000 2017-04-13 00:00:00.000
16/17 GX0401 01 1.000 2017-04-13 00:00:00.000
16/17 GX0401 01 27.000 2017-04-13 00:00:00.000
16/17 GX03 01 40.000 2017-04-13 00:00:00.000
16/17 GX0401 01 27.000 2017-04-13 00:00:00.000
16/17 GX0401 01 27.000 2017-04-13 00:00:00.000
16/17 GX04 01 -54.000 2017-04-13 00:00:00.000
16/17 GX0401 01 -27.000 2017-04-13 00:00:00.000
16/17 GX21 01 52.000 2017-04-13 00:00:00.000
16/17 GX0402 01 52.000 2017-04-13 00:00:00.000
16/17 GX03 01 -40.000 2017-04-13 00:00:00.000
16/17 GX0401 01 -27.000 2017-04-13 00:00:00.000
16/17 GX05 01 50.000 2017-04-13 00:00:00.000
16/17 GX03 01 4.950 2017-04-13 00:00:00.000
16/17 GX21 01 54.000 2017-04-13 00:00:00.000
16/17 GX0401 01 27.000 2017-04-13 00:00:00.000
16/17 GX04 01 -46.000 2017-04-13 00:00:00.000
16/17 GX05 01 26.000 2017-04-13 00:00:00.000
16/17 GX1002 01 26.000 2017-04-13 00:00:00.000
16/17 GX21 01 54.000 2017-04-13 00:00:00.000

12 个解决方案

#1


试试这个:
SELECT  a.RKRQ_入库日期 ,
        a.QYBM_入库企业编码 ,
        a.FLBM_所属分类编码 ,
        SUM(a.SL_入库数量) - SUM(b.SL_出库数量) AS 库存数量
FROM    入库表 a
        JOIN 出库表 b ON b.FLBM_所属分类编码 = a.FLBM_所属分类编码
                      AND a.QYBM_入库企业编码 = b.HQBM_货权企业编码
                      AND a.RKRQ_入库日期 = b.CKRQ_出库日期
GROUP BY a.RKRQ_入库日期 ,
        a.QYBM_入库企业编码 ,
        a.FLBM_所属分类编码

#2


楼主是要计算每天的结存数量么?

#3


计算每天结存数量:
;with crk as (select QYBM_入库企业编码 qybm,FLBM_所属分类编码 flbm,SL_入库数量 sl,RKRQ_入库日期 rkrq from 入库表
           union all
              select HQBM_货权企业编码,FLBM_所属分类编码,-SL_出库数量,CKRQ_出库日期 from 出库表),
 crkhz as (select qybm,flbm,SUM(sl) sl,rkrq from t
            group by qybm,flbm,rkrq)
 select qybm,flbm,rkrq,sl=isnull((select SUM(sl) from crkhz a
             where a.qybm=b.qybm and a.flbm=b.flbm ans a.rkrq>=b.rkrq),0)
      from crkhz b

#4


引用 1 楼 sinat_28984567 的回复:
试试这个:
SELECT  a.RKRQ_入库日期 ,
        a.QYBM_入库企业编码 ,
        a.FLBM_所属分类编码 ,
        SUM(a.SL_入库数量) - SUM(b.SL_出库数量) AS 库存数量
FROM    入库表 a
        JOIN 出库表 b ON b.FLBM_所属分类编码 = a.FLBM_所属分类编码
                      AND a.QYBM_入库企业编码 = b.HQBM_货权企业编码
                      AND a.RKRQ_入库日期 = b.CKRQ_出库日期
GROUP BY a.RKRQ_入库日期 ,
        a.QYBM_入库企业编码 ,
        a.FLBM_所属分类编码


没那么简单的,首先,需要的是每一天的库存量,出库入库表的出入库时间并不是一一对应的,有的日期可能有入库数据,但是没有出库数据,也有可能只有出库数据没有入库数据,还可能有的日期出库入库数据都没有,还是谢谢你的关注

#5


引用 3 楼 shinger126 的回复:
计算每天结存数量:
;with crk as (select QYBM_入库企业编码 qybm,FLBM_所属分类编码 flbm,SL_入库数量 sl,RKRQ_入库日期 rkrq from 入库表
           union all
              select HQBM_货权企业编码,FLBM_所属分类编码,-SL_出库数量,CKRQ_出库日期 from 出库表),
 crkhz as (select qybm,flbm,SUM(sl) sl,rkrq from t
            group by qybm,flbm,rkrq)
 select qybm,flbm,rkrq,sl=isnull((select SUM(sl) from crkhz a
             where a.qybm=b.qybm and a.flbm=b.flbm ans a.rkrq>=b.rkrq),0)
      from crkhz b


这样还是少了那些出入库没有记录的日期的库存呢

#6


下面的例子是在指定时间内列出每个产品每天的情况,不管没有出入库记录,所以数据比较多,不知道是不是你想要的

if object_id('tempdb..#t') is not null drop table #t
create table #t(NianDu varchar(10),QYBM_入库企业编码 varchar(10),FLBM_所属分类编码 varchar(10),SL_入库数量 float,RKRQ_入库日期 datetime)
insert into #t(NianDu,QYBM_入库企业编码,FLBM_所属分类编码,SL_入库数量,RKRQ_入库日期)
select '16/17','GX21','01',969.000,'2017-04-05 00:00:00.000' union all 
select '16/17','GX03','01',0.000,'2017-04-04 00:00:00.000' union all 
select '16/17','GX03','02',0.000,'2017-04-04 00:00:00.000' union all 
select '16/17','GX0201','02',180.745,'2017-03-31 00:00:00.000' union all 
select '16/17','GX03','01',19.500,'2017-03-31 00:00:00.000' union all 
select '16/17','GX03','02',142.150,'2017-03-31 00:00:00.000' union all 
select '16/17','GX03','01',120.700,'2017-03-30 00:00:00.000' union all 
select '16/17','GX03','02',14.500,'2017-03-30 00:00:00.000' union all 
select '16/17','GX0201','01',168.950,'2017-03-29 00:00:00.000' union all 
select '16/17','GX03','01',295.000,'2017-03-29 00:00:00.000' union all 
select '16/17','GX0201','01',189.650,'2017-03-28 00:00:00.000' union all 
select '16/17','GX03','01',316.500,'2017-03-28 00:00:00.000' union all 
select '16/17','GX0201','01',229.550,'2017-03-27 00:00:00.000' union all 
select '16/17','GX03','01',40.000,'2017-03-27 00:00:00.000' union all 
select '16/17','GX0201','01',76.750,'2017-03-26 00:00:00.000' union all 
select '16/17','GX03','01',176.150,'2017-03-26 00:00:00.000' union all 
select '16/17','GX04','01',85.000,'2017-03-26 00:00:00.000' union all 
select '16/17','GX05','02',214.950,'2017-03-26 00:00:00.000' union all 
select '16/17','GX21','01',690.000,'2017-03-26 00:00:00.000' union all 
select '16/17','GX03','01',359.600,'2017-03-25 00:00:00.000' union all 
select '16/17','GX03','01',381.550,'2017-03-24 00:00:00.000' union all 
select '16/17','GX03','01',156.800,'2017-03-23 00:00:00.000' union all 
select '16/17','GX04','01',108.000,'2017-03-23 00:00:00.000' union all 
select '16/17','GX05','02',112.500,'2017-03-23 00:00:00.000' union all 
select '16/17','GX1002','01',27.350,'2017-03-23 00:00:00.000' union all 
select '16/17','GX1002','02',80.500,'2017-03-23 00:00:00.000' union all 
select '16/17','GX05','01',99.550,'2017-03-22 00:00:00.000' union all 
select '16/17','GX05','02',142.500,'2017-03-22 00:00:00.000' union all 
select '16/17','GX1002','01',260.550,'2017-03-22 00:00:00.000'

--select * from #t


if object_id('tempdb..#o') is not null drop table #o
create table #o(NianDu varchar(10),HQBM_货权企业编码 varchar(10),FLBM_所属分类编码 varchar(10),SL_出库数量 float,CKRQ_出库日期 datetime)
insert into #o(NianDu,HQBM_货权企业编码,FLBM_所属分类编码,SL_出库数量,CKRQ_出库日期)

select '16/17','GX0401','01',52.000,'2017-04-14 00:00:00.000' union all
select '16/17','GX1002','01',26.000,'2017-04-14 00:00:00.000' union all
select '16/17','GX21','01',54.000,'2017-04-14 00:00:00.000' union all
select '16/17','GX03','01',32.000,'2017-04-14 00:00:00.000' union all
select '16/17','GX21','01',54.000,'2017-04-14 00:00:00.000' union all
select '16/17','GX0401','01',27.000,'2017-04-14 00:00:00.000' union all
select '16/17','GX05','01',-120.000,'2017-04-14 00:00:00.000' union all
select '16/17','GX0401','01',27.000,'2017-04-14 00:00:00.000' union all
select '16/17','GX05','01',26.000,'2017-04-14 00:00:00.000' union all
select '16/17','GX21','01',27.000,'2017-04-14 00:00:00.000' union all
select '16/17','GX0401','01',-27.000,'2017-04-14 00:00:00.000' union all
select '16/17','GX1001','01',45.000,'2017-04-14 00:00:00.000' union all
select '16/17','GX05','01',40.000,'2017-04-14 00:00:00.000' union all
select '16/17','GX05','01',26.000,'2017-04-14 00:00:00.000' union all
select '16/17','GX01','02',11.000,'2017-04-13 00:00:00.000' union all
select '16/17','GX0401','01',1.000,'2017-04-13 00:00:00.000' union all
select '16/17','GX0401','01',27.000,'2017-04-13 00:00:00.000' union all
select '16/17','GX03','01',40.000,'2017-04-13 00:00:00.000' union all
select '16/17','GX0401','01',27.000,'2017-04-13 00:00:00.000' union all
select '16/17','GX0401','01',27.000,'2017-04-13 00:00:00.000' union all
select '16/17','GX04','01',-54.000,'2017-04-13 00:00:00.000' union all
select '16/17','GX0401','01',-27.000,'2017-04-13 00:00:00.000' union all
select '16/17','GX21','01',52.000,'2017-04-13 00:00:00.000' union all
select '16/17','GX0402','01',52.000,'2017-04-13 00:00:00.000' union all
select '16/17','GX03','01',-40.000,'2017-04-13 00:00:00.000' union all
select '16/17','GX0401','01',-27.000,'2017-04-13 00:00:00.000' union all
select '16/17','GX05','01',50.000,'2017-04-13 00:00:00.000' union all
select '16/17','GX03','01',4.950,'2017-04-13 00:00:00.000' union all
select '16/17','GX21','01',54.000,'2017-04-13 00:00:00.000' union all
select '16/17','GX0401','01',27.000,'2017-04-13 00:00:00.000' union all
select '16/17','GX04','01',-46.000,'2017-04-13 00:00:00.000' union all
select '16/17','GX05','01',26.000,'2017-04-13 00:00:00.000' union all
select '16/17','GX1002','01',26.000,'2017-04-13 00:00:00.000' union all
select '16/17','GX21','01',54.000,'2017-04-13 00:00:00.000'
--select * from #o


declare @startDate datetime='2017-03-22',@EndDate datetime='2017-04-30'
select dateadd(d,sv.number,@StartDate) as [Date]
      ,p.企业编码,p.分类编码
      ,ISNULL(i.in_Qty,0) as 入库数量 ,ISNULL(o.out_Qty,0) AS 出库数量,ISNULL(i.in_Qty,0)-ISNULL(o.out_Qty,0) as 余额
from master.dbo.spt_values as sv
inner join (
    select distinct NianDu,QYBM_入库企业编码 as 企业编码,FLBM_所属分类编码 as 分类编码 from #t
) as p on 1=1
outer apply(
    select sum(SL_入库数量) as in_Qty 
    from #t as t
    where datediff(d,@startDate,t.RKRQ_入库日期)<=sv.number 
    and t.NianDu=p.NianDu and t.QYBM_入库企业编码=p.企业编码 and t.FLBM_所属分类编码=p.分类编码
 ) as i
 outer apply(
    select sum(SL_出库数量) as out_Qty 
    from #o as t
    where datediff(d,@startDate,t.CKRQ_出库日期)<=sv.number 
    and t.NianDu=p.NianDu and t.HQBM_货权企业编码=p.企业编码 and t.FLBM_所属分类编码=p.分类编码
 ) as o
where sv.type='P' and sv.number <=datediff(d,@startdate,@EndDate)
order by sv.number,p.企业编码,p.分类编码

#7


因为篇幅(多了不让发)摘抄部分数据

+-------------------------+--------+------+-------------------+-------+-------------------+
| Date                    | 企业编码   | 分类编码 | 入库数量              | 出库数量  | 余额                |
+-------------------------+--------+------+-------------------+-------+-------------------+
| 2017-03-22 00:00:00:000 | GX0201 | 01   | 0                 | 0     | 0                 |
| 2017-03-22 00:00:00:000 | GX0201 | 02   | 0                 | 0     | 0                 |
| 2017-03-22 00:00:00:000 | GX03   | 01   | 0                 | 0     | 0                 |
| 2017-03-22 00:00:00:000 | GX03   | 02   | 0                 | 0     | 0                 |
| 2017-03-22 00:00:00:000 | GX04   | 01   | 0                 | 0     | 0                 |
| 2017-03-22 00:00:00:000 | GX05   | 01   | 99.55             | 0     | 99.55             |
| 2017-03-22 00:00:00:000 | GX05   | 02   | 142.5             | 0     | 142.5             |
| 2017-03-22 00:00:00:000 | GX1002 | 01   | 260.55            | 0     | 260.55            |
| 2017-03-22 00:00:00:000 | GX1002 | 02   | 0                 | 0     | 0                 |
| 2017-03-22 00:00:00:000 | GX21   | 01   | 0                 | 0     | 0                 |
| 2017-03-23 00:00:00:000 | GX0201 | 01   | 0                 | 0     | 0                 |
| 2017-03-23 00:00:00:000 | GX0201 | 02   | 0                 | 0     | 0                 |
| 2017-03-23 00:00:00:000 | GX03   | 01   | 156.8             | 0     | 156.8             |
| 2017-03-23 00:00:00:000 | GX03   | 02   | 0                 | 0     | 0                 |
| 2017-03-23 00:00:00:000 | GX04   | 01   | 108               | 0     | 108               |
| 2017-03-23 00:00:00:000 | GX05   | 01   | 99.55             | 0     | 99.55             |
| 2017-03-23 00:00:00:000 | GX05   | 02   | 255               | 0     | 255               |
| 2017-03-23 00:00:00:000 | GX1002 | 01   | 287.9             | 0     | 287.9             |
| 2017-03-23 00:00:00:000 | GX1002 | 02   | 80.5              | 0     | 80.5              |
| 2017-03-23 00:00:00:000 | GX21   | 01   | 0                 | 0     | 0                 |
| 2017-03-24 00:00:00:000 | GX0201 | 01   | 0                 | 0     | 0                 |
| 2017-03-24 00:00:00:000 | GX0201 | 02   | 0                 | 0     | 0                 |
| 2017-03-24 00:00:00:000 | GX03   | 01   | 538.35            | 0     | 538.35            |
| 2017-03-24 00:00:00:000 | GX03   | 02   | 0                 | 0     | 0                 |
| 2017-03-24 00:00:00:000 | GX04   | 01   | 108               | 0     | 108               |
| 2017-03-24 00:00:00:000 | GX05   | 01   | 99.55             | 0     | 99.55             |
| 2017-03-24 00:00:00:000 | GX05   | 02   | 255               | 0     | 255               |
| 2017-03-24 00:00:00:000 | GX1002 | 01   | 287.9             | 0     | 287.9             |
| 2017-03-24 00:00:00:000 | GX1002 | 02   | 80.5              | 0     | 80.5              |
| 2017-03-24 00:00:00:000 | GX21   | 01   | 0                 | 0     | 0                 |
| 2017-03-25 00:00:00:000 | GX0201 | 01   | 0                 | 0     | 0                 |
| 2017-03-25 00:00:00:000 | GX0201 | 02   | 0                 | 0     | 0                 |
| 2017-03-25 00:00:00:000 | GX03   | 01   | 897.95            | 0     | 897.95            |
| 2017-03-25 00:00:00:000 | GX03   | 02   | 0                 | 0     | 0                 |
| 2017-03-25 00:00:00:000 | GX04   | 01   | 108               | 0     | 108               |
| 2017-03-25 00:00:00:000 | GX05   | 01   | 99.55             | 0     | 99.55             |
| 2017-03-25 00:00:00:000 | GX05   | 02   | 255               | 0     | 255               |
| 2017-03-25 00:00:00:000 | GX1002 | 01   | 287.9             | 0     | 287.9             |
| 2017-03-25 00:00:00:000 | GX1002 | 02   | 80.5              | 0     | 80.5              |
| 2017-03-25 00:00:00:000 | GX21   | 01   | 0                 | 0     | 0                 |
| 2017-03-26 00:00:00:000 | GX0201 | 01   | 76.75             | 0     | 76.75             |
| 2017-03-26 00:00:00:000 | GX0201 | 02   | 0                 | 0     | 0                 |
| 2017-03-26 00:00:00:000 | GX03   | 01   | 1074.1            | 0     | 1074.1            |
| 2017-03-26 00:00:00:000 | GX03   | 02   | 0                 | 0     | 0                 |
| 2017-03-26 00:00:00:000 | GX04   | 01   | 193               | 0     | 193               |
| 2017-03-26 00:00:00:000 | GX05   | 01   | 99.55             | 0     | 99.55             |
| 2017-03-26 00:00:00:000 | GX05   | 02   | 469.95            | 0     | 469.95            |
| 2017-03-26 00:00:00:000 | GX1002 | 01   | 287.9             | 0     | 287.9             |
| 2017-03-26 00:00:00:000 | GX1002 | 02   | 80.5              | 0     | 80.5              |
| 2017-03-26 00:00:00:000 | GX21   | 01   | 690               | 0     | 690               |
| 2017-03-27 00:00:00:000 | GX0201 | 01   | 306.3             | 0     | 306.3             |
| 2017-03-27 00:00:00:000 | GX0201 | 02   | 0                 | 0     | 0                 |
| 2017-03-27 00:00:00:000 | GX03   | 01   | 1114.1            | 0     | 1114.1            |
| 2017-03-27 00:00:00:000 | GX03   | 02   | 0                 | 0     | 0                 |
| 2017-03-27 00:00:00:000 | GX04   | 01   | 193               | 0     | 193               |
| 2017-03-27 00:00:00:000 | GX05   | 01   | 99.55             | 0     | 99.55             |
| 2017-03-27 00:00:00:000 | GX05   | 02   | 469.95            | 0     | 469.95            |
| 2017-03-27 00:00:00:000 | GX1002 | 01   | 287.9             | 0     | 287.9             |
| 2017-03-27 00:00:00:000 | GX1002 | 02   | 80.5              | 0     | 80.5              |
| 2017-03-27 00:00:00:000 | GX21   | 01   | 690               | 0     | 690               |
| 2017-03-28 00:00:00:000 | GX0201 | 01   | 495.95            | 0     | 495.95            |
| 2017-03-28 00:00:00:000 | GX0201 | 02   | 0                 | 0     | 0                 |
| 2017-03-28 00:00:00:000 | GX03   | 01   | 1430.6            | 0     | 1430.6            |
| 2017-03-28 00:00:00:000 | GX03   | 02   | 0                 | 0     | 0                 |
| 2017-03-28 00:00:00:000 | GX04   | 01   | 193               | 0     | 193               |
| 2017-03-28 00:00:00:000 | GX05   | 01   | 99.55             | 0     | 99.55             |
| 2017-03-28 00:00:00:000 | GX05   | 02   | 469.95            | 0     | 469.95            |
| 2017-03-28 00:00:00:000 | GX1002 | 01   | 287.9             | 0     | 287.9             |
| 2017-03-28 00:00:00:000 | GX1002 | 02   | 80.5              | 0     | 80.5              |
| 2017-03-28 00:00:00:000 | GX21   | 01   | 690               | 0     | 690               |
| 2017-03-29 00:00:00:000 | GX0201 | 01   | 664.9000000000001 | 0     | 664.9000000000001 |
| 2017-03-29 00:00:00:000 | GX0201 | 02   | 0                 | 0     | 0                 |
| 2017-03-29 00:00:00:000 | GX03   | 01   | 1725.6            | 0     | 1725.6            |
| 2017-03-29 00:00:00:000 | GX03   | 02   | 0                 | 0     | 0                 |
| 2017-03-29 00:00:00:000 | GX04   | 01   | 193               | 0     | 193               |
| 2017-03-29 00:00:00:000 | GX05   | 01   | 99.55             | 0     | 99.55             |
| 2017-03-29 00:00:00:000 | GX05   | 02   | 469.95            | 0     | 469.95            |
| 2017-03-29 00:00:00:000 | GX1002 | 01   | 287.9             | 0     | 287.9             |
| 2017-03-29 00:00:00:000 | GX1002 | 02   | 80.5              | 0     | 80.5              |
| 2017-03-29 00:00:00:000 | GX21   | 01   | 690               | 0     | 690               |
| 2017-03-30 00:00:00:000 | GX0201 | 01   | 664.9000000000001 | 0     | 664.9000000000001 |
| 2017-03-30 00:00:00:000 | GX0201 | 02   | 0                 | 0     | 0                 |
| 2017-03-30 00:00:00:000 | GX03   | 01   | 1846.3            | 0     | 1846.3            |
| 2017-03-30 00:00:00:000 | GX03   | 02   | 14.5              | 0     | 14.5              |
| 2017-03-30 00:00:00:000 | GX04   | 01   | 193               | 0     | 193               |
| 2017-03-30 00:00:00:000 | GX05   | 01   | 99.55             | 0     | 99.55             |
| 2017-03-30 00:00:00:000 | GX05   | 02   | 469.95            | 0     | 469.95            |
| 2017-03-30 00:00:00:000 | GX1002 | 01   | 287.9             | 0     | 287.9             |
| 2017-03-30 00:00:00:000 | GX1002 | 02   | 80.5              | 0     | 80.5              |
| 2017-03-30 00:00:00:000 | GX21   | 01   | 690               | 0     | 690               |
+-------------------------+--------+------+-------------------+-------+-------------------+

#8


注意我上面SQL返回的入库和出库是从开始到当前日期的合计,不是当前的入库和出库,以其中一个产品为例:

declare @startDate datetime='2017-03-22',@EndDate datetime='2017-04-30'
select dateadd(d,sv.number,@StartDate) as [Date]
      ,p.企业编码,p.分类编码
      ,ISNULL(i.in_TotalQty,0) as 入库数量 ,ISNULL(o.out_TotalQty,0) AS 出库数量,ISNULL(i.in_TotalQty,0)-ISNULL(o.out_TotalQty,0) as 余额
from master.dbo.spt_values as sv
inner join (
    select distinct NianDu,QYBM_入库企业编码 as 企业编码,FLBM_所属分类编码 as 分类编码 from #t
) as p on 1=1
outer apply(
    select sum(SL_入库数量) as in_TotalQty--,max(case when datediff(d,@startDate,t.RKRQ_入库日期)=sv.number then SL_入库数量 else null end ) as in_Qty 
    from #t as t
    where datediff(d,@startDate,t.RKRQ_入库日期)<=sv.number 
    and t.NianDu=p.NianDu and t.QYBM_入库企业编码=p.企业编码 and t.FLBM_所属分类编码=p.分类编码
 ) as i
 outer apply(
    select sum(SL_出库数量) as out_TotalQty 
    from #o as t
    where datediff(d,@startDate,t.CKRQ_出库日期)<=sv.number 
    and t.NianDu=p.NianDu and t.HQBM_货权企业编码=p.企业编码 and t.FLBM_所属分类编码=p.分类编码
 ) as o
where sv.type='P' and sv.number <=datediff(d,@startdate,@EndDate)
and p.企业编码='GX21'
order by sv.number,p.企业编码,p.分类编码


+-------------------------+------+------+------+------+------+
| Date                    | 企业编码 | 分类编码 | 入库数量 | 出库数量 | 余额   |
+-------------------------+------+------+------+------+------+
| 2017-03-22 00:00:00:000 | GX21 | 01   | 0    | 0    | 0    |
| 2017-03-23 00:00:00:000 | GX21 | 01   | 0    | 0    | 0    |
| 2017-03-24 00:00:00:000 | GX21 | 01   | 0    | 0    | 0    |
| 2017-03-25 00:00:00:000 | GX21 | 01   | 0    | 0    | 0    |
| 2017-03-26 00:00:00:000 | GX21 | 01   | 690  | 0    | 690  |
| 2017-03-27 00:00:00:000 | GX21 | 01   | 690  | 0    | 690  |
| 2017-03-28 00:00:00:000 | GX21 | 01   | 690  | 0    | 690  |
| 2017-03-29 00:00:00:000 | GX21 | 01   | 690  | 0    | 690  |
| 2017-03-30 00:00:00:000 | GX21 | 01   | 690  | 0    | 690  |
| 2017-03-31 00:00:00:000 | GX21 | 01   | 690  | 0    | 690  |
| 2017-04-01 00:00:00:000 | GX21 | 01   | 690  | 0    | 690  |
| 2017-04-02 00:00:00:000 | GX21 | 01   | 690  | 0    | 690  |
| 2017-04-03 00:00:00:000 | GX21 | 01   | 690  | 0    | 690  |
| 2017-04-04 00:00:00:000 | GX21 | 01   | 690  | 0    | 690  |
| 2017-04-05 00:00:00:000 | GX21 | 01   | 1659 | 0    | 1659 |
| 2017-04-06 00:00:00:000 | GX21 | 01   | 1659 | 0    | 1659 |
| 2017-04-07 00:00:00:000 | GX21 | 01   | 1659 | 0    | 1659 |
| 2017-04-08 00:00:00:000 | GX21 | 01   | 1659 | 0    | 1659 |
| 2017-04-09 00:00:00:000 | GX21 | 01   | 1659 | 0    | 1659 |
| 2017-04-10 00:00:00:000 | GX21 | 01   | 1659 | 0    | 1659 |
| 2017-04-11 00:00:00:000 | GX21 | 01   | 1659 | 0    | 1659 |
| 2017-04-12 00:00:00:000 | GX21 | 01   | 1659 | 0    | 1659 |
| 2017-04-13 00:00:00:000 | GX21 | 01   | 1659 | 160  | 1499 |
| 2017-04-14 00:00:00:000 | GX21 | 01   | 1659 | 295  | 1364 |
| 2017-04-15 00:00:00:000 | GX21 | 01   | 1659 | 295  | 1364 |
| 2017-04-16 00:00:00:000 | GX21 | 01   | 1659 | 295  | 1364 |
| 2017-04-17 00:00:00:000 | GX21 | 01   | 1659 | 295  | 1364 |
| 2017-04-18 00:00:00:000 | GX21 | 01   | 1659 | 295  | 1364 |
| 2017-04-19 00:00:00:000 | GX21 | 01   | 1659 | 295  | 1364 |
| 2017-04-20 00:00:00:000 | GX21 | 01   | 1659 | 295  | 1364 |
| 2017-04-21 00:00:00:000 | GX21 | 01   | 1659 | 295  | 1364 |
| 2017-04-22 00:00:00:000 | GX21 | 01   | 1659 | 295  | 1364 |
| 2017-04-23 00:00:00:000 | GX21 | 01   | 1659 | 295  | 1364 |
| 2017-04-24 00:00:00:000 | GX21 | 01   | 1659 | 295  | 1364 |
| 2017-04-25 00:00:00:000 | GX21 | 01   | 1659 | 295  | 1364 |
| 2017-04-26 00:00:00:000 | GX21 | 01   | 1659 | 295  | 1364 |
| 2017-04-27 00:00:00:000 | GX21 | 01   | 1659 | 295  | 1364 |
| 2017-04-28 00:00:00:000 | GX21 | 01   | 1659 | 295  | 1364 |
| 2017-04-29 00:00:00:000 | GX21 | 01   | 1659 | 295  | 1364 |
| 2017-04-30 00:00:00:000 | GX21 | 01   | 1659 | 295  | 1364 |
+-------------------------+------+------+------+------+------+

#9


你这个需求很奇葩啊,如果物料多,并且时间跨度长的话,计算量很大,效率非常低,你不如建个作业或者用其他方式,每天定时计算当天库存数量,存放到一个库存日表中

#10


每次全量计算从成立开始至今的出入库数量来计算库存是不科学的,你应该设计一张库存表来针对每次出入库情况,增减库存数量。

#11


引用 9 楼 shinger126 的回复:
你这个需求很奇葩啊,如果物料多,并且时间跨度长的话,计算量很大,效率非常低,你不如建个作业或者用其他方式,每天定时计算当天库存数量,存放到一个库存日表中


我也知道啊,但是没办法啊,我没权限直接上数据库修改,只能用软件链接上数据库查询而已

#12


引用 10 楼 zengertao 的回复:
每次全量计算从成立开始至今的出入库数量来计算库存是不科学的,你应该设计一张库存表来针对每次出入库情况,增减库存数量。


并不用全量计算从成立开始至今的出入库数量,这个系统是按年度计算的,到新的一年会把上一年的库存量用一次入库转存到新的年度,而且目前数据量不是很大,所以用#7的方法也还可以

#1


试试这个:
SELECT  a.RKRQ_入库日期 ,
        a.QYBM_入库企业编码 ,
        a.FLBM_所属分类编码 ,
        SUM(a.SL_入库数量) - SUM(b.SL_出库数量) AS 库存数量
FROM    入库表 a
        JOIN 出库表 b ON b.FLBM_所属分类编码 = a.FLBM_所属分类编码
                      AND a.QYBM_入库企业编码 = b.HQBM_货权企业编码
                      AND a.RKRQ_入库日期 = b.CKRQ_出库日期
GROUP BY a.RKRQ_入库日期 ,
        a.QYBM_入库企业编码 ,
        a.FLBM_所属分类编码

#2


楼主是要计算每天的结存数量么?

#3


计算每天结存数量:
;with crk as (select QYBM_入库企业编码 qybm,FLBM_所属分类编码 flbm,SL_入库数量 sl,RKRQ_入库日期 rkrq from 入库表
           union all
              select HQBM_货权企业编码,FLBM_所属分类编码,-SL_出库数量,CKRQ_出库日期 from 出库表),
 crkhz as (select qybm,flbm,SUM(sl) sl,rkrq from t
            group by qybm,flbm,rkrq)
 select qybm,flbm,rkrq,sl=isnull((select SUM(sl) from crkhz a
             where a.qybm=b.qybm and a.flbm=b.flbm ans a.rkrq>=b.rkrq),0)
      from crkhz b

#4


引用 1 楼 sinat_28984567 的回复:
试试这个:
SELECT  a.RKRQ_入库日期 ,
        a.QYBM_入库企业编码 ,
        a.FLBM_所属分类编码 ,
        SUM(a.SL_入库数量) - SUM(b.SL_出库数量) AS 库存数量
FROM    入库表 a
        JOIN 出库表 b ON b.FLBM_所属分类编码 = a.FLBM_所属分类编码
                      AND a.QYBM_入库企业编码 = b.HQBM_货权企业编码
                      AND a.RKRQ_入库日期 = b.CKRQ_出库日期
GROUP BY a.RKRQ_入库日期 ,
        a.QYBM_入库企业编码 ,
        a.FLBM_所属分类编码


没那么简单的,首先,需要的是每一天的库存量,出库入库表的出入库时间并不是一一对应的,有的日期可能有入库数据,但是没有出库数据,也有可能只有出库数据没有入库数据,还可能有的日期出库入库数据都没有,还是谢谢你的关注

#5


引用 3 楼 shinger126 的回复:
计算每天结存数量:
;with crk as (select QYBM_入库企业编码 qybm,FLBM_所属分类编码 flbm,SL_入库数量 sl,RKRQ_入库日期 rkrq from 入库表
           union all
              select HQBM_货权企业编码,FLBM_所属分类编码,-SL_出库数量,CKRQ_出库日期 from 出库表),
 crkhz as (select qybm,flbm,SUM(sl) sl,rkrq from t
            group by qybm,flbm,rkrq)
 select qybm,flbm,rkrq,sl=isnull((select SUM(sl) from crkhz a
             where a.qybm=b.qybm and a.flbm=b.flbm ans a.rkrq>=b.rkrq),0)
      from crkhz b


这样还是少了那些出入库没有记录的日期的库存呢

#6


下面的例子是在指定时间内列出每个产品每天的情况,不管没有出入库记录,所以数据比较多,不知道是不是你想要的

if object_id('tempdb..#t') is not null drop table #t
create table #t(NianDu varchar(10),QYBM_入库企业编码 varchar(10),FLBM_所属分类编码 varchar(10),SL_入库数量 float,RKRQ_入库日期 datetime)
insert into #t(NianDu,QYBM_入库企业编码,FLBM_所属分类编码,SL_入库数量,RKRQ_入库日期)
select '16/17','GX21','01',969.000,'2017-04-05 00:00:00.000' union all 
select '16/17','GX03','01',0.000,'2017-04-04 00:00:00.000' union all 
select '16/17','GX03','02',0.000,'2017-04-04 00:00:00.000' union all 
select '16/17','GX0201','02',180.745,'2017-03-31 00:00:00.000' union all 
select '16/17','GX03','01',19.500,'2017-03-31 00:00:00.000' union all 
select '16/17','GX03','02',142.150,'2017-03-31 00:00:00.000' union all 
select '16/17','GX03','01',120.700,'2017-03-30 00:00:00.000' union all 
select '16/17','GX03','02',14.500,'2017-03-30 00:00:00.000' union all 
select '16/17','GX0201','01',168.950,'2017-03-29 00:00:00.000' union all 
select '16/17','GX03','01',295.000,'2017-03-29 00:00:00.000' union all 
select '16/17','GX0201','01',189.650,'2017-03-28 00:00:00.000' union all 
select '16/17','GX03','01',316.500,'2017-03-28 00:00:00.000' union all 
select '16/17','GX0201','01',229.550,'2017-03-27 00:00:00.000' union all 
select '16/17','GX03','01',40.000,'2017-03-27 00:00:00.000' union all 
select '16/17','GX0201','01',76.750,'2017-03-26 00:00:00.000' union all 
select '16/17','GX03','01',176.150,'2017-03-26 00:00:00.000' union all 
select '16/17','GX04','01',85.000,'2017-03-26 00:00:00.000' union all 
select '16/17','GX05','02',214.950,'2017-03-26 00:00:00.000' union all 
select '16/17','GX21','01',690.000,'2017-03-26 00:00:00.000' union all 
select '16/17','GX03','01',359.600,'2017-03-25 00:00:00.000' union all 
select '16/17','GX03','01',381.550,'2017-03-24 00:00:00.000' union all 
select '16/17','GX03','01',156.800,'2017-03-23 00:00:00.000' union all 
select '16/17','GX04','01',108.000,'2017-03-23 00:00:00.000' union all 
select '16/17','GX05','02',112.500,'2017-03-23 00:00:00.000' union all 
select '16/17','GX1002','01',27.350,'2017-03-23 00:00:00.000' union all 
select '16/17','GX1002','02',80.500,'2017-03-23 00:00:00.000' union all 
select '16/17','GX05','01',99.550,'2017-03-22 00:00:00.000' union all 
select '16/17','GX05','02',142.500,'2017-03-22 00:00:00.000' union all 
select '16/17','GX1002','01',260.550,'2017-03-22 00:00:00.000'

--select * from #t


if object_id('tempdb..#o') is not null drop table #o
create table #o(NianDu varchar(10),HQBM_货权企业编码 varchar(10),FLBM_所属分类编码 varchar(10),SL_出库数量 float,CKRQ_出库日期 datetime)
insert into #o(NianDu,HQBM_货权企业编码,FLBM_所属分类编码,SL_出库数量,CKRQ_出库日期)

select '16/17','GX0401','01',52.000,'2017-04-14 00:00:00.000' union all
select '16/17','GX1002','01',26.000,'2017-04-14 00:00:00.000' union all
select '16/17','GX21','01',54.000,'2017-04-14 00:00:00.000' union all
select '16/17','GX03','01',32.000,'2017-04-14 00:00:00.000' union all
select '16/17','GX21','01',54.000,'2017-04-14 00:00:00.000' union all
select '16/17','GX0401','01',27.000,'2017-04-14 00:00:00.000' union all
select '16/17','GX05','01',-120.000,'2017-04-14 00:00:00.000' union all
select '16/17','GX0401','01',27.000,'2017-04-14 00:00:00.000' union all
select '16/17','GX05','01',26.000,'2017-04-14 00:00:00.000' union all
select '16/17','GX21','01',27.000,'2017-04-14 00:00:00.000' union all
select '16/17','GX0401','01',-27.000,'2017-04-14 00:00:00.000' union all
select '16/17','GX1001','01',45.000,'2017-04-14 00:00:00.000' union all
select '16/17','GX05','01',40.000,'2017-04-14 00:00:00.000' union all
select '16/17','GX05','01',26.000,'2017-04-14 00:00:00.000' union all
select '16/17','GX01','02',11.000,'2017-04-13 00:00:00.000' union all
select '16/17','GX0401','01',1.000,'2017-04-13 00:00:00.000' union all
select '16/17','GX0401','01',27.000,'2017-04-13 00:00:00.000' union all
select '16/17','GX03','01',40.000,'2017-04-13 00:00:00.000' union all
select '16/17','GX0401','01',27.000,'2017-04-13 00:00:00.000' union all
select '16/17','GX0401','01',27.000,'2017-04-13 00:00:00.000' union all
select '16/17','GX04','01',-54.000,'2017-04-13 00:00:00.000' union all
select '16/17','GX0401','01',-27.000,'2017-04-13 00:00:00.000' union all
select '16/17','GX21','01',52.000,'2017-04-13 00:00:00.000' union all
select '16/17','GX0402','01',52.000,'2017-04-13 00:00:00.000' union all
select '16/17','GX03','01',-40.000,'2017-04-13 00:00:00.000' union all
select '16/17','GX0401','01',-27.000,'2017-04-13 00:00:00.000' union all
select '16/17','GX05','01',50.000,'2017-04-13 00:00:00.000' union all
select '16/17','GX03','01',4.950,'2017-04-13 00:00:00.000' union all
select '16/17','GX21','01',54.000,'2017-04-13 00:00:00.000' union all
select '16/17','GX0401','01',27.000,'2017-04-13 00:00:00.000' union all
select '16/17','GX04','01',-46.000,'2017-04-13 00:00:00.000' union all
select '16/17','GX05','01',26.000,'2017-04-13 00:00:00.000' union all
select '16/17','GX1002','01',26.000,'2017-04-13 00:00:00.000' union all
select '16/17','GX21','01',54.000,'2017-04-13 00:00:00.000'
--select * from #o


declare @startDate datetime='2017-03-22',@EndDate datetime='2017-04-30'
select dateadd(d,sv.number,@StartDate) as [Date]
      ,p.企业编码,p.分类编码
      ,ISNULL(i.in_Qty,0) as 入库数量 ,ISNULL(o.out_Qty,0) AS 出库数量,ISNULL(i.in_Qty,0)-ISNULL(o.out_Qty,0) as 余额
from master.dbo.spt_values as sv
inner join (
    select distinct NianDu,QYBM_入库企业编码 as 企业编码,FLBM_所属分类编码 as 分类编码 from #t
) as p on 1=1
outer apply(
    select sum(SL_入库数量) as in_Qty 
    from #t as t
    where datediff(d,@startDate,t.RKRQ_入库日期)<=sv.number 
    and t.NianDu=p.NianDu and t.QYBM_入库企业编码=p.企业编码 and t.FLBM_所属分类编码=p.分类编码
 ) as i
 outer apply(
    select sum(SL_出库数量) as out_Qty 
    from #o as t
    where datediff(d,@startDate,t.CKRQ_出库日期)<=sv.number 
    and t.NianDu=p.NianDu and t.HQBM_货权企业编码=p.企业编码 and t.FLBM_所属分类编码=p.分类编码
 ) as o
where sv.type='P' and sv.number <=datediff(d,@startdate,@EndDate)
order by sv.number,p.企业编码,p.分类编码

#7


因为篇幅(多了不让发)摘抄部分数据

+-------------------------+--------+------+-------------------+-------+-------------------+
| Date                    | 企业编码   | 分类编码 | 入库数量              | 出库数量  | 余额                |
+-------------------------+--------+------+-------------------+-------+-------------------+
| 2017-03-22 00:00:00:000 | GX0201 | 01   | 0                 | 0     | 0                 |
| 2017-03-22 00:00:00:000 | GX0201 | 02   | 0                 | 0     | 0                 |
| 2017-03-22 00:00:00:000 | GX03   | 01   | 0                 | 0     | 0                 |
| 2017-03-22 00:00:00:000 | GX03   | 02   | 0                 | 0     | 0                 |
| 2017-03-22 00:00:00:000 | GX04   | 01   | 0                 | 0     | 0                 |
| 2017-03-22 00:00:00:000 | GX05   | 01   | 99.55             | 0     | 99.55             |
| 2017-03-22 00:00:00:000 | GX05   | 02   | 142.5             | 0     | 142.5             |
| 2017-03-22 00:00:00:000 | GX1002 | 01   | 260.55            | 0     | 260.55            |
| 2017-03-22 00:00:00:000 | GX1002 | 02   | 0                 | 0     | 0                 |
| 2017-03-22 00:00:00:000 | GX21   | 01   | 0                 | 0     | 0                 |
| 2017-03-23 00:00:00:000 | GX0201 | 01   | 0                 | 0     | 0                 |
| 2017-03-23 00:00:00:000 | GX0201 | 02   | 0                 | 0     | 0                 |
| 2017-03-23 00:00:00:000 | GX03   | 01   | 156.8             | 0     | 156.8             |
| 2017-03-23 00:00:00:000 | GX03   | 02   | 0                 | 0     | 0                 |
| 2017-03-23 00:00:00:000 | GX04   | 01   | 108               | 0     | 108               |
| 2017-03-23 00:00:00:000 | GX05   | 01   | 99.55             | 0     | 99.55             |
| 2017-03-23 00:00:00:000 | GX05   | 02   | 255               | 0     | 255               |
| 2017-03-23 00:00:00:000 | GX1002 | 01   | 287.9             | 0     | 287.9             |
| 2017-03-23 00:00:00:000 | GX1002 | 02   | 80.5              | 0     | 80.5              |
| 2017-03-23 00:00:00:000 | GX21   | 01   | 0                 | 0     | 0                 |
| 2017-03-24 00:00:00:000 | GX0201 | 01   | 0                 | 0     | 0                 |
| 2017-03-24 00:00:00:000 | GX0201 | 02   | 0                 | 0     | 0                 |
| 2017-03-24 00:00:00:000 | GX03   | 01   | 538.35            | 0     | 538.35            |
| 2017-03-24 00:00:00:000 | GX03   | 02   | 0                 | 0     | 0                 |
| 2017-03-24 00:00:00:000 | GX04   | 01   | 108               | 0     | 108               |
| 2017-03-24 00:00:00:000 | GX05   | 01   | 99.55             | 0     | 99.55             |
| 2017-03-24 00:00:00:000 | GX05   | 02   | 255               | 0     | 255               |
| 2017-03-24 00:00:00:000 | GX1002 | 01   | 287.9             | 0     | 287.9             |
| 2017-03-24 00:00:00:000 | GX1002 | 02   | 80.5              | 0     | 80.5              |
| 2017-03-24 00:00:00:000 | GX21   | 01   | 0                 | 0     | 0                 |
| 2017-03-25 00:00:00:000 | GX0201 | 01   | 0                 | 0     | 0                 |
| 2017-03-25 00:00:00:000 | GX0201 | 02   | 0                 | 0     | 0                 |
| 2017-03-25 00:00:00:000 | GX03   | 01   | 897.95            | 0     | 897.95            |
| 2017-03-25 00:00:00:000 | GX03   | 02   | 0                 | 0     | 0                 |
| 2017-03-25 00:00:00:000 | GX04   | 01   | 108               | 0     | 108               |
| 2017-03-25 00:00:00:000 | GX05   | 01   | 99.55             | 0     | 99.55             |
| 2017-03-25 00:00:00:000 | GX05   | 02   | 255               | 0     | 255               |
| 2017-03-25 00:00:00:000 | GX1002 | 01   | 287.9             | 0     | 287.9             |
| 2017-03-25 00:00:00:000 | GX1002 | 02   | 80.5              | 0     | 80.5              |
| 2017-03-25 00:00:00:000 | GX21   | 01   | 0                 | 0     | 0                 |
| 2017-03-26 00:00:00:000 | GX0201 | 01   | 76.75             | 0     | 76.75             |
| 2017-03-26 00:00:00:000 | GX0201 | 02   | 0                 | 0     | 0                 |
| 2017-03-26 00:00:00:000 | GX03   | 01   | 1074.1            | 0     | 1074.1            |
| 2017-03-26 00:00:00:000 | GX03   | 02   | 0                 | 0     | 0                 |
| 2017-03-26 00:00:00:000 | GX04   | 01   | 193               | 0     | 193               |
| 2017-03-26 00:00:00:000 | GX05   | 01   | 99.55             | 0     | 99.55             |
| 2017-03-26 00:00:00:000 | GX05   | 02   | 469.95            | 0     | 469.95            |
| 2017-03-26 00:00:00:000 | GX1002 | 01   | 287.9             | 0     | 287.9             |
| 2017-03-26 00:00:00:000 | GX1002 | 02   | 80.5              | 0     | 80.5              |
| 2017-03-26 00:00:00:000 | GX21   | 01   | 690               | 0     | 690               |
| 2017-03-27 00:00:00:000 | GX0201 | 01   | 306.3             | 0     | 306.3             |
| 2017-03-27 00:00:00:000 | GX0201 | 02   | 0                 | 0     | 0                 |
| 2017-03-27 00:00:00:000 | GX03   | 01   | 1114.1            | 0     | 1114.1            |
| 2017-03-27 00:00:00:000 | GX03   | 02   | 0                 | 0     | 0                 |
| 2017-03-27 00:00:00:000 | GX04   | 01   | 193               | 0     | 193               |
| 2017-03-27 00:00:00:000 | GX05   | 01   | 99.55             | 0     | 99.55             |
| 2017-03-27 00:00:00:000 | GX05   | 02   | 469.95            | 0     | 469.95            |
| 2017-03-27 00:00:00:000 | GX1002 | 01   | 287.9             | 0     | 287.9             |
| 2017-03-27 00:00:00:000 | GX1002 | 02   | 80.5              | 0     | 80.5              |
| 2017-03-27 00:00:00:000 | GX21   | 01   | 690               | 0     | 690               |
| 2017-03-28 00:00:00:000 | GX0201 | 01   | 495.95            | 0     | 495.95            |
| 2017-03-28 00:00:00:000 | GX0201 | 02   | 0                 | 0     | 0                 |
| 2017-03-28 00:00:00:000 | GX03   | 01   | 1430.6            | 0     | 1430.6            |
| 2017-03-28 00:00:00:000 | GX03   | 02   | 0                 | 0     | 0                 |
| 2017-03-28 00:00:00:000 | GX04   | 01   | 193               | 0     | 193               |
| 2017-03-28 00:00:00:000 | GX05   | 01   | 99.55             | 0     | 99.55             |
| 2017-03-28 00:00:00:000 | GX05   | 02   | 469.95            | 0     | 469.95            |
| 2017-03-28 00:00:00:000 | GX1002 | 01   | 287.9             | 0     | 287.9             |
| 2017-03-28 00:00:00:000 | GX1002 | 02   | 80.5              | 0     | 80.5              |
| 2017-03-28 00:00:00:000 | GX21   | 01   | 690               | 0     | 690               |
| 2017-03-29 00:00:00:000 | GX0201 | 01   | 664.9000000000001 | 0     | 664.9000000000001 |
| 2017-03-29 00:00:00:000 | GX0201 | 02   | 0                 | 0     | 0                 |
| 2017-03-29 00:00:00:000 | GX03   | 01   | 1725.6            | 0     | 1725.6            |
| 2017-03-29 00:00:00:000 | GX03   | 02   | 0                 | 0     | 0                 |
| 2017-03-29 00:00:00:000 | GX04   | 01   | 193               | 0     | 193               |
| 2017-03-29 00:00:00:000 | GX05   | 01   | 99.55             | 0     | 99.55             |
| 2017-03-29 00:00:00:000 | GX05   | 02   | 469.95            | 0     | 469.95            |
| 2017-03-29 00:00:00:000 | GX1002 | 01   | 287.9             | 0     | 287.9             |
| 2017-03-29 00:00:00:000 | GX1002 | 02   | 80.5              | 0     | 80.5              |
| 2017-03-29 00:00:00:000 | GX21   | 01   | 690               | 0     | 690               |
| 2017-03-30 00:00:00:000 | GX0201 | 01   | 664.9000000000001 | 0     | 664.9000000000001 |
| 2017-03-30 00:00:00:000 | GX0201 | 02   | 0                 | 0     | 0                 |
| 2017-03-30 00:00:00:000 | GX03   | 01   | 1846.3            | 0     | 1846.3            |
| 2017-03-30 00:00:00:000 | GX03   | 02   | 14.5              | 0     | 14.5              |
| 2017-03-30 00:00:00:000 | GX04   | 01   | 193               | 0     | 193               |
| 2017-03-30 00:00:00:000 | GX05   | 01   | 99.55             | 0     | 99.55             |
| 2017-03-30 00:00:00:000 | GX05   | 02   | 469.95            | 0     | 469.95            |
| 2017-03-30 00:00:00:000 | GX1002 | 01   | 287.9             | 0     | 287.9             |
| 2017-03-30 00:00:00:000 | GX1002 | 02   | 80.5              | 0     | 80.5              |
| 2017-03-30 00:00:00:000 | GX21   | 01   | 690               | 0     | 690               |
+-------------------------+--------+------+-------------------+-------+-------------------+

#8


注意我上面SQL返回的入库和出库是从开始到当前日期的合计,不是当前的入库和出库,以其中一个产品为例:

declare @startDate datetime='2017-03-22',@EndDate datetime='2017-04-30'
select dateadd(d,sv.number,@StartDate) as [Date]
      ,p.企业编码,p.分类编码
      ,ISNULL(i.in_TotalQty,0) as 入库数量 ,ISNULL(o.out_TotalQty,0) AS 出库数量,ISNULL(i.in_TotalQty,0)-ISNULL(o.out_TotalQty,0) as 余额
from master.dbo.spt_values as sv
inner join (
    select distinct NianDu,QYBM_入库企业编码 as 企业编码,FLBM_所属分类编码 as 分类编码 from #t
) as p on 1=1
outer apply(
    select sum(SL_入库数量) as in_TotalQty--,max(case when datediff(d,@startDate,t.RKRQ_入库日期)=sv.number then SL_入库数量 else null end ) as in_Qty 
    from #t as t
    where datediff(d,@startDate,t.RKRQ_入库日期)<=sv.number 
    and t.NianDu=p.NianDu and t.QYBM_入库企业编码=p.企业编码 and t.FLBM_所属分类编码=p.分类编码
 ) as i
 outer apply(
    select sum(SL_出库数量) as out_TotalQty 
    from #o as t
    where datediff(d,@startDate,t.CKRQ_出库日期)<=sv.number 
    and t.NianDu=p.NianDu and t.HQBM_货权企业编码=p.企业编码 and t.FLBM_所属分类编码=p.分类编码
 ) as o
where sv.type='P' and sv.number <=datediff(d,@startdate,@EndDate)
and p.企业编码='GX21'
order by sv.number,p.企业编码,p.分类编码


+-------------------------+------+------+------+------+------+
| Date                    | 企业编码 | 分类编码 | 入库数量 | 出库数量 | 余额   |
+-------------------------+------+------+------+------+------+
| 2017-03-22 00:00:00:000 | GX21 | 01   | 0    | 0    | 0    |
| 2017-03-23 00:00:00:000 | GX21 | 01   | 0    | 0    | 0    |
| 2017-03-24 00:00:00:000 | GX21 | 01   | 0    | 0    | 0    |
| 2017-03-25 00:00:00:000 | GX21 | 01   | 0    | 0    | 0    |
| 2017-03-26 00:00:00:000 | GX21 | 01   | 690  | 0    | 690  |
| 2017-03-27 00:00:00:000 | GX21 | 01   | 690  | 0    | 690  |
| 2017-03-28 00:00:00:000 | GX21 | 01   | 690  | 0    | 690  |
| 2017-03-29 00:00:00:000 | GX21 | 01   | 690  | 0    | 690  |
| 2017-03-30 00:00:00:000 | GX21 | 01   | 690  | 0    | 690  |
| 2017-03-31 00:00:00:000 | GX21 | 01   | 690  | 0    | 690  |
| 2017-04-01 00:00:00:000 | GX21 | 01   | 690  | 0    | 690  |
| 2017-04-02 00:00:00:000 | GX21 | 01   | 690  | 0    | 690  |
| 2017-04-03 00:00:00:000 | GX21 | 01   | 690  | 0    | 690  |
| 2017-04-04 00:00:00:000 | GX21 | 01   | 690  | 0    | 690  |
| 2017-04-05 00:00:00:000 | GX21 | 01   | 1659 | 0    | 1659 |
| 2017-04-06 00:00:00:000 | GX21 | 01   | 1659 | 0    | 1659 |
| 2017-04-07 00:00:00:000 | GX21 | 01   | 1659 | 0    | 1659 |
| 2017-04-08 00:00:00:000 | GX21 | 01   | 1659 | 0    | 1659 |
| 2017-04-09 00:00:00:000 | GX21 | 01   | 1659 | 0    | 1659 |
| 2017-04-10 00:00:00:000 | GX21 | 01   | 1659 | 0    | 1659 |
| 2017-04-11 00:00:00:000 | GX21 | 01   | 1659 | 0    | 1659 |
| 2017-04-12 00:00:00:000 | GX21 | 01   | 1659 | 0    | 1659 |
| 2017-04-13 00:00:00:000 | GX21 | 01   | 1659 | 160  | 1499 |
| 2017-04-14 00:00:00:000 | GX21 | 01   | 1659 | 295  | 1364 |
| 2017-04-15 00:00:00:000 | GX21 | 01   | 1659 | 295  | 1364 |
| 2017-04-16 00:00:00:000 | GX21 | 01   | 1659 | 295  | 1364 |
| 2017-04-17 00:00:00:000 | GX21 | 01   | 1659 | 295  | 1364 |
| 2017-04-18 00:00:00:000 | GX21 | 01   | 1659 | 295  | 1364 |
| 2017-04-19 00:00:00:000 | GX21 | 01   | 1659 | 295  | 1364 |
| 2017-04-20 00:00:00:000 | GX21 | 01   | 1659 | 295  | 1364 |
| 2017-04-21 00:00:00:000 | GX21 | 01   | 1659 | 295  | 1364 |
| 2017-04-22 00:00:00:000 | GX21 | 01   | 1659 | 295  | 1364 |
| 2017-04-23 00:00:00:000 | GX21 | 01   | 1659 | 295  | 1364 |
| 2017-04-24 00:00:00:000 | GX21 | 01   | 1659 | 295  | 1364 |
| 2017-04-25 00:00:00:000 | GX21 | 01   | 1659 | 295  | 1364 |
| 2017-04-26 00:00:00:000 | GX21 | 01   | 1659 | 295  | 1364 |
| 2017-04-27 00:00:00:000 | GX21 | 01   | 1659 | 295  | 1364 |
| 2017-04-28 00:00:00:000 | GX21 | 01   | 1659 | 295  | 1364 |
| 2017-04-29 00:00:00:000 | GX21 | 01   | 1659 | 295  | 1364 |
| 2017-04-30 00:00:00:000 | GX21 | 01   | 1659 | 295  | 1364 |
+-------------------------+------+------+------+------+------+

#9


你这个需求很奇葩啊,如果物料多,并且时间跨度长的话,计算量很大,效率非常低,你不如建个作业或者用其他方式,每天定时计算当天库存数量,存放到一个库存日表中

#10


每次全量计算从成立开始至今的出入库数量来计算库存是不科学的,你应该设计一张库存表来针对每次出入库情况,增减库存数量。

#11


引用 9 楼 shinger126 的回复:
你这个需求很奇葩啊,如果物料多,并且时间跨度长的话,计算量很大,效率非常低,你不如建个作业或者用其他方式,每天定时计算当天库存数量,存放到一个库存日表中


我也知道啊,但是没办法啊,我没权限直接上数据库修改,只能用软件链接上数据库查询而已

#12


引用 10 楼 zengertao 的回复:
每次全量计算从成立开始至今的出入库数量来计算库存是不科学的,你应该设计一张库存表来针对每次出入库情况,增减库存数量。


并不用全量计算从成立开始至今的出入库数量,这个系统是按年度计算的,到新的一年会把上一年的库存量用一次入库转存到新的年度,而且目前数据量不是很大,所以用#7的方法也还可以