[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Split Proc

obelix

3/9/2007 8:56:00 AM

olá
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 http://www.sqlm...
2 Answers

masri999

3/9/2007 11:33:00 AM

0

Check this

select T.Fund_ID,case when T.alllocatedqty <= 0 then 0
when T.tobeallocated <= alllocatedqty then
T.tobeallocated
When T.tobeallocated > alllocatedqty then T.alllocatedqty
else 0 end ,
T.fundcode,T.poolcode
from
(
select a.*,b.Fund_ID ,
(a.quantity * alloc_perc/100.00) as tobeallocated ,
b.Quantity- (
select isnull(sum(quantity * alloc_perc/100.00),0) as
allocation
from Allocation_Pool b
where a.fundcode = b.fundcode
and b.Priority < a.Priority
) alllocatedqty
from Allocation_Pool a ,
Funds_Pool b
where a.fundcode = b.fundcode )T

M A Srinivas




On Mar 9, 1:56 pm, "obelix via SQLMonster.com" <u24035@uwe> wrote:
> olá
> 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 viahttp://www.sqlm...

obelix

3/9/2007 12:28:00 PM

0

Thanks a lot for the response, had gone to buy myself flowers, was feeling
very ignored. I've tested it once & it seems to work, I wld prefer to
process the inserts i rec at a time as i need to do some pother stuff on the
record created but I'm sure I can work my way building from the ideas .
Nifty stuff indeed!

M A Srinivas wrote:
>Check this
>
>select T.Fund_ID,case when T.alllocatedqty <= 0 then 0
> when T.tobeallocated <= alllocatedqty then
>T.tobeallocated
> When T.tobeallocated > alllocatedqty then T.alllocatedqty
> else 0 end ,
>T.fundcode,T.poolcode
>from
>(
> select a.*,b.Fund_ID ,
> (a.quantity * alloc_perc/100.00) as tobeallocated ,
> b.Quantity- (
> select isnull(sum(quantity * alloc_perc/100.00),0) as
>allocation
> from Allocation_Pool b
> where a.fundcode = b.fundcode
> and b.Priority < a.Priority
> ) alllocatedqty
>from Allocation_Pool a ,
>Funds_Pool b
>where a.fundcode = b.fundcode )T
>
>M A Srinivas
>
>> olá
>> Need to split funds from Funds_Pool tbl based on availability and priority in
>[quoted text clipped - 63 lines]
>>
>> Message posted viahttp://www.sqlm...

--
obelix

"Whether you think you can or you think you cant you are right" .... Anon

Message posted via SQLMonster.com
http://www.sqlm.../Uwe/Forums.aspx/sql-server-programming/200703/1