Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Insert query to distribute given amount


Insert query to distribute given amount

Author
Message
zabistian
zabistian
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 1367
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
Ken McKelvey
Ken McKelvey
SSC Eights!
SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)

Group: General Forum Members
Points: 958 Visits: 7255
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'


zabistian
zabistian
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 1367
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
Furrukh Baig
Furrukh Baig
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 90
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
)


zabistian
zabistian
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 1367
Cheers mate!

it's very close to what I was looking for!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search