CODEWITHSUNDEEP
sql-servert-sql
user1452574
user1452574

Reputation: 485

How can you query based on the cumulative sum of one column?

So suppose I have the following table:

enter image description here

Now suppose the query for that is:

SELECT
    JobNo,
    WorkCenter,
    EstimMinutes
FROM Schedule
WHERE WorkCenter = 'Grinding'

Now suppose Grinding's daily capacity is 500 daily minutes, so if I were to hand them a schedule for the present day, I only want to show jobs up until the job that goes over the 500 minute mark. So in this case, I want to return only the rows highlighted in green, the total of which is 505 total estimated minutes. How would I go about doing this?

I tried doing:

WHERE SUM(EstimMinutes) < 500

But that obviously doesn't work. Not really sure if it's possible to do this, and if so, what direction I should be going in. Any help is greatly appreciated

Upvotes: 1

Views: 41

Answers (3)

BugCatcherJoe
BugCatcherJoe

Reputation: 497

You can add another column and have it's values be the running sum of the EstimMinutes and then filter by the calculated column.

SELECT t.JobNo, t.WorkCenter, t.EstimMinutes
FROM (
    SELECT JobNo, WorkCenter, EstimMinutes,
    SUM(EstimMinutes) OVER (ORDER BY JobNo) AS RunningTotal
    FROM Schedule
    WHERE WorkCenter = 'Grinding'
    AND RunningTotal <= 500
) as t

Upvotes: 0

Angel M.
Angel M.

Reputation: 1358

I would propose you a solution based in Common Table Expressions:

WITH CTE AS (
SELECT
    JobNo,
    WorkCenter,
    EstimMinutes,
    SUM(EstimMinutes) OVER (PARTITION BY WorkCenter 
                            ORDER BY JobNo) AS CumulatedMinutes
FROM Schedule
WHERE WorkCenter = 'Grinding')
SELECT * 
FROM CTE
WHERE CumulatedMinutes < 500;

Upvotes: 4

Cato
Cato

Reputation: 3701

quickest way is to use a sub-query, you could also use CROSS APPLY if you wanted to show the cummulative value. Note here, I'm only adding up the previous jobs in the check, I'm not adding the current job, because we do want to show the job that exceeds 500

SELECT t1.* from table t1
             where 500 >=
                     (SELECT SUM(EstimMinutes) X
                                             FROM table t2
                                             WHERE t2.JobNo<t1.jobno 
                                                 and t1.workcentre = t2.workcentre)

Upvotes: 0

Related Questions