SQL AdvancedQuery

Question

I have the following data in a table called: dbo.DecodedCSVMessages_Staging

Here is a sample of the data:

MMSI     Message_ID    Time     Ship_Type  Vessel_Name   Latitude   Longitude

2102930      3       2012-06-01     NULL      NULL       56.528003  85.233443

2102930      5       2012-07-01      70     RIO_CUBAL      NULL      NULL

2109300      1       2012-08-01     NULL      NULL       57.43234   131.123343

2109300      1       2012-09-01     NULL      NULL       62.432345  121.123343

2109300      1       2012-09-02     NULL      NULL       65.432345  140.123343

2109300      5       2012-08-01      70     RIO_CUBAL      NULL       NULL

2225555      1       2012-08-01     NULL      NULL       66.432345  143.123343

2225555      1       2012-09-01     NULL      NULL       25.432345  145.123343

2225555      5       2012-08-01      70      RIO_II        NULL       NULL

What I need is as follows:

• An MMSI number is a unique number that represents a ship. A ship sends out different information in unique Message types. I am interested in only Message_ID 1's and 3's as they have Latitudinal and Longitudinal Information. MMSI's will be reoccuring in this database (as demonstrated in the data above)
• Problem is, I need information about the ship which is unfortunately only available in Message_ID 5's. such as Vessel_name and Ship_type.

• I only need ships which fall within the following longitude and latitude

• Ship_Type might change! In which case it must reflect the later date

  where Latitude > 55 and Latitude <85 and Longitude > 50 and Longitude < 141;

The result of querying the data would produce the following (Of which I will save to CSV format as I am using this information to build point features in ArcGIS)

MMSI     Message_ID    Time    Ship_Type  Vessel_Name   Latitude   Longitude

2102930      3       2012-06-01     70     RIO_CUBAL    56.528003  85.233443

2109300      1       2012-08-01     70     RIO_CUBAL    57.43234   131.123343

2109300      1       2012-09-01     70     RIO_CUBAL    62.432345  121.123343

2109300      1       2012-09-02     70     RIO_CUBAL    65.432345  140.123343

2225555      1       2012-08-01     70      RIO_II      66.432345  143.123343

• So all the Message_ID 5's are gone

• The second to last row is gone (as the Latitude was only 25.432345)

• The information Ship_Type and Vessel_Name has been added to the Message_ID 1's and 3's by way of linking using the MMSI.

RESTRAINTS:

1. Cannot create a new table (do no have access)
2. There are over a billion rows of data in this database (only about 4.3 million that meet the latitude/longitude constraints though

WHAT MIGHT THE QUERY BE??? tHANKS!

Answer 1

Something like this should do it:

; WITH positions AS (
  SELECT MMSI
       , Message_ID
       , "Time"
       , Latitude
       , Longitude
  FROM   dbo.DecodedCSVMessages_Staging
  WHERE  Message_ID IN (1, 3)
  AND    Latitude  > 55
  AND    Latitude  < 85
  AND    Longitude > 50
  AND    Longitude < 141
)
, details AS (
  SELECT MMSI
       , Ship_Type
       , Vessel_Name
       , Row_Number() OVER (PARTITION BY MMSI ORDER BY "Time" DESC) As row_num
  FROM   dbo.DecodedCSVMessages_Staging
  WHERE  Message_ID = 5
)
SELECT positions.MMSI
     , positions.Message_ID
     , positions."Time"
     , details.Ship_Type
     , details.Vessel_Name
     , positions.Latitude
     , positions.Longitude
FROM   positions
 INNER
  JOIN details
    ON details.MMSI    = positions.MMSI
   AND details.row_num = 1 -- Limit to "latest" ship details per MMSI