CODEWITHSUNDEEP
sql-server-2005
EvanBlack
EvanBlack

Reputation: 759

How to limit the selection in SQL Server by sum of a column?

Can I limit rows by sum of a column in a SQL Server database?

For example:

Type | Time (in minutes)
-------------------------
A    | 50
B    | 10
C    | 30
D    | 20
E    | 70
...

And I want to limit the selection by sum of time. For example maximum of 100 minutes. Table must look like this:

Type | Time (in minutes)
-------------------------
A    | 50
B    | 10
C    | 30

Any ideas? Thanks.

Upvotes: 1

Views: 1223

Answers (1)

Martin Smith
Martin Smith

Reputation: 453648

DECLARE @T TABLE
(
[Type] CHAR(1) PRIMARY KEY,
[Time] INT
)
INSERT INTO @T
SELECT 'A',50 UNION ALL
SELECT 'B',10 UNION ALL
SELECT 'C',30 UNION ALL
SELECT 'D',20 UNION ALL
SELECT 'E',70;


WITH    RecursiveCTE
AS      (
        SELECT TOP 1 [Type], [Time], CAST([Time] AS BIGINT) AS Total
        FROM @T
        ORDER BY [Type]
        UNION   ALL
        SELECT  R.[Type], R.[Time], R.Total
        FROM    (
                SELECT  T.*,
                        T.[Time] + Total AS Total,
                        rn = ROW_NUMBER() OVER (ORDER BY T.[Type])
                FROM    @T T
                JOIN    RecursiveCTE R
                        ON  R.[Type] < T.[Type]
                ) R
        WHERE   R.rn = 1 AND Total <= 100
        )
SELECT  [Type], [Time], Total
FROM    RecursiveCTE
OPTION  (MAXRECURSION 0);

Or if your table is small

SELECT t1.[Type],
       t1.[Time],
       SUM(t2.[Time])
FROM @T t1
 JOIN @T t2
ON t2.[Type] <= t1.[Type]
GROUP BY t1.[Type],t1.[Time]
HAVING SUM(t2.[Time]) <=100

Upvotes: 2

Related Questions