Finding first X records that when sumed up meet a criteria (Threshold)

Hi I'm tryin to do a tsql statement that returns the first X records that meet a certain criteria (Threshold):

Say the table has 2 columns: ID & Count

And 2 rows:

ID, Count

1, 10

2, 10

I'm looking to return rows and values until a threshold is reached, so

if threshold is 8, query should return:

ID 1 and Count 8

if theshold i s 15, query should return

ID 1, Count 10

ID 2, Count 5

Any help on this would be appreciated, Thanks!

Hey Zorca. First of all, I'll assume for now that you're running SQL 2000. If you're using SQL 2005, let me know and I'll give you a simpler solution using new SQL 2005 features.

To achieve this, you'd first need to write a query to get the running totals for your given table. You can do this as follows (note that I'm using the table name 'ztmp_table'):

create table dbo.ztmp_table (iid int, cnt int)
go

insert dbo.ztmp_table (iid, cnt) select 1,10
insert dbo.ztmp_table (iid, cnt) select 2,10
insert dbo.ztmp_table (iid, cnt) select 3,10
go

-- Get a running total of the cnt column for the table
select t1.iid, min(t1.cnt) as mycnt, sum(t2.cnt) as runcnt
from ztmp_table t1
join ztmp_table t2
on t2.iid <= t1.iid
group by t1.iid
order by t1.iid
go

Once you have that in place, you then use that query to fulfill the remainder of your mission. I'm going to simplify it by wrapping the above query in a view, removing the order by clause, as follows:

create view dbo.ztmp_runtotals as

-- Get a running total of the cnt column for the table
select t1.iid, min(t1.cnt) as mycnt, sum(t2.cnt) as runcnt
from ztmp_table t1
join ztmp_table t2
on t2.iid <= t1.iid
group by t1.iid
go

Now that I have that view to make simpler code, you can use the following script to see how you can achieve what you're looking for:

declare @.thresh int
set @.thresh = 15

select iid,
case
when runcnt > @.thresh then (@.thresh - (runcnt - mycnt))
when runcnt <= @.thresh then mycnt
end as cnt
from dbo.ztmp_runtotals as tmp1
where tmp1.iid <=
isnull((select min(iid) from dbo.ztmp_runtotals where runcnt >= @.thresh),iid)
order by tmp1.iid

I'm not going to spend a lot of time going into detail on how it works, I'll let you figure that part out, but feel free to repost any questions you may have. Play around with the @.thresh parameter a bit to see the different results you get.

HTH,