problem with select statement in RunningTotals

Question

problem with select statement in RunningTotals

preetha.sathyamoorthy

Ten Centuries

Points: 1180
More actions
May 5, 2008 at 3:44 am

#190160

Declare NValue int
Set NValue =40
create table RTable
(
ID int NULL,
Number int NULL
)
insert into RTable
select 1,5
union
select 2,19
union
select 3,15
union
select 4,10
union
select 5,20
union
select 6,10
union
select 7,15
select * from Rtable
ID Number
1 5
2 19
3 15
4 10
5 20
6 10
7 15
I Have to select the only the rows where the sum(Number)< NValue
My Expected output is
ID Number
1 5
2 19
3 15
Is there any idea to select the expected values without using Loops
Is it possible to use any of the Tally Table,CTE or RunningTotal for this scenario?
Awaiting your Results,
Regards,
Preetha

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 1

[font="Verdana"]You havn't posted the exact example. Coz you stated, you need summation of the number table, but on the basis of your example on what basis you need summation?

If you need the below o/p:

My Expected output is

ID Number

1 5

2 19

3 15

simpley you could try this:

Select ID, Number From Table1 Where Number < 20

assuming the value for NValue is 20

If possible, post the real life example with exact expected o/p

confirm on this.

Mahesh

[/font]

MH-09-AM-8694

preetha.sathyamoorthy Ten Centuries Points: 1180 More actions · Answer 2

Hi Mahesh,

Thanks for your valuable Response and What i mean to explain is

Summation of ID (1+2+3) < NValue (If NValue=40 , Display first 3 Rows)

and summation of ID (1+2+3+4) < NValue (if nValue =50, Display first 4 Rows)

I Need a query which only displays these Rows starting from ID = 1 which satisfies the NValue condition.

Thanks,

Preetha

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 3

[font="Verdana"]Ohh... I got it. Hopes the below url will help you out.

http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

you need to just add your condition there.

Mahesh

[/font]

MH-09-AM-8694

preetha.sathyamoorthy Ten Centuries Points: 1180 More actions · Answer 4

Thanks Mahesh,

I got the Idea from that Article and I hope will do the things succesfully

Thanks a lot

Bye

Keith DuAime Ten Centuries Points: 1129 More actions · Answer 5

Or save yourself the trouble and solve it with the information you already have

SELECT RTable.*

FROM RTable

INNER JOIN (

SELECT MAX(TheId.id) MaxId

FROM RTable TheId

INNER JOIN RTable TheSum ON TheSum.id <= TheId.ID

GROUP BY TheId.id

HAVING SUM(TheSum.Number) <= @NValue

) MaxId ON

MaxId.MaxId = RTable.id