October 13, 2009 at 4:52 am
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
October 13, 2009 at 5:24 am
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'
October 13, 2009 at 4:17 pm
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
October 13, 2009 at 6:57 pm
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
)
October 15, 2009 at 2:01 am
Cheers mate!
it's very close to what I was looking for!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy