| 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 |
|
|
jonasalbert20
Constraint Violating Yak Guru
300 Posts |
|
|
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 |
|
|
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 |
|
|
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 |
|
|
|
Remove if sufficient amount