SQL Partition By Multiple Columns

Question

I have following GPS report table :

I'm expecting to group it by Lat, Long and retrieve the sequence of the resource movement :

I'm almost there with following code :

  ;WITH dt AS(
  SELECT ROW_NUMBER() OVER (Partition By ROUND(Latitude,2), ROUND(Longitude, 2),         DATEPART(hh,[GPS Time])  ORDER BY [GPS Time]) AS RowNumber,  
 ID, ResourceID, Region, [GPS Time], ROUND(Latitude,2) AS Latitude, ROUND(Longitude, 2) AS Longitude 
 FROM [dbo].[GeofenceReport] WHERE TenantID=2 AND CAST([GPS Time] AS Date) = '2014-02-11' AND ResourceID = 'MH202 (B 9349 OI)'
)
  SELECT * FROM dt WHERE RowNumber = 1 ORDER BY [GPS Time] 

But this will group all occurrence of the resource. I want it to group by sequence. So if resource leaves location A - moves to Location B - then Location C and then Location A again.

Example in SQL Fiddle

Answer 1

I don't think you can do this using a simple ranking function. Ordering by time doesn't give you the desired group id by lat/lon and ordering by geo location doesn't make much sense. What would work is to create a flag saying if lat/lon changed and using running sum of this as the sequence/group id. Then you can group by that to get your results.

with cte as
(
  SELECT
    ID, ResourceID, Region, [GPS Time], Latitude, Longitude, 
    lag(LATITUDE, 1) over(order by [gps time]) as prev_LATITUDE,
    lag(LONGITUDE, 1) over(order by [gps time]) as prev_LONGITUDE
  FROM 
    [dbo].[GeofenceReport]
)
, cte2 as
(
  select
    ID, ResourceID, Region, [GPS Time], Latitude, Longitude, 
    SUM(
      -- 1 if location changed, 0 otherwise
      CASE WHEN 
        Latitude <> prev_Latitude 
        OR Longitude <> prev_Longitude THEN 1
      ELSE 0 END
    ) OVER(ORDER BY [GPS Time]) as seq -- running sum over time
  from cte
)
select 
  min(id) as id, min(region) as region, min([GPS Time]) as [GPS Time],
  min(Latitude) as Latitude, min(Longitude) as Longitude
from cte2
group by seq

Your updated SQL fiddle