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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply