sql server - SQL - add records to temp table until order is fulfilled -
what ways tackle scenario? want select enough records table b support table , want use oldest newest table b.
table a:
ordernum partnum quantity ship date ----------------------------------------------- 1 xyz 15 7/1/2014 2 xyz 15 8/1/2014 table b:
lotid partnum quantity dateent ----------------------------------------------- 1 xyz 10 1/1/2014 2 xyz 10 2/1/2014 3 xyz 10 3/1/2014 results:
ordernum partnum quantity lotid lotquantity -------------------------------------------------------- 1 xyz 15 1 10 1 xyz 15 2 5 2 xyz 15 2 5 2 xyz 15 3 10 this i've tried
select ordernum,case when lotqty < (orderqty - prevlotqty) lotqty else orderqty - prevlotqty end needed,lotid (select top 1000 ordernum,tv.orderqty,lotqty, previqty = isnull((select sum(qty) tableb partnum = tb.partnum , dateent < tb.dateent),0) tablea ta inner join tableb tb on ta.partnum = tableb.partnum tablb.qty > 0 order dateent) baseqry orderqty - prevlotqty > 0 this works 1 order, reuses same records table b if there more orders on same part.
like said in oppinion better solve on buisness logic side if whant on server side reason (nightjob,...) script. keep in mind preformance problems , other things pointed out in comments. c# based solution blam's answer here sql aproach that
create table taba (ordernum numeric(1), partnum varchar(3), quntity numeric(2), shipdate datetime) create table tabb (lotid numeric(1), partnum varchar(3), quntity numeric(2), dateent datetime) create table tabc (ordernum numeric(1), partnum varchar(3), quntity numeric(2), lotid numeric(1), lotquntity numeric(2)) insert taba values(1,'xyz',15,'20140107'), (2,'xyz',15,'20140107') insert tabb values(1,'xyz',10,'20140101'), (2,'xyz',10,'20140102'), (3,'xyz',10,'20140103') select * #tabb1 tabb declare @lnordernum numeric(1), @lcpartnum varchar(3), @ldshipdate datetime, @lnquntity numeric(2) declare @lnlotid numeric(1), @lcpartnum1 varchar(3), @ldentdate datetime, @lnquntity1 numeric(2), @lnquntity2 numeric(2), @lnquntity3 numeric(2) declare cur_a cursor select * taba order shipdate open cur_a fetch next cur_a @lnordernum, @lcpartnum,@lnquntity, @ldshipdate while @@fetch_status=0 begin set @lnquntity3 = @lnquntity while @lnquntity > 0 begin select top 1 lotid, partnum, quntity, dateent #tmpb1 #tabb1 quntity > 0 order dateent set @lnlotid = (select top 1 lotid #tmpb1 ) set @lcpartnum1 = (select top 1 partnum #tmpb1 ) set @ldentdate = (select top 1 dateent #tmpb1 ) set @lnquntity1 = (select top 1 quntity #tmpb1 ) if @lnquntity1 >= @lnquntity begin set @lnquntity2 = 0 insert tabc values(@lnordernum,@lcpartnum,@lnquntity3,@lnlotid,@lnquntity) update #tabb1 set quntity = @lnquntity1 - @lnquntity lotid = @lnlotid , partnum = @lcpartnum1 , dateent = @ldentdate drop table #tmpb1 set @lnquntity = @lnquntity2 end else begin set @lnquntity2 = @lnquntity - @lnquntity1 insert tabc values(@lnordernum,@lcpartnum,@lnquntity3,@lnlotid,@lnquntity1) update #tabb1 set quntity = 0 lotid = @lnlotid , partnum = @lcpartnum1 , dateent = @ldentdate drop table #tmpb1 set @lnquntity = @lnquntity2 end end fetch next cur_a @lnordernum, @lcpartnum,@lnquntity, @ldshipdate end close cur_a deallocate cur_a drop table #tabb1 drop table taba drop table tabb select * tabc drop table tabc
Comments
Post a Comment