SQL Geography Latitude/Longitude distance calc

Question

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

Latitude and Longitude of both locations are side by side:

id | A_Latitude | A_Longitude | B_Latitude | B_Longitude

I'm trying to get the point of A, along with the point of B, and return the distance between A and B in miles.

I've tried a few 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'm getting a repeating value for miles and meters across all rows. Could anyone please suggest how I should be structuring 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's what I'd 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

If you're thinking of doing this often (and on the fly), consider adding computed columns to your table for not only the geography types, but also the distance between them. At that point, it's just a simple query from the table!