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 ***

> 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 *** 

"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
instead of guessing at them.

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 ***