Query that returns records on total sum of quantity less or equal to some value

DECLARE @TotalMaxQty int
SET @TotalMaxQty = 1000

SELECT
  PKId
  ,Qty
FROM dbo.Sales

PKId          Qty
____          _____
1             100
2             200 
3             750
4             200
...

I have to get 1, 2, 3开发者_如何学Python records because of SUM(Qty) <= @TotalMaxQty (record 3 should be included partially with Qty = 700).

Thank you.

You can use a recursive CTE to calculate the running sum.

declare @Sales table (PKId int, Qty int)

insert into @Sales values
(1,             100),
(2,             200), 
(3,             750),
(4,             200)

declare @TotalMaxQty int = 1000

;with OrderedSales as
(
  select PKId,
         Qty,
         row_number() over(order by PKId) as rn
  from @Sales
  --where "some where clause against Sales"
),
RunningSum as
(
  select OS.PKId,
         case when OS.Qty < @TotalMaxQty then OS.Qty
              else @TotalMaxQty
         end as Qty,
         @TotalMaxQty - OS.Qty as Rest,
         OS.rn
  from OrderedSales OS
  where rn = 1
  union all
  select OS.PKId,
         case when OS.Qty < RS.Rest then OS.Qty
              else RS.Rest
         end as Qty,
         RS.Rest - OS.Qty,
         OS.rn
  from OrderedSales as OS
    inner join RunningSum as RS
      on OS.rn = RS.rn + 1
  where RS.Rest > 0
)
select PKId,
       Qty
from RunningSum
option (maxrecursion 0)

Edit: A version that stores the ordered sales in a table variable with rn as a primary key. My tests shows much improved performance.

declare @Sales table (PKId int, Qty int)

insert into @Sales values
(1,             100),
(2,             200), 
(3,             750),
(4,             200)

declare @TotalMaxQty int = 1000

declare @OrderedSales table
( 
  rn int primary key,
  PKId int,
  Qty int
)

insert into @OrderedSales
select row_number() over(order by PKId),
       PKId,
       Qty
from @Sales
--where "some where clause against Sales"

;with RunningSum as
(
  select OS.PKId,
         case when OS.Qty < @TotalMaxQty then OS.Qty
              else @TotalMaxQty
         end as Qty,
         @TotalMaxQty - OS.Qty as Rest,
         OS.rn
  from @OrderedSales OS
  where rn = 1
  union all
  select OS.PKId,
         case when OS.Qty < RS.Rest then OS.Qty
              else RS.Rest
         end as Qty,
         RS.Rest - OS.Qty,
         OS.rn
  from @OrderedSales as OS
    inner join RunningSum as RS
      on OS.rn = RS.rn + 1
  where RS.Rest > 0

)
select PKId,
       Qty
from RunningSum
option (maxrecursion 0)

Something along these lines should do the trick. (NB: The reason for having the TOP and the subquery is to stop the triangular join calculations as soon as the target is reached)

SELECT  *
FROM    Sales
WHERE   PKId < = ( SELECT TOP 1
                            S1.PKId
                   FROM     Sales S1
                            LEFT JOIN Sales S2 ON S1.PKId >= S2.PKId
                   GROUP BY S1.PKId
                   HAVING   SUM(S2.Qty) >= @TotalMaxQty
                   ORDER BY PKId
                 )

record 3 should be included partially with Qty = 700

That part is a bad idea to do in sql, for two reason: it's just more efficient to do it in client code, and to do this right you'll also want to also update or insert a record somewhere to know how much of that quantity is remaining (meaning you need another difficult query).

But if you insist:

SELECT s.PKId, CASE WHEN PriorTotal + Qty > @TotalMaxQty THEN @TotalMaxQty - PriorTotal ELSE Qty END As Qty
FROM SALES s
INNER JOIN (
   SELECT s1.PKId, Sum(s2.Qty) As PriorTotal 
   FROM SALES s1
   LEFT JOIN SALES s2 ON s2.PKId < s1.PKId
   GROUP BY s1.PKId
) q ON q.PKId = s.PKId
WHERE q.PriorTotal < @TotalMaxQty
ORDER BY s.Qty