amount allocation query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

amount allocation query

Author

Topic

pnpsql
Posting Yak Master

246 Posts

Posted - 2013-05-07 : 07:26:01

i have table LIKE

tbl_charge

id amount
1 10
2 20
3 30
4 20

AND i want TO craete FUNCTION WHERE i want to pass some amount
LIKE FN_alloc(50)
AND that amount will virtaully allocated TO amounts IN TABLE AND RETURN the id
FROM that is NOT allocated BY this amount

challenge everything

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-07 : 07:27:57

on what order you want to allocate? based on id value sequence? Also for partial allocations do you require those ids to be returned?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

pnpsql
Posting Yak Master

246 Posts

Posted - 2013-05-07 : 07:31:19

yes on id basis

challenge everything

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-07 : 07:39:11

quote:
Originally posted by pnpsql

yes on id basis

challenge everything

]
what about second question?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

pnpsql
Posting Yak Master

246 Posts

Posted - 2013-05-07 : 07:43:24

partial allocation should considered as no allocation.

challenge everything

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-07 : 08:01:26

[code]
SELECT *
FROM table
EXCEPT
SELECT t.*
FROM table t
OUTER APPLY (SELECT SUM(amount) AS PrevTotal
FROM table
WHERE id < t.id
)t1
WHERE COALESCE(PrevTotal,0) <=50
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

pnpsql
Posting Yak Master

246 Posts

Posted - 2013-05-07 : 08:12:20

can't understand , please explain

challenge everything

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-07 : 15:26:12

Here is the code you are interested in: (there is a minor modification to Visakh16's code in red below)
[CODE]

CREATE TABLE tbl_charge(
ID int,
amount int )

INSERT INTO tbl_charge (ID, amount) Values
(1, 10),
(2, 20),
(3, 30),
(4, 20);

IF OBJECT_ID('FN_Alloc') IS NOT NULL DROP PROC FN_Alloc;

GO

CREATE PROC FN_Alloc @amount int -- Creates FN_Alloc function
AS
BEGIN
SELECT *
FROM tbl_charge
EXCEPT
SELECT t.*
FROM tbl_charge t
OUTER APPLY (SELECT SUM(amount) AS PrevTotal
FROM tbl_charge
WHERE id <= t.id
)t1
WHERE COALESCE(PrevTotal,0) <= @amount;
END;

GO

exec FN_Alloc 50;

[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-08 : 00:13:45

quote:
Originally posted by pnpsql

can't understand , please explain

challenge everything

the code takes each row and does a running count of all amount values of previous records. then this is compared against your passed value and wherever the sum is below it we will retrieve records. This will give you records which are allocated. Then we use EXCEPT to exclude these records from your actual table records to give you remaining ones

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

pnpsql
Posting Yak Master

246 Posts

Posted - 2013-05-08 : 09:42:40

thanks a lot .

challenge everything

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-08 : 12:31:08

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources