Top However Untill - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Top However Untill

Author

Topic

ConradK
Posting Yak Master

140 Posts

Posted - 2010-07-02 : 18:04:04

So I want to return a result set until the sum of the values of a column is = a defined value.

Example

sku cost
a 1
b 1
c 3
d 6
e 3
f 9

if I say return up to 5 dollars, then it woudl return a,b, and c. If I say return up to 14 dollars it would return a, b, c, d and e.

Get it? I can think of some really long while loops that would bog the system, but is there something easier?

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-07-03 : 01:35:40

Try this script,

create table #Temp (Col1 char(2) , Val int)
insert into #Temp values('a',1)
insert into #Temp values('b', 1)
insert into #Temp values('c', 3)
insert into #Temp values('d', 6)
insert into #Temp values('e', 3)
insert into #Temp values('f', 9)

select * from #Temp

select O.Col1,
O.Val,
(select sum(Val) from #Temp
where Col1 <= O.Col1 or
Col1 = O.Col1)
'Running Total'
from #Temp O
where (select sum(Val) from #Temp
where Col1 <= O.Col1 or
Col1 = O.Col1)<=5
order by Col1

SQL Server Programmers and Consultants
http://www.sql-programmers.com/