Remove if sufficient amount - 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)

Remove if sufficient amount

Author

Topic

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2009-05-20 : 22:12:46

Good day to all...

Could someone help me on this... Any help would be highly appreciated. Thanks in advance.

I have this data...


select tblTotal.id, tblTotal.total,
	tblRemove.removed
from (
	SELECT 1 as id, 100 as total union all
	SELECT 2 as id, 2000 as total union all
	SELECT 4 as id, 1500 as total
     ) as tblTotal
inner join (
	select 1 as id, 100 as removed union all
	select 1 as id, 150 as removed union all
	select 3 as id, 100 as removed union all
	select 4 as id, 350 as removed union all
	select 4 as id, 470 as removed union all
	select 4 as id, 1270 as removed 
	  ) as tblRemove on tblRemove.id = tblTotal.id

What I want is to minus the removed column to the total column only if it has still sufficient amount to removed.
Desired result is this...


select 1 as id, 100 as total, 100 as removed union all
select 4 as id, 1500 as total, 350 as removed union all
select 4 as id, 1500 as total, 470 as removed

Notice that 150 and 1270 was not shown because theres no sufficient amount to removed.

thanks in advance guys.

For fast result follow this...
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Want Philippines to become 1st World COuntry? Go for World War 3...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-20 : 22:18:30

what's the criteria to remove ? starts from smaller amount to bigger amount ?

any other condition ? like combination of remove amount to maximize the amount to be removed ?

KH
[spoiler]Time is always against us[/spoiler]

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2009-05-20 : 22:23:12

thanks for the followup question. from bigger amount to smaller. No combination of amount... I just want to display the removed column if it still have sufficient amount. thanks.

For fast result follow this...
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Want Philippines to become 1st World COuntry? Go for World War 3...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-21 : 01:19:14

See http://weblogs.sqlteam.com/peterl/archive/2008/08/12/How-to-sum-up-an-unknown-number-of-records.aspx
and http://weblogs.sqlteam.com/peterl/archive/2008/11/23/Bin-packaging.aspx

E 12°55'05.63"
N 56°04'39.26"

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2009-05-21 : 02:14:42

Can it be done without using a cursor?

For fast result follow this...
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Want Philippines to become 1st World COuntry? Go for World War 3...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-21 : 02:36:11

How do you define the order of records for the "removed table"?

E 12°55'05.63"
N 56°04'39.26"

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2009-05-21 : 05:11:17

Order by which ever is the highest amount to be removed.

For fast result follow this...
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Want Philippines to become 1st World COuntry? Go for World War 3...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-21 : 07:42:07

So this is also a valid result?

select 1 as id, 100 as total, 100 as removed union all
select 4 as id, 1500 as total, 1270 as removed

E 12°55'05.63"
N 56°04'39.26"

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2009-05-21 : 20:38:21

quote:
Originally posted by Peso

So this is also a valid result?

select 1 as id, 100 as total, 100 as removed union all
select 4 as id, 1500 as total, 1270 as removed

E 12°55'05.63"
N 56°04'39.26"

Yes... :-) thanks!

Please help me. thanks!

For fast result follow this...
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Want Philippines to become 1st World COuntry? Go for World War 3...

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2009-05-22 : 20:16:22

Any help will be greatly appreciated...

For fast result follow this...
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Want Philippines to become 1st World COuntry? Go for World War 3...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-25 : 21:09:16

[code]
select tblTotal.id, tblTotal.total,
tblRemove.removed
into data
from
(
SELECT 1 as id, 100 as total union all
SELECT 2 as id, 2000 as total union all
SELECT 4 as id, 1500 as total
) as tblTotal
inner join
(
select 1 as id, 100 as removed union all
select 1 as id, 150 as removed union all
select 3 as id, 100 as removed union all
select 4 as id, 350 as removed union all
select 4 as id, 470 as removed union all
select 4 as id, 1270 as removed
) as tblRemove on tblRemove.id = tblTotal.id

select id, total, removed
from
(
select *, total_removed = (select sum(removed) from data x where x.id = d.id and x.removed <= d.removed)
from data d
) d
where d.total_removed <= d.total

drop table data
[/code]

KH
[spoiler]Time is always against us[/spoiler]

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2009-05-26 : 23:51:56

Thanks KH :-)

For fast result follow this...
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Want Philippines to become 1st World COuntry? Go for World War 3...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources