How do I find the cumulative distance between geometry points in SQL Server 2014? I have a database designed to be a proof of concept for delivery of products from a central depot to customers distributed at random locations within ten miles of the depot. The depot location is expressed in absolute coordinates (0,0) and customer locations are stored in the database in two columns with Coord_X and Coord_Y. The code I have at present to determine which deliveries are due on a particular day is as follows:

SELECT t_Orders.Order_ID, t_Orders.Del_Sess, t_Customers.Contact_Name, t_Addresses.Cust_ID, t_Addresses.Town, t_Addresses.County, t_Addresses.Coord_Y, t_Addresses.Coord_X, t_Customers.Comp_Name, t_Orders.Cust_ID, 
t_Orders.Del_Date
FROM t_Orders
JOIN t_Customers
ON t_Orders.Cust_ID=t_Customers.Cust_ID AND t_Orders.Del_Sess=1 AND t_Orders.Del_Date = '2016-05-21' 
JOIN t_Addresses
ON t_Orders.Cust_ID=t_Addresses.Cust_ID AND (t_Addresses.Coord_Y &gt;= 0 AND t_Addresses.Coord_X &lt;= 0 AND SQUARE(t_Addresses.Coord_X) &lt; SQUARE(t_Addresses.Coord_Y)) 
OR (t_Addresses.Coord_Y &gt;= 0 AND t_Addresses.Coord_X &gt;= 0 AND (t_Addresses.Coord_X) &lt; (t_Addresses.Coord_Y))
ORDER BY SQUARE (t_Addresses.Coord_X) + SQUARE (t_Addresses.Coord_Y) DESC


This returns a list of all deliveries, with the furthest away first. I'd like to be able to take the output of this query and run queries on it (I looked into temporary tables and views, but can't seem to get this into one) to first remove any duplicates (e.g. multiple deliveries to one customer on a particular day as each order is treated as one delivery) and then run a query that says:

Starting at the furthest away, calculate distance to second furthest away, then from second furthest away, calculate distance to third furthest and so on until back at HQ.

I also have a geometry column for each customer location if that helps - e.g. is there a very easy way to do this using geometry, declaring HQ as a point first and letting SQL server work out the cumulative shortest distance back?

It's travelling salesman in a way, but a very simple version of it. I'm not looking for the shortest route per se. I'm looking for:


Order deliveries by distance from HQ
Draw line between furthest out delivery and HQ, which moves to through each point of delivery in order as determined earlier at 1
Measure length of that line.


This is the SQL I have at present to bring back the delivery locations and coordinates:

SELECT t_Addresses.Coord_X, t_Addresses.Coord_Y, t_Addresses.Cust_ID
FROM t_Addresses
WHERE t_Addresses.Cust_ID IN
(SELECT DISTINCT t_Orders.Cust_ID
FROM t_Orders
WHERE t_Orders.Del_Date='2016-05-21' AND t_Orders.Del_Sess=1)
AND (t_Addresses.Coord_Y &gt;= 0 AND t_Addresses.Coord_X &lt;= 0 AND SQUARE(t_Addresses.Coord_X) &lt; SQUARE(t_Addresses.Coord_Y)) 
OR (t_Addresses.Coord_Y &gt;= 0 AND t_Addresses.Coord_X &gt;= 0 AND (t_Addresses.Coord_X) &lt; (t_Addresses.Coord_Y))
ORDER BY SQUARE (t_Addresses.Coord_X) + SQUARE (t_Addresses.Coord_Y) DESC
OK, then this is the query, SQL Server 2012+ . I'll use test data instead of your query and omit all customer, order details but orderid. The query also doesn't use spatial features which doesn't help much here as we use no geography but plain geometry distance.

with points as(
select orderid, id, Coord_X, Coord_Y,
    rn = ROW_NUMBER() OVER(PARTITION BY orderid ORDER BY SQUARE (t.Coord_X) + SQUARE (t.Coord_Y) DESC)
from (
    values 
        (1, 1, 10, 20), 
        (1, 2, 15, 30), 
        (1, 3, 25, 5),
        (2, 1, 15, 20), 
        (2, 2, 17, 30), 
        (2, 3, 27, 8)
    ) t(orderid, id, Coord_X, Coord_Y)
)
select pfr.orderid, pfr.rn, pfr.id, pto.id,
    dist = SQRT(SQUARE(pfr.Coord_X - pto.Coord_X) + SQUARE (pfr.Coord_Y - pto.Coord_Y)),
    cumdist = SUM(SQRT(SQUARE(pfr.Coord_X - pto.Coord_X) + SQUARE (pfr.Coord_Y - pto.Coord_Y)))
        OVER (PARTITION BY pfr.orderid ORDER BY pfr.rn)
from points pfr
join points pto on pto.orderid = pfr.orderid and pto.rn=pfr.rn+1


May I say that this cumdist is far from being shortest in many cases.

Trying to use the second query from the question as a data source, not tested:

with points as(
select orderid, id, Coord_X, Coord_Y,
    rn = ROW_NUMBER() OVER(PARTITION BY orderid ORDER BY SQUARE (t.Coord_X) + SQUARE (t.Coord_Y) DESC)
from (
   -- data source
    SELECT odrerid = 1, -- the data source returns single transportation order or route i suppose
        id = t_Addresses.ID -- probably you have it. It is intended to be a separate id for every point to be visited
        Coord_X = t_Addresses.Coord_X, 
        Coord_Y = t_Addresses.Coord_Y 
    FROM t_Addresses 
    WHERE t_Addresses.Cust_ID IN (
            SELECT DISTINCT t_Orders.Cust_ID 
            FROM t_Orders 
            WHERE t_Orders.Del_Date='2016-05-21' AND t_Orders.Del_Sess=1) 
        AND ( (t_Addresses.Coord_Y &gt;= 0 AND t_Addresses.Coord_X &lt;= 0 AND SQUARE(t_Addresses.Coord_X) &lt; SQUARE(t_Addresses.Coord_Y)) 
           OR (t_Addresses.Coord_Y &gt;= 0 AND t_Addresses.Coord_X &gt;= 0 AND (t_Addresses.Coord_X) &lt; (t_Addresses.Coord_Y)) )
    --    
    ) t
)
select pfr.orderid, pfr.rn, pfr.id, pto.id,
    dist = SQRT(SQUARE(pfr.Coord_X - pto.Coord_X) + SQUARE (pfr.Coord_Y - pto.Coord_Y)),
    cumdist = SUM(SQRT(SQUARE(pfr.Coord_X - pto.Coord_X) + SQUARE (pfr.Coord_Y - pto.Coord_Y)))
        OVER (PARTITION BY pfr.orderid ORDER BY pfr.rn)
from points pfr
join points as pto on pfr.orderid = pto.orderid and  pto.rn=pfr.rn+1


I also changed the WHERE to: 

WHERE t_Addresses.Cust_ID IN (..) AND ( (..) OR (..) )


My guess is you need proper Cust_ID AND points within a quadrant.

I made some assumptions, please correct me if they are wrong.