根据日期选择记录

debugcn 发表于 Dev

阿丽娜·迪尔沙德（Alina Dilshad）

以下是我的查询，我希望从那些将首先过期的SKU中选择数量。请帮我怎么做。

DECLARE @Data table (Id int identity(1,1)
, SKU varchar(10)
, QtyRec int
, Expiry date
, Rec date)
DECLARE @Qty int = 20

INSERT @Data VALUES
('001A', 5 ,'2017-01-15','2015-11-14'),
('001A', 8 ,'2017-01-10','2015-11-14'),
('001A', 6 ,'2015-12-15','2015-11-15'),
('001A', 25,'2016-01-01','2015-11-16'),
('001A', 9 ,'2015-12-20','2015-11-17')

;WITH sumqty AS 
(
 SELECT *, SUM(QtyRec) OVER (PARTITION BY SKU ORDER BY Id) AS TotalQty FROM @Data
)
,takeqty AS (
SELECT *, 
CASE 
  WHEN @Qty >= TotalQty THEN QtyRec 
  ELSE @Qty - ISNULL(LAG(TotalQty) OVER (PARTITION BY SKU ORDER BY Id), 0)
END AS TakeQty
FROM sumqty
)
SELECT * FROM takeqty WHERE TakeQty > 0

火星

有许多可能的结果取决于您的问题：

如果要基于到期日的升序对结果集进行排序，请ORDER BY Expiry在CTE查询末尾使用。或选择最高过期时间：使用SELECT TOP(1) ROW_NUMBER() OVER(ORDER BY Expiry) AS 'SN'代替SELECT ROW_NUMBER() OVER(ORDER BY Expiry) AS 'SN'

DECLARE @Data table (Id int identity(1,1)
, SKU varchar(10)
, QtyRec int
, Expiry date
, Rec date)
DECLARE @Qty int = 20

INSERT @Data VALUES
('001A', 5 ,'2017-01-15','2015-11-14'),
('001A', 8 ,'2017-01-10','2015-11-14'),
('001A', 6 ,'2015-12-15','2015-11-15'),
('001A', 25,'2016-01-01','2015-11-16'),
('001A', 9 ,'2015-12-20','2015-11-17');

WITH sumqty AS 
(
 SELECT *, SUM(QtyRec) OVER (PARTITION BY SKU ORDER BY Id) AS TotalQty FROM @Data
)
,takeqty AS (
SELECT *, 
CASE 
  WHEN @Qty >= TotalQty THEN QtyRec 
  ELSE @Qty - ISNULL(LAG(TotalQty) OVER (PARTITION BY SKU ORDER BY Id), 0)
END AS TakeQty
FROM sumqty
)
SELECT ROW_NUMBER() OVER(ORDER BY Expiry) AS 'SN'
    ,   SKU
    ,   QtyRec
    ,   Expiry
    ,   Rec
    ,   TotalQty
    ,   TakeQty
FROM takeqty WHERE TakeQty > 0
ORDER BY Expiry;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。