title: Selection of records on bases of date
body: <p>Below is my query and I want the selection of Qty from those SKU which is going to expire first. Please help me how can I do this.</p>
  
  <pre><code>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 &gt;= 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 &gt; 0
  </code></pre>