Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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 -
 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  
 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.02 seconds. Powered By: Snitz Forums 2000