Help Urgent! - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Help Urgent!

Author

Topic

mibheenick
Starting Member

12 Posts

Posted - 2004-09-16 : 06:40:47

Hello everyone

can anyone help me wiz this one plz....here's the problem!

say i have a table PAYMENT as follows:
Id Name Amount
---------- ----------- ---------
3 test1 300
4 test2 400
5 test3 200
6 test4 150
8 test5 500
15 test6 125
44 test7 600

I need a single SELECT statement doing the following...i've tried GROUP BY, HAVING but still nothing!

SELECT * FROM PAYMENT
WHERE sum(Amount) < 1000

and the result should be
Id Name Amount
---------- ----------- ---------
3 test1 300
4 test2 400
5 test3 200

thx

I leave it to god.....

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-09-16 : 06:51:03

do you mean you want the first "few" records till the sum < 1000

Hemanth Gorijala
BI Architect / DBA...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-16 : 06:51:52

and on what criteria do you want it to sum up?

Go with the flow & have fun! Else fight the flow

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-16 : 06:58:48

based on your criteria (which is none

)


select * 
from
(select *, amount + COALESCE((SELECT SUM(amount) 
              			  FROM PAYMENT
              			  WHERE id < t1.id),0)
                 AS Amount1
from PAYMENT t1) t
where amount1 < 1000

Go with the flow & have fun! Else fight the flow

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-16 : 07:02:25

[code]
create table #payment(id int,name varchar(10),amount int)
insert #payment
select 3, 'test1', 300
union select 4, 'test2', 400
union select 5, 'test3', 200
union select 6, 'test4', 150
union select 8, 'test5', 500
union select 15, 'test6', 125
union select 44, 'test7', 600

-- with where
select p.id,
( select sum(r.amount) from #payment r where p.id >= r.id ) as running_total
from #payment p
where ( select sum(r.amount) from #payment r where p.id >= r.id ) < 1000

-- with having
select p.id,
sum(r.amount) as running_total
from #payment p
join #payment r on p.id >= r.id
group by p.id
having sum(r.amount) < 1000

drop table #payment
[/code]

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-16 : 07:26:44

if i understand correctly,
you want to show groups of the records whose sum of amount<1000,

so the result would be, more than one group? you can have
3,4,5,6,7,8,15,44,{3,4},{6,8},{15,44}, etc....

IMHO, you need to loop through this...

btw, where do you apply this type of transaction? just curious, coz why pass this to sql and not just process the info in your appl?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources