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.
| Author |
Topic |
|
Naveensrcl
Starting Member
8 Posts |
 Posted - 2013-04-02 : 05:13:28
|
| Hi Experts , here is sample table and data create table #sample (Invtid varchar(255),[Final SiteId] varchar(255),Whseloc varchar(255) ,Days int,Qty int,[Aging Stock] int,Priority int)insert into #sampleselect '11003291001-----NOB30000','0V003','A00A00',269,285,305,1 union allselect '11003291001-----NOB30000','03003','A00A00',287,30,305,2-- Expected output Invtid Siteid Whseloc Days Qty11003291001-----NOB30000 0V003 A00A00 269 28511003291001-----NOB30000 03003 A00A00 269 20 -- 305-285=20 (20 out of 30)11003291001-----NOB30000 03003 A00A00 287 10 -- 10 out of 30here is the logic:row 1 priority column 1 first distribution i allocated 285 units. But the bucket at the [Aging Stock] had originally 305 units Then the balance between the 305 - 285 = 20 units. That means that i still have 20 units in stock with 269 days.So i will allocate only the 20 units to the priority 2[Aging Stock] (20 from 30)Now in the final table i have 2 lines11003291001-----NOB30000 0V003 A00A00 269 285 ( First Allocation) 11003291001-----NOB30000 03003 A00A00 269 20 ( Second Allocation -- from row 2)But there are still 10 units left from the priority 2 So now if i look again to the priority 1 [Aging Stock], i can't work with the 269 days, since now there is 0 balance there, i use the 10 left for the second allocation. So i move to the next record11003291001-----NOB30000 03003 287 10The next record has exactly the same 10 units i need, and i insert those 10 units with 287 days to the final table. Now the final output will have 3 records11003291001-----NOB30000 0V003 A00A00 269 285 ( First Allocation) 11003291001-----NOB30000 03003 A00A00 269 20 ( Second Allocation ) 11003291001-----NOB30000 03003 A00A00 287 10 ( Third Allocation )please help me .. |
|
|
|
|
|
|
|
Splitting the rows -