Reputation: 3253
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
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