MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

SQL Set Commision

• Follow

```
I have two tables (here is a simplification of the problem).  One table
has: OrderID, Amount, SplitID, CustomerID. Sample

OrderID	 Amount 	SplitID	CustomerID
1	 \$100.00 	1	A
2	 \$40.00 	1	B
3	 \$100.00 	1	A
4	 \$250.00 	1	A
5	 \$100.00 	2	A
6	 \$150.00 	2	B

For Customer A, the Total for Customer A SplitID 1, the total is:
\$450.00 (\$100 + \$100 + \$250)

The second Table has SplitID, SplitN, Amount, Percent. Sample

SplitID	SplitN	 Amount 	%
1	1	 \$0.00   	0%
1	2	 \$50.00 	5%
1	3	 \$100.00 	10%
1	4	 \$200.00 	15%
2	1	 \$0.00   	0%
2	2	 \$100.00 	5%

I use the second table to calculate the commision for my Customers. For
Customer A, SplitID 1:
(50-0)*0.00 + (100-50)*.05 + (200-100)*.10 + (450-200)*.15
I would like to use both tables to find my commision problem using these
two tables in a Set Base solution.  I can't figure out a way to do this
using - just normal procedural programming.

*** Sent via Developersdex http://www.developersdex.com ***
```
 0

```> I use the second table to calculate the commision for my Customers. For
> Customer A, SplitID 1:
> (50-0)*0.00 + (100-50)*.05 + (200-100)*.10 + (450-200)*.15

I'm sure a set-based solution is possible but I don't understand the
relationship between Orders and Commissions.  It looks like the subtrahend
in each of the expressions is the Amount for the commissions table.   The
minuend for the first three seems to be the amount from the next highest
SplitN and the last looks like the total order amount for the
customer/split.

Can you post the entire expected results with customer A and B included as
well as and commission splits 1 and 2?

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Daniel Mojica" <temp@hotmail1.com> wrote in message
news:eRchJZflKHA.1536@TK2MSFTNGP06.phx.gbl...
>
>
> I have two tables (here is a simplification of the problem).  One table
> has: OrderID, Amount, SplitID, CustomerID. Sample
>
> OrderID Amount SplitID CustomerID
> 1 \$100.00 1 A
> 2 \$40.00 1 B
> 3 \$100.00 1 A
> 4 \$250.00 1 A
> 5 \$100.00 2 A
> 6 \$150.00 2 B
>
> For Customer A, the Total for Customer A SplitID 1, the total is:
> \$450.00 (\$100 + \$100 + \$250)
>
> The second Table has SplitID, SplitN, Amount, Percent. Sample
>
> SplitID SplitN Amount %
> 1 1 \$0.00   0%
> 1 2 \$50.00 5%
> 1 3 \$100.00 10%
> 1 4 \$200.00 15%
> 2 1 \$0.00   0%
> 2 2 \$100.00 5%
>
> I use the second table to calculate the commision for my Customers. For
> Customer A, SplitID 1:
> (50-0)*0.00 + (100-50)*.05 + (200-100)*.10 + (450-200)*.15
> I would like to use both tables to find my commision problem using these
> two tables in a Set Base solution.  I can't figure out a way to do this
> using - just normal procedural programming.
> Thanks for all your help.
>
> *** Sent via Developersdex http://www.developersdex.com ***

```
 0

```"A problem well stated is a problem half solved." -- Charles F.
Kettering

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you know how, follow ISO-11179 data element naming
conventions and formatting rules.  Temporal data should use ISO-8601
formats.  Code should be in Standard SQL as much as possible and not
local dialect.

Sample data is also a good idea, along with clear specifications.  It
is very hard to debug code when you do not let us see it. If you want
to learn how to ask a question on a Newsgroup, look at:
http://www.catb.org/~esr/faqs/smart-questions.html

It would also help if we knew the rules for Orders and Commissions
```
 0

```Hi Daniel,

SELECT SplitAmount = COALESCE( sc.Amount, 0 )
FROM Orders o
LEFT OUTER JOIN SplitComm sc ON sc.SplitID = o.SplitID
AND o.SplitN = (
SELECT TOP 1 sc2.SplitN

FROM SplitComm sc2

WHERE sc2.SplitID = o.SplitID

AND sc2.SplitN >= o.Amount

ORDER BY sc2.SplitN )

The above does the windowing you require, obviously you need to test and
check - please post workable create table/inserts if it doesn't do what you
want so I can refine it.

Essentially you need to work out the SplitComm row that the Amount refers
to - I've assumed that the Amount in the Split Comm table holds the starting
value for the window.

Tony.

"Daniel Mojica" <temp@hotmail1.com> wrote in message
news:eRchJZflKHA.1536@TK2MSFTNGP06.phx.gbl...
>
>
> I have two tables (here is a simplification of the problem).  One table
> has: OrderID, Amount, SplitID, CustomerID. Sample
>
> OrderID Amount SplitID CustomerID
> 1 \$100.00 1 A
> 2 \$40.00 1 B
> 3 \$100.00 1 A
> 4 \$250.00 1 A
> 5 \$100.00 2 A
> 6 \$150.00 2 B
>
> For Customer A, the Total for Customer A SplitID 1, the total is:
> \$450.00 (\$100 + \$100 + \$250)
>
> The second Table has SplitID, SplitN, Amount, Percent. Sample
>
> SplitID SplitN Amount %
> 1 1 \$0.00   0%
> 1 2 \$50.00 5%
> 1 3 \$100.00 10%
> 1 4 \$200.00 15%
> 2 1 \$0.00   0%
> 2 2 \$100.00 5%
>
> I use the second table to calculate the commision for my Customers. For
> Customer A, SplitID 1:
> (50-0)*0.00 + (100-50)*.05 + (200-100)*.10 + (450-200)*.15
> I would like to use both tables to find my commision problem using these
> two tables in a Set Base solution.  I can't figure out a way to do this
> using - just normal procedural programming.
> Thanks for all your help.
>
> *** Sent via Developersdex http://www.developersdex.com ***

```
 0