Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Insert query to distribute given amount Expand / Collapse
Author
Message
Posted Tuesday, October 13, 2009 4:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 17, 2011 11:09 PM
Points: 6, Visits: 1,367
Hello Gurus,

I would like to write an insert stored proc (without using cursor or while loop) to distribute a given value among several rows. I have two tables - SourceTable have rows for available amount and AllocationTable will be use to insert allocated rows.


Tables:
-----------------
CREATE TABLE [dbo].[SourceTable](
[ID] [int] NOT NULL,
[Amount] [decimal](18, 2) NOT NULL,
CONSTRAINT [PK_SourceTable] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[AllocationTable](
[ID] [decimal](18, 2) NOT NULL,
[SourceTableID] [int] NOT NULL,
[AllocatedAmount] [decimal](18, 2) NOT NULL,
CONSTRAINT [PK_AllocationTable] PRIMARY KEY CLUSTERED
(
[ID] ASC,
[SourceTableID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[AllocationTable] WITH CHECK ADD CONSTRAINT [FK_AllocationTable_SourceTable] FOREIGN KEY([SourceTableID])
REFERENCES [dbo].[SourceTable] ([ID])
GO

ALTER TABLE [dbo].[AllocationTable] CHECK CONSTRAINT [FK_AllocationTable_SourceTable]
GO


Required Data:

INSERT INTO [SourceTable](Amount) VALUES(1, 1000)
INSERT INTO [SourceTable](Amount) VALUES(2, 1000)
INSERT INTO [SourceTable](Amount) VALUES(3, 1000
)




Now the Stored proc part:

The insert stored proc gets distribution amount as an input and insert records in [AllocationTable] table until it exaust given amount
Let's say @AmountToAllocate passed to SP

DECLARE @AmountToAllocate DECIMAL(18,2)

IF
@AmountToAllocate = 2300.00
THEN
I need to INSERT three rows (dynamically) in AllocationTable

Example ;
INSERT INTO [AllocationTable](SourceTableID, AllocatedAmount) VALUES(1, 1000)
INSERT INTO [AllocationTable](SourceTableID, AllocatedAmount) VALUES(2, 1000)
INSERT INTO [AllocationTable](SourceTableID, AllocatedAmount) VALUES(3, 300)

If
@AmountToAllocate = 1500.00
THEN
I need to insert script should insert three rows (dynamically) in [AllocationTable]

Example:
INSERT INTO [AllocationTable](SourceTableID, AllocatedAmount) VALUES(1, 1000)
INSERT INTO [AllocationTable](SourceTableID, AllocatedAmount) VALUES(2, 500)


If
@AmountToAllocate = 4500.00
THEN
I need to insert Three rows (dynamically) in [AllocationTable]

Example:
INSERT INTO [AllocationTable](SourceTableID, AllocatedAmount) VALUES(1, 1000)
INSERT INTO [AllocationTable](SourceTableID, AllocatedAmount) VALUES(2, 1000)
INSERT INTO [AllocationTable](SourceTableID, AllocatedAmount) VALUES(3, 1000)

Hint: it should keep inserting rows until it exaust given amount - if given amount is over then it will be fully allocated


Please help me to solve this query


B.Syd
Post #802058
Posted Tuesday, October 13, 2009 5:24 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 845, Visits: 5,452
Use a number/tally table and CASE. Something like:
INSERT INTO AllocationTable(SourceTableID, AllocatedAmount)
SELECT [ID]
,CASE
WHEN N.Number * 1000 <= S.Amount
THEN N.Number * 1000
ELSE S.Amount % ((N.Number - 1) * 1000)
END
FROM SourceTable S
JOIN master.dbo.spt_values N
ON S.Amount > (N.Number - 1) * 1000
AND N.Number > 0
AND N.[Type] = 'P'

Post #802072
Posted Tuesday, October 13, 2009 4:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 17, 2011 11:09 PM
Points: 6, Visits: 1,367
Thanks for you response but it doesnt solve the problem.

SourceTable could have rows like:
ID SourceAmout
1 1300
2 2000
3 1700

and @AmountToAllocate passed to SP could be 4123.00

in this case I would like to insert records in AllocationTable like this

SourceTableID, AllocatedAmount
1 1300
2 2000
3 823

Additionally I dont want to reallocate the amount if it has been already allocated i.e rows exist in AllocationTable

Thanks again for your help
B.Syd
Post #802444
Posted Tuesday, October 13, 2009 6:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 5:28 AM
Points: 6, Visits: 77
I would hope following will work.

This will always keep one item line in allocationtable by deleting the old row and keeping balance uptodate.

declare @AmountToBeAllocated as decimal(18,2)
declare @decimal18 as decimal(18,2)
declare @bigint as bigint
declare @int as int

-- test this script by changing following value
set @AmountToBeAllocated = 300

set @bigint = 0
set @decimal18 = 0;

-- sum up already allocated rows so balance can be calculated in recursive part accordingly
declare @allocated table (sourcetableid int, allocatedamount decimal(18,2))

-- do not remove semicolon from end of statement as it is required before defining CTE
insert into @allocated (sourcetableid, allocatedamount)
select sourcetableid, allocatedamount from allocationtable ;
-- PLEASE ADD INVOICE ID FILTER


With test as
(
-- Anchor query to provide seed for amount to be allocated
-- anchor row will not part of result set as it is only used as seed
-- Please use your parameter replacing figure in remaining column like "remaining = @amount"

select INVOICEID = @BIGINT, rowid = @bigint, id = @int, amount = @decimal18, allocated = @decimal18, remaining = @AmountToBeAllocated

union all

-- recursive part uses anchor seed as remaining amount to be allocated
Select
-- PLEASE ADD INVOICE ID COLUMN REPLACE 0 (ZERO)
@BIGINT,
s.rowid,
s.id,
s.amount,
cast((case when t.remaining > (s.amount - isnull((select allocatedamount from @allocated where sourcetableid = s.id),0))
then s.amount
else t.remaining + isnull((select allocatedamount from @allocated where sourcetableid = s.id),0)
end) as decimal(18,2)),
cast(t.remaining - (s.amount - isnull((select allocatedamount from @allocated where sourcetableid = s.id),0)) as decimal(18,2))
from
-- following inline table used to avoid issues with non-sequential number in ID and use rownumber for safe join
(select rowid = row_number() over (order by id), id, amount = cast(amount as decimal(18,2)) from sourcetable) s
inner join test t
on s.rowid = t.rowid + 1
-- PLEASE ADD INVOICE ID FILTER
)

insert into allocationtable (ID, SourceTableID, AllocatedAmount)
select
-- PLEASE ADD INVOICE ID COLUMN FROM RECURSIVE
--INVOICEID,
id = row_number() over (order by t.id) + isnull((select max(id) from allocationtable),0),
sourcetableid = t.id,
t.allocated
from
test t
where
t.id > 0 -- ignore anchor part
AND t.allocated > 0

DELETE FROM ALLOCATIONTABLE FROM ALLOCATIONTABLE AT
WHERE EXISTS
(
SELECT 1
FROM
ALLOCATIONTABLE
-- PLEASE ADD INVOICE ID FILTER
GROUP BY
SOURCETABLEID
HAVING
COUNT(1) > 1
AND MIN(ID) = AT.ID
)

Post #802469
Posted Thursday, October 15, 2009 2:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 17, 2011 11:09 PM
Points: 6, Visits: 1,367
Cheers mate!

it's very close to what I was looking for!
Post #803274
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse