SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Splitting the rows -
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Naveensrcl
Starting Member

India
8 Posts

Posted - 04/02/2013 :  05:13:28  Show Profile  Reply with Quote
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 #sample
select '11003291001-----NOB30000','0V003','A00A00',269,285,305,1 union all
select '11003291001-----NOB30000','03003','A00A00',287,30,305,2

-- Expected output
Invtid Siteid Whseloc Days Qty
11003291001-----NOB30000 0V003 A00A00 269 285
11003291001-----NOB30000 03003 A00A00 269 20 -- 305-285=20 (20 out of 30)
11003291001-----NOB30000 03003 A00A00 287 10 -- 10 out of 30

here 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 lines
11003291001-----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 record
11003291001-----NOB30000 03003 287 10

The 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 records
11003291001-----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 ..

Edited by - Naveensrcl on 04/02/2013 10:38:24
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000