SQLTeam.com | Weblogs | Forums

Sql server query in one table database

sql2014

#1

Good evening,

I have imported in sql server a table with 4 columns: timestamp, ship, longitude, latitude. You can see an example in the photo of this link: https://scontent-mxp1-1.xx.fbcdn.net/v/t34.0-12/13401443_10207703500604055_55974542_n.png?oh=b965bf71bde84fc2146c30955a4a5415&oe=57593293 . Every row is an entry of a specific ship signal through time. The second column is the ships' number, something like their name. The rows are ordered by ascending timestamp (UNIX time). The table has no primary key. I want to find which ship and when send signal (rows in our table), with timestamp difference between next ship's entry, over 600sec and under 43200sec. To be more spesific i want to find when a ship doesn't send signal for over 10min and under 12hrs.

Thank you!


#2

If you are using SQL 2012, you can sort by ship then timestamp and use LEAD and LAG functions to obtain time difference.