CODEWITHSUNDEEP
sqlsql-servert-sql
RMD
RMD

Reputation: 3253

TSQL Filter by partial aggregate

SQL Server 2005+.

I have a table with the following schema/data: 

Name   Slots    Date
---------------------
Bob     1       1/1/2010 
Bob     2       1/8/2010 
Joe     4       1/2/2010 
Nat     1       1/4/2010 
Nat     3       1/3/2010 
Nat     8       1/9/2010

What I need to do is find the row which for a particular name that results in the sum of slots of that name reaching or exceeding an arbitrary value - let's say 3.

So in the above example, Bob has two rows. The second row, with a date of 1/8/2010, would be my match as the sum of 1 and 2 = 3.

Joe only has one row, and he would match as 4 is already greater than 3.

Nat has 3 rows, and in his case the matching row is the 2nd row with a date of 1/3/2010 since the sum of 1 and 3 is 4, and 4 is greater than 3.

So my results are:

Name   SlotsSum    Date
-------------------------
Bob     3       1/8/2010 
Joe     4       1/2/2010 
Nat     4       1/3/2010

I know I can do this with a cursor, but I'm hoping for a set-based approach.

Thoughts?

Upvotes: 1

Views: 383

Answers (1)

Bert
Bert

Reputation: 82499

Using two techniques, we can do what you want. CROSS APPLY to calculate a running total, and ROW_NUMBER so we can select the first row over your limit.

declare @MyTable table (Name varchar(10), Slots int, Date date)
insert @MyTable values
('Bob',     1 ,      '1/1/2010'),
('Bob',     2 ,      '1/8/2010'), 
('Joe',     4 ,      '1/2/2010 '),
('Nat',     1 ,      '1/4/2010 '),
('Nat',     3 ,      '1/3/2010 '),
('Nat',     8 ,      '1/9/2010 ')

declare @Limit int = 3

;with cte as
(
    select Name, Slots, Date, RunningTotal,
    ROW_NUMBER() over (partition by Name order by Name, Date) as rn
    from @MyTable rsTable
    cross apply 
    (
        select SUM(Slots) as RunningTotal
        from @MyTable rsApply
        where Date <= rsTable.Date and Name = rsTable.Name
    ) rsRunningTotal
    where RunningTotal >= @Limit

)
select Name, RunningTotal, Date
from cte 
where rn = 1

Upvotes: 2

Related Questions