Geometry Geometry Latitude / Longitude SQL

Question

I have a problem using geography to calculate the distance in miles using the format of my table.

The latitude and longitude of both places are located nearby:

id | A_Latitude | A_Longitude | B_Latitude | B_Longitude

I am trying to get a point Aalong with a point Band return the distance between Aand Bin miles.

I tried several things, including something similar to:

DECLARE @orig geography 
DECLARE @end geography
SELECT
@orig = geography::Point(A_LATITUDE, A_LONGITUDE, 4326) 
,@end = geography::Point(B_LATITUDE, B_LONGITUDE, 4326) 
FROM table1
SELECT 
    ROUND(@END.STDistance(@ORIG)/1609.344,2) AS MILES
    ,@END.STDistance(@ORIG) AS METERS
    ,table1.*
FROM table1;

Where I get a repeating value for miles and meters across all lines. Can anyone suggest how I should structure this query to get what I'm looking for?

EDIT: Thanks SQL Surfer!

WITH X AS 
(SELECT 
 geography::Point(A_LATITUDE, A_LONGITUDE, 4326) A
,geography::Point(B_LATITUDE, B_LONGITUDE, 4326) B
,ID
FROM TABLE1)

SELECT 
ROUND(X.B.STDistance(X.A)/1609.344,2) AS MILES
,X.B.STDistance(X.A) AS METERS
,T.*
FROM TABLE1 T
LEFT JOIN X ON T.ID = X.ID

Answer 1

Here is what I would do:

WITH X AS 
(
   SELECT 
      geography::Point(A_LATITUDE, A_LONGITUDE, 4326) A
     ,geography::Point(B_LATITUDE, B_LONGITUDE, 4326) B
     ,*
   FROM TABLE1
)

SELECT 
   ROUND(B.STDistance(A)/1609.344, 2) AS MILES
   ,B.STDistance(X.A) AS METERS
   ,*
FROM X

( ), , . !