CODEWITHSUNDEEP
sqlsql-serverdatabase
etaskin
etaskin

Reputation: 97

Get rows in SQL by summing up a until certain value is exceeded and stop retrieving

I have to return rows from the database when the value exceeds a certain point.

I should get enough rows to sum up to a value that is greater than my quantity and stop retrieving rows.

Is this possible and does it makes sense? Can this be transferred into LINQ for EF core?

I am currently stuck with query that will return all the rows...

SELECT [i].[InventoryArticleId], [i].[ArticleId], [i].[ArticleQuantity], [i].[InventoryId]
      FROM [InventoryArticle] AS [i]
      INNER JOIN [Article] AS [a] ON [i].[ArticleId] = [a].[ArticleId]
      WHERE (([i].[ArticleId] = 1) AND ([a].[ArticlePrice] <= 1500)) 
      AND ((
          SELECT COALESCE(SUM([i0].[ArticleQuantity]), 0)
          FROM [InventoryArticle] AS [i0]
          INNER JOIN [Article] AS [a0] ON [i0].[ArticleId] = [a0].[ArticleId]
          WHERE ([i0].[ArticleId] = 1) AND ([a0].[ArticlePrice] < 1500)) > 10)

Results for querry

Expected result is one row. If number would be greater than 34, more rows should be added. Expected result

Upvotes: 1

Views: 4032

Answers (2)

Charlieface
Charlieface

Reputation: 71638

You can use a windowed SUM to calculate a running sum ArticleQuantity. It is likely to be far more efficient than self-joining.

The trick is that you need all rows where the running sum up to the previous row is less than the requirement.

You could utilize a ROWS clause of ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING. But then you need to deal with possible NULLs on the first row.

In any event, even a regular running sum should always use ROWS UNBOUNDED PRECEDING, because the default is RANGE UNBOUNDED PRECEDING, which is subtly different and can cause incorrect results, as well as being slower.

DECLARE @requirement int = 10;

SELECT
  i.InventoryArticleId,
  i.ArticleId,
  i.ArticleQuantity,
  i.InventoryId
FROM (
    SELECT
      i.*,
      RunningSum = SUM(i.ArticleQuantity) OVER (PARTITION BY i.ArticleId ORDER BY i.InventoryArticleId ROWS UNBOUNDED PRECEDING)
    FROM InventoryArticle i
    INNER JOIN Article a ON i.ArticleId = a.ArticleId
    WHERE i.ArticleId = 1
      AND a.ArticlePrice <= 1500
) i
WHERE i.RunningSum - i.ArticleQuantity < @requirement;

You may want to choose a better ordering clause.

EF Core cannot use window functions, unless you specifically define a SqlExpression for it.

Upvotes: 4

T N
T N

Reputation: 10024

My approach would be to:

  1. Filter for the eligible records.
  2. Calculate the running total.
  3. Identify the first record where the running total satisfies your criteria.
  4. Perform a final select of all eligible records up to that point.

Something like the following somewhat stripped down example:

-- Some useful generated data
DECLARE @Inventory TABLE (InventoryArticleId INT, ArticleId INT, ArticleQuantity INT)
INSERT @Inventory(InventoryArticleId, ArticleId, ArticleQuantity)
SELECT TOP 1000
    InventoryArticleId = N.n,
    ArticleId = N.n % 5,
    ArticleQuantity = 5 * N.n
FROM (
    -- Generate a range of integers
    SELECT n =  ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n
    FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),
         (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n),
         (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) hundreds(n),
         (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) thousands(n)
    ORDER BY 1
) N
ORDER BY N.n

SELECT * FROM @Inventory

DECLARE @ArticleId INT = 2
DECLARE @QuantityNeeded INT = 500

;
WITH isum as (
    SELECT i.*, runningTotalQuantity = SUM(i.ArticleQuantity) OVER(ORDER BY i.InventoryArticleId)
    FROM @Inventory i
    WHERE i.ArticleId = @ArticleId
)
SELECT isum.*
FROM (
    SELECT TOP 1 InventoryArticleId
    FROM isum
    WHERE runningTotalQuantity >= @QuantityNeeded
    ORDER BY InventoryArticleId
) selector
JOIN isum ON isum.InventoryArticleId <= selector.InventoryArticleId
ORDER BY isum.InventoryArticleId

Results:

InventoryArticleId ArticleId ArticleQuantity runningTotalQuantity
2 2 10 10
7 2 35 45
12 2 60 105
17 2 85 190
22 2 110 300
27 2 135 435
32 2 160 595

All of the ORDER BY clauses in the running total calculation, selector, and final select must be consistent and unambiguous (no dups). If a more complex order or preference is needed, it may be necessary to assign a rank value the eligible records before calculating the running total.

Upvotes: 0

Related Questions