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
 General SQL Server Forums
 New to SQL Server Programming
 Movement of Qauntities - complicated

Author  Topic 

pvong
Yak Posting Veteran

58 Posts

Posted - 2015-03-03 : 17:37:47
Got a complicated question.

I have TABLE1 as below



Seq Ticker Qty
=== ====== ===
1 msft 50
2 msft 75
3 msft 110


A param will define the qty. So if a user picks 100 WHERE Ticker = 'msft', I would like to INSERT just the first 100 order by the seq INTO TABLE2.

So TABLE2 would look like this

Seq Ticker Qty
=== ====== ===
1 msft 50
2 msft 50


BUT.... Then I need to reduce TABLE1 by that qty so TABLE1 will now look like this. As you can see seq 2 is reduced by 50.


Seq Ticker Qty
=== ====== ===
2 msft 25
3 msft 110


So this will be 2 queries.

Thanks for your help in advance.

------------------------------
Using VS2010 / Learning in VB.Net / Win2008 R2 / SQL 2008 R2
Be kind to the newbies because you were once there.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-03-04 : 13:54:52
Well, I can't do it i only 2 queries, but if that is not a requirement, this might work for you:
declare @param int=100;

select a.seq
,a.ticker
,case when sum(b.qty)>@param then @param-(sum(b.qty)-a.qty) else a.qty end as qty
into #temptable
from table1 as a
inner join table1 as b
on b.ticker=a.ticker
and b.seq<=a.seq
group by a.ticker
,a.seq
,a.qty
having sum(b.qty)-a.qty<@param
;

delete from table1
where exists (select 1
from #temptable
where #temptable.ticker=table1.ticker
and #temptable.seq=table1.seq
and #temptable.qty=table1.qty
)
;

update a
set a.qty=a.qty-b.qty
from table1 as a
inner join #temptable as b
on b.ticker=a.ticker
and b.seq=a.seq
;

update a
set a.qty=a.qty+b.qty
from table2 as a
inner join #temptable as b
on b.ticker=a.ticker
and b.seq=a.seq
;

insert into table2
select *
from #temptable as a
where not exists (select 1
from table2 as b
where b.ticker=a.ticker
and b.seq=a.seq
)
;

drop table #temptable;
Go to Top of Page
   

- Advertisement -