SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 amount allocation query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pnpsql
Posting Yak Master

India
246 Posts

Posted - 05/07/2013 :  07:26:01  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 05/07/2013 :  07:27:57  Show Profile  Reply with Quote
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
Go to Top of Page

pnpsql
Posting Yak Master

India
246 Posts

Posted - 05/07/2013 :  07:31:19  Show Profile  Reply with Quote
yes on id basis

challenge everything
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/07/2013 :  07:39:11  Show Profile  Reply with Quote
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
Go to Top of Page

pnpsql
Posting Yak Master

India
246 Posts

Posted - 05/07/2013 :  07:43:24  Show Profile  Reply with Quote
partial allocation should considered as no allocation.


challenge everything
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/07/2013 :  08:01:26  Show Profile  Reply with Quote

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 



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

pnpsql
Posting Yak Master

India
246 Posts

Posted - 05/07/2013 :  08:12:20  Show Profile  Reply with Quote
can't understand , please explain


challenge everything
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/07/2013 :  15:26:12  Show Profile  Reply with Quote
Here is the code you are interested in: (there is a minor modification to Visakh16's code in red below)


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;



Edited by - MuMu88 on 05/07/2013 15:36:24
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/08/2013 :  00:13:45  Show Profile  Reply with Quote
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
Go to Top of Page

pnpsql
Posting Yak Master

India
246 Posts

Posted - 05/08/2013 :  09:42:40  Show Profile  Reply with Quote
thanks a lot .

challenge everything
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/08/2013 :  12:31:08  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000