Complex Calculation Needed - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Complex Calculation Needed

Author

Topic

nick_bin
Starting Member

2 Posts

Posted - 2009-10-23 : 16:07:54

Hi,

This is my first post in this forum. Greetings to all.

First of all, this is a query that will create table and feed some data.

quote:


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Temp_Payment_Holder]') AND type in (N'U'))
DROP TABLE [dbo].[Temp_Payment_Holder]

Create Table Temp_Payment_Holder
(
	Payment_ID INT,
	Required_Payment MONEY,
	Cust_ID INT,
	Available_Amount MONEY
)

INSERT INTO Temp_Payment_Holder(Payment_ID, Required_Payment, Cust_ID, Available_Amount)
SELECT 4, 10000, 2, 6400
UNION
SELECT 4, 10000, 50, 3500
UNION
SELECT 4, 10000, 79, 25000
UNION
SELECT 4, 10000, 80, 10000
UNION
SELECT 5, 35000, 76, 1800
UNION
SELECT 5, 35000, 78, 5000
UNION
SELECT 5, 35000, 80, 10000
UNION
SELECT 6, 19000, 2, 6400
UNION
SELECT 6, 19000, 79, 25000
--IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Temp_Payment_Holder]') AND type in (N'U'))
--DROP TABLE [dbo].[Temp_Payment_Holder]

Table Structure


Payment_ID	Required_Payment	Cust_ID	Available_Amount
4		10000			2		6400
4		10000			50		3500
4		10000			79		25000
4		10000			80		10000
5		35000			76		1800
5		35000			78		5000
5		35000			80		10000
6		19000			2		6400
6		19000			79		25000

Payment_ID:
FK of Payment_Schedule table which stores information of payments on specific dates.

Required_Payment:
Derived from Payment_Schedule table, payment amount of the current schedule. This is the amount that should be paid by customers.

Cust_ID:
Derived from Customers table.

Available_Amount:
Derived from a function, the total available balance of a customer.

Scenario
A query is needed to generate a result like shown in the bottom in such a way that ...

The sum of Payment(of the result) of each Cust_ID should not exceed Available_Amount of Cust_ID

If the total sum of Available_Amount is less than Required_Payment, it should return a null value, or no row at all.

Anticipated Result


Payment_ID	Cust_ID	Payment
4		2		6400
4		50		3500
4		79		100
5		NULL		NULL
6		79		19000

nick_bin
Starting Member

2 Posts

Posted - 2009-10-25 : 01:28:25

Anyone there to look into this post.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-25 : 16:19:51

Shouldn't payment 6 be like this?


Payment_ID	Cust_ID		Payment
6		2		6400
6		79		12600

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources