求一个库存覆盖周期的写法

lsh902 2014-06-16 11:13:50
库存表
year_month product_code data_type inv
201401 C111 AAA 100
201401 D112 BBB 120
201402 C111 AAA 160
201402 D112 BBB 220
201402 C111 AAA 360
201402 D112 BBB 320


库存需求表
year_month product_code data_type inv_f
201401 C111 AAA 20
201401 D112 BBB 30
201402 C111 AAA 20
201402 D112 BBB 60
201403 C111 AAA 50
201403 D112 BBB 20
201404 C111 AAA 20
201405 C111 AAA 15
201405 D112 BBB 10
如上两表，库存表记录当前库存，库存需求表为每月产品的需求，求当月库存能覆盖几个月，举例：如201401月C111库存为100,
201401月库存需求20,即100-20=80,
201402月库存需求20，即80-20=60，
201403月库存需求50，即60-50=10，
201404月库存需求20，即10-20=-10，无法满足需求。
即201401月库存只能覆盖3个月，周期为3.
求覆盖周期的SQL server的写法。
...全文
917 8 打赏 收藏 转发到动态 举报
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
Tiger_Zhao 2014-11-18
  • 打赏
  • 举报
 回复 
/* http://bbs.csdn.net/topics/390813196

*/

WITH store(year_month,product_code,data_type,inv) AS (

    SELECT 201401,'C111','AAA',100 UNION ALL

    SELECT 201401,'D112','BBB',120 UNION ALL

    SELECT 201402,'C111','AAA',160 UNION ALL

    SELECT 201402,'D112','BBB',220 UNION ALL

    SELECT 201402,'C111','AAA',360 UNION ALL

    SELECT 201402,'D112','BBB',320

)

,require(year_month,product_code,data_type,inv_f) AS (

    SELECT 201401,'C111','AAA',20 UNION ALL

    SELECT 201401,'D112','BBB',30 UNION ALL

    SELECT 201402,'C111','AAA',20 UNION ALL

    SELECT 201402,'D112','BBB',60 UNION ALL

    SELECT 201403,'C111','AAA',50 UNION ALL

    SELECT 201403,'D112','BBB',20 UNION ALL

    SELECT 201404,'C111','AAA',20 UNION ALL

    SELECT 201405,'C111','AAA',15 UNION ALL

    SELECT 201405,'D112','BBB',10

)

,m AS (

    SELECT DISTINCT year_month FROM require

)

        SELECT *

          FROM store s

   CROSS APPLY (

                SELECT max(year_month)-s.year_month+1 inv_cyc --如果有跨年要转日期用DateDiff算

                  FROM ( --从s.year_month 开始，按月累积需求

                            SELECT m.year_month,

                                   r.product_code,

                                   SUM(r.inv_f) inv_f

                              FROM m

                              JOIN require r

                                ON r.year_month <= m.year_month

                             WHERE m.year_month >= s.year_month

                               AND r.year_month >= s.year_month

                               AND r.product_code = s.product_code

                          GROUP BY m.year_month, r.product_code

                       ) tt

                 WHERE tt.inv_f <= s.inv

                   AND tt.product_code = s.product_code

               ) t

      ORDER BY s.year_month,s.product_code

 year_month product_code data_type         inv     inv_cyc

----------- ------------ --------- ----------- -----------

     201401 C111         AAA               100           3

     201401 D112         BBB               120           5

     201402 C111         AAA               360           4

     201402 C111         AAA               160           4

     201402 D112         BBB               320           4

     201402 D112         BBB               220           4

补充：中间断档的需求的说明。
比如对库存 201401,D112,BBB,120 的覆盖统计为例：
假如把 201403,D112 的需求数该为 30，让3月份累积需求正好是120；
由于没有 201404,D112 这条需求，那么4月份的累积需求也算成120，算覆盖；
201405,D112有需求10，累积需求130；
所以这时覆盖月数是4而不是3。
十林 2014-07-15
  • 打赏
  • 举报
 回复
以下代码的前提是对于同一个data_type,product_code在需求表 和 库存表 中的需求 和 库存在时间上是连续的。 因为使用with CTE 迭代需要数据；连续性，如果不连续你得用row_number()这个函数来处理成连续数据。 
/****** Object:  Table [dbo].[_temp_inventory]    Script Date: 07/15/2014 19:57:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[_temp_inventory](
	[year_month] [nvarchar](100) NULL,
	[product_code] [nvarchar](100) NULL,
	[data_type] [nvarchar](100) NULL,
	[inv] [nvarchar](100) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[_temp_inventory] ([year_month], [product_code], [data_type], [inv]) VALUES (N'201401', N'C111', N'AAA', N'100')
INSERT [dbo].[_temp_inventory] ([year_month], [product_code], [data_type], [inv]) VALUES (N'201401', N'D112', N'BBB', N'120')
INSERT [dbo].[_temp_inventory] ([year_month], [product_code], [data_type], [inv]) VALUES (N'201402', N'C111', N'AAA', N'160')
INSERT [dbo].[_temp_inventory] ([year_month], [product_code], [data_type], [inv]) VALUES (N'201402', N'D112', N'BBB', N'220')
INSERT [dbo].[_temp_inventory] ([year_month], [product_code], [data_type], [inv]) VALUES (N'201403', N'C111', N'AAA', N'360')
INSERT [dbo].[_temp_inventory] ([year_month], [product_code], [data_type], [inv]) VALUES (N'201403', N'D112', N'BBB', N'320')
/****** Object:  Table [dbo].[_temp_Demand]    Script Date: 07/15/2014 19:57:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[_temp_Demand](
	[year_month] [nvarchar](100) NULL,
	[product_code] [nvarchar](100) NULL,
	[data_type] [nvarchar](100) NULL,
	[inv_f] [nvarchar](100) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[_temp_Demand] ([year_month], [product_code], [data_type], [inv_f]) VALUES (N'201401', N'C111', N'AAA', N'20')
INSERT [dbo].[_temp_Demand] ([year_month], [product_code], [data_type], [inv_f]) VALUES (N'201401', N'D112', N'BBB', N'30')
INSERT [dbo].[_temp_Demand] ([year_month], [product_code], [data_type], [inv_f]) VALUES (N'201402', N'C111', N'AAA', N'20')
INSERT [dbo].[_temp_Demand] ([year_month], [product_code], [data_type], [inv_f]) VALUES (N'201402', N'D112', N'BBB', N'60')
INSERT [dbo].[_temp_Demand] ([year_month], [product_code], [data_type], [inv_f]) VALUES (N'201403', N'C111', N'AAA', N'50')
INSERT [dbo].[_temp_Demand] ([year_month], [product_code], [data_type], [inv_f]) VALUES (N'201403', N'D112', N'BBB', N'20')
INSERT [dbo].[_temp_Demand] ([year_month], [product_code], [data_type], [inv_f]) VALUES (N'201404', N'C111', N'AAA', N'20')
INSERT [dbo].[_temp_Demand] ([year_month], [product_code], [data_type], [inv_f]) VALUES (N'201405', N'C111', N'AAA', N'15')
INSERT [dbo].[_temp_Demand] ([year_month], [product_code], [data_type], [inv_f]) VALUES (N'201405', N'D112', N'BBB', N'10')


/****** Script for SelectTopNRows command from SSMS  ******/
SELECT *
FROM   [dbo].[_temp_inventory]
ORDER  BY product_code,
          year_month

SELECT *
FROM   _temp_Demand
ORDER  BY product_code,
          year_month;

WITH CTE([year_month], [year_month2], [product_code], [data_type], [inv_original], [inv], cnt)
     AS (SELECT     A.[year_month],
                    A.[year_month] AS [year_month2],
                    A.[product_code],
                    A.[data_type],
                    A.[inv] * 1    AS [inv_original],
                    A.[inv] * 1 - B.inv_f,
                    CASE WHEN a.[inv]*1-b.inv_f*1>0 THEN 1 ELSE 0 END --确认单前的库存是否满足需求，满足+1，不满足+0
         FROM      [dbo].[_temp_inventory] A
         INNER JOIN [dbo].[_temp_Demand] B
                 ON a.[data_type] = b.[data_type] AND A.[product_code] = B.[product_code]
                  AND A.[year_month] = B.[year_month]
         UNION ALL
         SELECT b.[year_month],
                a.[year_month]       AS [year_month2],
                b.[product_code],
                b.[data_type],
                b.[inv_original] * 1 AS [inv_original],
                b.[inv] * 1 - a.inv_f * 1,--当前剩余库存
                b.cnt + CASE WHEN b.[inv]*1-a.inv_f*1>0 THEN 1 ELSE 0 END
         FROM   [dbo].[_temp_Demand] A,
                CTE B
         WHERE  a.[data_type] = b.[data_type] AND A.[product_code] = B.[product_code] 
         AND A.[year_month] = B.[year_month2] + 1 AND b.[inv] * 1 - a.inv_f * 1 > 0)
SELECT     A.[year_month],
           A.[product_code],
           A.[data_type],
           A.cnt
FROM       cte A
INNER JOIN (SELECT [year_month],
                   [product_code],
                   [data_type],
                   MAX(cnt) AS CNT
            FROM   CTE
            GROUP  BY [year_month],
                      [product_code],
                      [data_type]) B
        ON a.[data_type] = b.[data_type] AND A.[product_code] = B.[product_code] AND A.[year_month] = B.[year_month] AND a.cnt = b.cnt
ORDER      BY 3, 1
飞啊子 2014-06-18
  • 打赏
  • 举报
 回复
麻烦 楼主提供建表 插入数据。。 弄数据是最麻烦的，分数太少真心懒的弄。
lsh902 2014-06-18
  • 打赏
  • 举报
 回复
引用 3 楼 fredrickhu 的回复:
先

select
	year_month,product_code,data_type,
 (select 
	sum(inv_f) as inv_f
  from 
	库存需求表 where product_code=t.product_code and data_type=t.data_type and year_month<=t.year_month)  as inv_f
from 
 库存需求表 as t
你看得到的结果 然后自己再去做吧 哥只能帮你到这了
小弟，还不是没明白，求进一步解释或思路。
--小F-- 2014-06-17
  • 打赏
  • 举报
 回复 
先

select
	year_month,product_code,data_type,
 (select 
	sum(inv_f) as inv_f
  from 
	库存需求表 where product_code=t.product_code and data_type=t.data_type and year_month<=t.year_month)  as inv_f
from 
 库存需求表 as t
你看得到的结果 然后自己再去做吧 哥只能帮你到这了
lsh902 2014-06-17
  • 打赏
  • 举报
 回复
获得这样的结果 year_month product_code data_type inv inv_cyc 201401 C111 AAA 100 3 201401 D112 BBB 120 3 201402 C111 AAA 160 4 201402 D112 BBB 220 4 201402 C111 AAA 360 4 201402 D112 BBB 320 4
唐诗三百首 2014-06-17
  • 打赏
  • 举报
 回复
请问LZ希望的结果格式是什么?
2023年信息系统项目管理师论文专题
本课程针对2023年高项信息系统项目管理师论文考试，是从十大领域的角度，教会学员论文写作。以每次都至少必考一个十大知识领域的题目为基础对论文从“审题+框架+写法+细节+对比+范文”进行详细解读，详细说明如何写好该领域的论文，帮助学员解决论文写作这一难题，保障学员能够顺利通过论文考试。考前进行论文押题，已经连续10多次押中论文题目。
单元测试框架和覆盖率统计原理简析
单元测试又是软件研发过程中的重要一环，此文可以帮助理解单元测试插件的运行过程，了解 mock 框架以及平台覆盖率统计相关的原理，从而更好更快地编写单元测试。 作者 |忠素 一、背景介绍 最近部门在推进质量标准化，通过标准化研发、交付、部署、运维等过程，减少缺陷率和返工率，提高整体的工作效率。而单元测试又是软件研发过程中的重要一环，此文可以帮助理解单元测试插件的运行过程，了解 mock 框架以及平台覆盖率统计相关的原理，从而更好更快地编写单元测试。 二、 单元测试与敏捷开发 在常规的测试环节中，可.
java面试题全覆盖
java面试题全覆盖
Java 10 新特性介绍
作为当今使用最广泛的编程语言之一的 Java 在 2018 年 3 月 21 日发布了第十个大版本。为了更快地迭代、更好地跟进社区反馈，Java 语言版本发布周期调整为每隔 6 个月发布一次。Java 10 是这一新规则之后，采用新发布周期的第一个大版本。Java 10 版本带来了很多新特性，其中最备受广大开发者关注的莫过于局部变量类型推断。除此之外，还有其他包括垃圾收集器改善、GC 改进、性能提...
200316&菜鸡前端工作卡壳梳理汇总
卡壳的地方(不能直接写的都算) 思路不清； 还需要想一想； 还需要搜搜； 还需要找参考的地方 … setState的写法 this.state.setState({});//错误写法 this.setState({});//正确写法 如何获取antd的form组件的formItem的被getFieldDecorator包括的值 this.props.form.getFieldValue("rangePicker") 卡在多重数据的遍历绑定上(绑定列表的id) 前置:获取数据有时间差,还是生命周期函数
community_281

593

社区成员

254,048

社区内容

发帖
与我相关
我的任务
社区描述
提出问题
其他 技术论坛（原bbs）
社区管理员
  • community_281
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

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

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

+ 用AI写文章