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的方法也还可以
标签：

相关文章