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

火柴52030 2017-07-14 05:31:40
入库表和出库表都包含不同企业，不同商品的出入库记录，存在有的日期没有出入库记录情况，现在要求计算每日仓库的库存费用，所以我想求出仓库每日的库存数量，按不同企业，不同商品分类统计好。
出入库表我只是放了部分数据

入库表结构如下

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
...全文
1238 12 打赏 收藏 转发到动态 举报
写回复
用AI写文章
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
火柴52030 2017-07-19
  • 打赏
  • 举报
 回复
引用 10 楼 zengertao 的回复:
每次全量计算从成立开始至今的出入库数量来计算库存是不科学的，你应该设计一张库存表来针对每次出入库情况，增减库存数量。
并不用全量计算从成立开始至今的出入库数量，这个系统是按年度计算的，到新的一年会把上一年的库存量用一次入库转存到新的年度，而且目前数据量不是很大，所以用#7的方法也还可以
火柴52030 2017-07-19
  • 打赏
  • 举报
 回复
引用 9 楼 shinger126 的回复:
你这个需求很奇葩啊，如果物料多，并且时间跨度长的话，计算量很大，效率非常低，你不如建个作业或者用其他方式，每天定时计算当天库存数量，存放到一个库存日表中
我也知道啊，但是没办法啊，我没权限直接上数据库修改，只能用软件链接上数据库查询而已
繁花尽流年 2017-07-18
  • 打赏
  • 举报
 回复
每次全量计算从成立开始至今的出入库数量来计算库存是不科学的，你应该设计一张库存表来针对每次出入库情况，增减库存数量。
道素 2017-07-18
  • 打赏
  • 举报
 回复
注意我上面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 |
+-------------------------+------+------+------+------+------+
道素 2017-07-18
  • 打赏
  • 举报
 回复
因为篇幅（多了不让发）摘抄部分数据 

+-------------------------+--------+------+-------------------+-------+-------------------+
| 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               |
+-------------------------+--------+------+-------------------+-------+-------------------+
道素 2017-07-18
  • 打赏
  • 举报
 回复
下面的例子是在指定时间内列出每个产品每天的情况，不管没有出入库记录，所以数据比较多，不知道是不是你想要的 

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.分类编码
shinger126 2017-07-18
  • 打赏
  • 举报
 回复
你这个需求很奇葩啊，如果物料多，并且时间跨度长的话，计算量很大，效率非常低，你不如建个作业或者用其他方式，每天定时计算当天库存数量，存放到一个库存日表中
火柴52030 2017-07-17
  • 打赏
  • 举报
 回复
引用 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
这样还是少了那些出入库没有记录的日期的库存呢
火柴52030 2017-07-17
  • 打赏
  • 举报
 回复
引用 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_所属分类编码
没那么简单的，首先，需要的是每一天的库存量，出库入库表的出入库时间并不是一一对应的，有的日期可能有入库数据，但是没有出库数据，也有可能只有出库数据没有入库数据，还可能有的日期出库入库数据都没有，还是谢谢你的关注
shinger126 2017-07-15
  • 打赏
  • 举报
 回复
计算每天结存数量： ;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
shinger126 2017-07-15
  • 打赏
  • 举报
 回复
楼主是要计算每天的结存数量么？
二月十六 2017-07-14
  • 打赏
  • 举报
 回复
试试这个： 
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_所属分类编码
用友U8_12.5按天统计库存 SQL语句
支持用友U8_12.5及以上 根据期初库存与出入库单据统计某日库存量 可实现通过批号或序列号统计，汇总
图书管理信息系统 SIM SQL Server2000数据库管理系统
5 库存统计 分门别类统计图书现有库存量、入库时间等 6 进货记录查询 查询历次进货记录 7 退货登记 登记供应商、图书种类、退货的数量、原因等 8 退货统计 按供应商统计退货记录 9 退货记录查询 查询历次退货记录 10...
基于Java和SQL-Se...的仓库管理系统的设计与开发-司小龙.pdf
在出入库操作中，需要包含入库操作与入库信息记录、出库操作与出库信息记录、库存信息查询操作。在仓库管理中，数据报表起着十分重要的作用，可以根据它掌握整个仓库的出入库情况，从而在对仓库中的药品进行管理时...
里诺仓库管理软件(SQL网络版) V5.84 无限用户破解版
里诺仓库管理软件是一款通用性极强的仓库及货物管理软件，软件适用于食品、... 软件的后台数据库使用的是SQL Server2000,软件具有安全度高,速度快等特点,特别适合数据量大的公司,软件完全可以满足中小型企业的要求。
2021040309-公司配件库存管理系统设计与实现源码.rar
报表统计：提供各类报表，如库存统计、出入库统计、配件销售统计等。 用户管理：管理系统的用户，包括添加、删除、修改用户，以及分配权限。 二、使用技术 后端：使用Java或.NET作为开发语言，Spring Boot或ASP.NET...
疑难问题

22,209

社区成员

121,730

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章