November 19, 2008 at 3:02 am
Hello,
I have the following table:
tempTable
Id TmePeriod
1 5
2 10
3 4
4 8
5 7
In this I want the top records untill the sum of Time Period exceeds 20.
so the output is
Select * from tempTable where (Criteria needed)
Id TmePeriod
1 5
2 10
3 4
4 8
without usig cursor. Can single query give above result?
November 19, 2008 at 4:02 am
You can achieve this with following query:
Select *
From tempTable
Where ID <=
(
select MIN(ID)
from tempTable T1
Where
(
select Sum(TmePeriod) from tempTable T2 Where T2.ID <= T1.ID
) > 20
)
But I'm not sure, this is the most efficient way to do this.
November 19, 2008 at 4:13 am
Can you UPDATE tempTable? If so, you could use the running totals solution:
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply