please help with this query

[subs]

Ost Ocity Dstate Dcity Carrier Price Rank Diff
A B C D X 1200 1 100
A B C D Y 1300 2 100
A B C D Z 1350 3 100
A B C D W 1789 4 100
A1 B1 C1 D1 X1 785 1
A1 B1 C1 D1 Y1 789 2
The rank for every carrier is based on the price . If rank1
carrier
is not a pariticular carrier(say if it is not X1 or
Y1 or Z1), then i want to calculate the difference between Price
corresponding to rank1 and price corresponding to rank2. and fill the
Diff column with the value. I want a query which can create that
difference coln ( last col) as in the above example. The first four
rows is one lane and the last two rows is another lane. For the
second set of lane, the diff col will be empty because tthe carrier is
X1.
Can somebody please help me in writing this query.

Thanks in advance

 

[John W. Vinson]

Ost Ocity Dstate Dcity Carrier Price Rank Diff
A B C D X 1200 1 100
A B C D Y 1300 2 100
A B C D Z 1350 3 100
A B C D W 1789 4 100
A1 B1 C1 D1 X1 785 1
A1 B1 C1 D1 Y1 789 2
The rank for every carrier is based on the price . If rank1
carrier
is not a pariticular carrier(say if it is not X1 or
Y1 or Z1), then i want to calculate the difference between Price
corresponding to rank1 and price corresponding to rank2. and fill the
Diff column with the value. I want a query which can create that
difference coln ( last col) as in the above example. The first four
rows is one lane and the last two rows is another lane. For the
second set of lane, the diff col will be empty because tthe carrier is
X1.
Can somebody please help me in writing this query.

Thanks in advance

Subbu, I think the reason nobody has posted a response is that your question
is very difficult to understand. You know what a "lane" is, but we don't. You
know how rank is determined, we don't. You understand how 1789 and 1350 can
produce a Diff of 100, but we don't. You know the special meaning of X1 but we
don't. Could you explain a bit more about the nature of your data and the
calculations??