Oddball SQL calculation query - any Gurus up to this?

OK, so I have a situation where the Client gets money from activites based on mileage. The further from 'home', the greater the mileage rate is. The distance per stopover is measured from wither home, or the last stopover, making it a continuous interval.

Dependinig on the load size, the rates will increase or decrease as well.

Assume the following are skeleton table structures.

Table structures assume that appropriate keys are in place as 'Keyfields':

Table1Fields: <Table one holds the actual user-entered data per Size>

Key Keyfield(s), <keys to Table2's Keyfields>

Size Varchar,

DistanceFrom Number, (all distances are in miles)

DistanceTo Number,

Table2 Fields: <Table2 is the table holding the dollar rates per distance interval>

Key Keyfields(s),

Size Varchar,

DistanceFrom Number,

DistanceTo Number

Rate Number (dollar rate per mile )

Business rules:

1. The Table1 data may contain entries such as:

Size = 'A', DistanceFrom = 0, DistanceTo = 110

Size = 'A', DistanceFrom = 110, DistanceTo = 150

Size = 'B', DistanceFrom = 150, DistanceTo = 225, etc.

Note that the distance numbers are continuous, no breaks or gaps. Also note the Size B spans the interval in Table2

2. Table2 always contains unchanging intervals (ie: a lookup table) such as:

Size = 'A', DistanceFrom = 0, DistanceTo = 100, Rate = $10

Size = 'A', DistanceFrom = 100, DistanceTo = 200, Rate = $20, etc...

Size = 'B', DistanceFrom = 0, DistanceTo = 100, Rate = $15

Size = 'B', DistanceFrom = 100, DistanceTo = 200, Rate = $25

Size = 'B', DistanceFrom = 200, DistanceTo = 300, Rate = $30, etc...

Note that the distance numbers are continuous, no breaks or gaps. There may be from one to many rates per size (ie: could only have one interval defined, such as from 0 to 2000 miles, or several intervals, depending on the contract).

3. The max Table1.DistanceTo will never exceed the max Table2.DistanceTo.

4. The object is to calculate the total cost per Table1 intervals, per size given in Table1, using the lookup values in Table2. For example, the total cost for the data in Table1 would be:

Size A, 0 to 100 times $10 per mile = $1000 - plus -

Size A, 100 to 110 times $20 per mile = $200 - plus - (notice that the interval spanned that in Table2, so have to calc using next rate)

Size A, 110 to 155 times $20 per mile = $800 - plus -

Size B, 150 to 200 times $25 per mile = $1250 -plus -

Size B, 200 to 225 miles times $30 per mile = $750 (ditto, the interval spanned Table2 interval)

Total: Size A = $2000

Size B = $2000

===============================

Issue: How do I program this using SQL and not resorting to cursors (curses!)?

I've tried CASE statements, but keep getting bogged down in classic programming mush (ie: if-then thinking). My 'set' hat is crooked today!

Any ideas, given the business rules?

Thanks !

G.

Ok, that's a lot of information. Can you build a table, populate it with data and give us your desired results?|||

Thanks for reply.

My post has data for the simplest table, Table1 being the 'real-world' style of data I get. The calculations shown are for the data in Table1.

Table2 is the kind of lookup rates actually used.

G.

|||

Ok, but could you build something like:

CREATE TABLE table1
(
columns...
)

INSERT INTO table1
...

CREATE TABLE table2
(
columns...
)

INSERT INTO table2
...

I want to see:

table1.column table2.column
- --
x y
x z
z y

This would be really helpful to make us understand your requirements (and to make writing the query easier.)

|||

I believe that I have understood the problem you are trying to solve.

I worked my own example which is shown below. My approach was to first set up the join using the rule for overlapping -- t1.from < t2.to and t2.from < t1.to. Next, the calculation uses the min( t1.to, t2.to ) minus max( t1.from, t2.from ). This is the distance subject to the rate. You can see that I first did the calculation with extra fields then applied the sum.

drop table #table1
drop table #table2

create table #table1(
loadSize char(1) not null,
fromDistance int not null,
toDistance int not null
)

create table #table2(
loadSize char(1) not null,
fromDistance int not null,
toDistance int not null,
rate numeric(10,2) not null
)

insert #table1 values( 'A', 0, 110 )
insert #table1 values( 'A', 110, 200 )
insert #table1 values( 'A', 200, 2000 )
insert #table1 values( 'B', 0, 1100 )
insert #table1 values( 'B', 1100, 2000 )

insert #table2 values( 'A', 0, 100, 10.0 )
insert #table2 values( 'A', 100, 500, 20 )
insert #table2 values( 'A', 500, 2000, 15 )
insert #table2 values( 'B', 0, 2000, 30 )

select * from #table1
select * from #table2

Select t1.loadSize,
t1.fromDistance,
t1.toDistance,
t2.fromDistance,
t2.toDistance,
-- min( t1.toDistance, t2.toDistance )
(Case when t1.toDistance < t2.toDistance then t1.toDistance else t2.toDistance end
-
-- max( t1.fromDistance, t2.fromDistance )
Case when t1.fromDistance < t2.fromDistance then t2.fromDistance else t1.fromDistance end
)
*
rate
From #table1 t1
Join #table2 t2
On t1.loadSize = t2.loadSize
And t1.fromDistance < t2.toDistance
And t2.fromDistance < t1.toDistance

Select t1.loadSize,
t1.fromDistance,
t1.toDistance,
Sum(
-- min( t1.toDistance, t2.toDistance )
(Case when t1.toDistance < t2.toDistance then t1.toDistance else t2.toDistance end
-
-- max( t1.fromDistance, t2.fromDistance )
Case when t1.fromDistance < t2.fromDistance then t2.fromDistance else t1.fromDistance end
)
*
rate)
From #table1 t1
Join #table2 t2
On t1.loadSize = t2.loadSize
And t1.fromDistance < t2.toDistance
And t2.fromDistance < t1.toDistance
Group
By t1.loadSize,
t1.fromDistance,
t1.toDistance

|||

Thanks!

I'll get to try this out tomorrow and let U know what happens - looks like what I need essentially.

G.

sql