Recursive Function or Anything to solve the issue.

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 2005 Forums

Transact-SQL (2005)

Recursive Function or Anything to solve the issue.

Author

Topic

sqlidiot
Starting Member

2 Posts

Posted - 2010-09-14 : 16:08:49

I need a way to sum a field until I get a specific value. I have a subset of records ~8-20 sales records with 2 different discount rates applied on the same invoice. I know the percentage of the discounts is say 10% for a toal of $25.20 and 40% for a total of 36. Given this I know that the item(s) receiving 10% discount totaled $252 and the item(s) that recieved 40% discount totaled $90.
Does anyone have a way to take this information and find the transacation(s) that match the total dollars. It does not have to be 100% accurate but I can only apply one discount to each transaction record.

Any insight you may have on this will be greatly apprecaited.

Thanks.

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2010-09-15 : 03:46:38

CAn u provide table structure and some sample data

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-15 : 04:50:05

You can write a Recursive Common Table Expression.
Your problem is also known as Bin Packaging. There are a number of examples of how to do this on this site.

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

MSquared
Yak Posting Veteran

52 Posts

Posted - 2010-09-15 : 09:35:04

Is this homework?

For Faster results please follow the posting guidelines here

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

sqlidiot
Starting Member

2 Posts

Posted - 2010-09-15 : 17:22:46

Sorry Guys I have some more information for you on this issue. Below I have two tables one with Sales info and one with discount info. My desired result is to basically be able to link the sales data with a discount rate by matching the summed sales amount with the total discount amount.

Create table #Sales(
Id int,
Invoice_Num varchar(50),
Brand varchar(10),
Item varchar(50),
Amount money)

insert into #Sales
Select 1, 'Inv_1', 'SMP', 'Item1', 81
Union
Select 2, 'Inv_1', 'SMP','Item2', 81
Union
Select 3, 'Inv_1', 'SMP','Item3', 90
Union
Select 4, 'Inv_1', 'SMP','Item4', 90
Union
Select 5, 'Inv_1', 'SMP','Item5', 10

Create table #Discount(
Invoice_Num varchar(50),
Brand varchar(10),
Discount_PCT money,
Discount_Amt money)

insert into #Discount
Select 'Inv_1', 'SMP', 0.10,252
Union
Select 'Inv_1', 'SMP',0.40, 90

Resulting data looks like this.
Sales data
1 Inv_1 SMP Item1 81.00
2 Inv_1 SMP Item2 81.00
3 Inv_1 SMP Item3 90.00
4 Inv_1 SMP Item4 90.00
5 Inv_1 SMP Item5 10.00

Discount data
Inv_1 SMP 0.10 252.00
Inv_1 SMP 0.40 90.00

Using these two sets I need to be able to assign a discount rate to the sales data based on the sum of the sales amount.

The first three rows of the sales data sums to $252. This matches the total discount for the first discount row of 10% at $252. The fourth row sales amount is $90 which matches the total for the second discount of 40%. I know that the third row also matches this total but I can only apply one discount per line item. The fifth row does not match nor in combination with any of the other rows add up to either discount amount so no discount is applied.

The end result I am looking for is something like this.
1 Inv_1 SMP Item1 81.00 0.10
2 Inv_1 SMP Item2 81.00 0.10
3 Inv_1 SMP Item3 90.00 0.10
4 Inv_1 SMP Item4 90.00 0.40
5 Inv_1 SMP Item5 10.00 0

Thanks for any insight you may have.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources