microsoft.public.sqlserver.programming

Dan Guzman

3/9/2007 12:11:00 PM

You 2 active threads in this group titled 'Split Proc' and 'split sp' and
have received responses to both. Be patient. There may be a delay before
answers propagate.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"obelix via SQLMonster.com" <u24035@uwe> wrote in message
news:6eeb8b3f3ff9d@uwe...
> Just need some pointers only ... not good at this thing.
>
> Need to split funds from Funds_Pool tbl based on availability and priority
> in
> Allocation_Pool tbl. What may be allocated from the Funds_Pool depends on
> the
> alloc% e.g even though quantity is 100 in Allocation_Pool tbl if alloc%
> is
> 50 then only 50 can be allocated from that pool, also which pool to
> allocate
> from will depend on the priority with 1 being high priority. Please help
> ....
>
> obrigado!
>
> DDL follows
>
> CREATE TABLE [dbo].[Funds_Pool] (
> [Fund_ID] [int] NOT NULL ,
> [Quantity] [decimal](23, 8) NULL ,
> [FundCode] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Fund_Allocation] (
> [ID] [int] IDENTITY (1, 1) NOT NULL,
> [Fund_ID] [int] NOT NULL ,
> [Quantity] [decimal](23, 8) NULL ,
> [FundCode] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [PoolCode] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Allocation_Pool] (
> [APool_ID] [int] NOT NULL ,
> [Quantity] [decimal](23, 8) NULL ,
> [Priority] [int] NULL,
> [Alloc_Perc] [int] NULL ,
> [PoolCode] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [FundCode] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> ) ON [PRIMARY]
> GO
>
> INSERT INTO Funds_Pool (Fund_ID,Quantity,FundCode) VALUES
> (1,1000,'ABC')
> ----->
> INSERT INTO Allocation_Pool
> (APool_ID,Priority,Quantity,Alloc_Perc,PoolCode,
> FundCode)
> VALUES
> (1,1,1000,50,'A','ABC')
> INSERT INTO Allocation_Pool
> (APool_ID,Priority,Quantity,Alloc_Perc,PoolCode,
> FundCode)
> VALUES
> (2,3,200,100,'B','ABC')
> INSERT INTO Allocation_Pool
> (APool_ID,Priority,Quantity,Alloc_Perc,PoolCode,
> FundCode)
> VALUES
> (3,2,300,100,'C','ABC')
> ------>
>
> Based on the above the inserts into Fund_Allocation we shld get the
> results
> below
>
> ID FUN_ID QUANTITY FUNDCODE POOLCODE
> 1 1 500 ABC A
> 2 1 300 ABC C
> 3 1 200 ABC B
>
> --
> obelix
>
> "Whether you think you can or you think you cant you are right" .... Anon
>
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programmin...
>