Good Forum.

I hope you all will find it very well. Sorry for this inconvenience but I need if I could give idea of how to make the following query in a simple and easy, or that method could be applied to the construction and generation of the same in a SELECT in SQL Server 2005:

TABLE: TBL_POINTCONTROL
IdTBL_POINTCONTROL INT IDENTITY (1,1) NOT NULL,
CODE CHAR (5) NOT NULL,
NAME nvarchar (80) NOT NULL,
MILE SMALLINT NOT NULL

Each record that is generated in this table indicates a point control in "x" region of a site "and". The kilometers specifies that the record identifies the point control automotive as such.

Now, the problem is that I need to create a query predefined in the database WHERE I make the difference (subtraction) of the number of miles from the record that there are 2 to register 1, and expressing how much is the distance between those two points, so to speak.

Example data for the table:

IdTBL_PUNTOCONTROL     CODE      NAME               MILES
1                                    AAA       ENCRUCIJADA      10
2                                    BBB       PALO NEGRO       25
3                                    CCC       TAPA TAPA          35
4                                    DDD      SANTA CLARA       60

What I want to me as a result of the query is as follows:

CODE     NAME                CODE           NAME                 DISTANCE
BBB      PALO NEGRO        AAA           ENCRUCIJADA          15
CCC      TAPA TAPA           BBB           PALO NEGRO           10
DDD      SANTA CLARA       CCC          TAPA TAPA               25

As I think that really is something simple but not give encounter as a solution to this topic. Could someone give me a light solution of the matter.

Thanks,;).

Regards,

Jose Redondo

Try This:
SELECT A.Code, A.Name, B.Code, B.Name, (B.Mile-A.Mile) As Distance

FROM TBL_POINTCONTROL A

INNER JOIN

TBL_POINTCONTROL B

ON A.TBL_POINTCONTROL = B.TBL_POINTCONTROL +1