Calculating total time in a place with SQL Server datetime


I am new to SQL and I was wondering if the following is even possible to achieve.

Using SQL Server 2012 I would like to find the total time a given entity has spent in one location in minutes. There are multiple entries for each entity as they may have gone in and out of a given location many times during the day, I just want to find the total time spent in minutes for all the time spent for a given location.

The table has two datetime columns that display Starttime and Endtime, along with location ID and Entity ID.

I have managed to find a way to work out the time difference between Starttime and Endtime for one event, but I want to sum for all those events in the location to be displayed as total time.

Please let me know if I need to give any more information


What you need to do is to calculate the date difference between the locations where the entity had spent time, as below


Result Values

Starttime Endtime LocationID EntityID 2014-09-23 14:07:43 2014-09-23 16:07:43 2 2 2014-09-23 14:09:03 2014-09-23 20:09:03 3 2 2014-09-23 14:09:51 2014-09-23 21:09:51 8 2 2014-09-23 14:15:10 2014-09-23 21:15:10 8 3 2014-09-23 14:15:16 2014-09-23 17:15:16 8 3 2014-09-23 14:15:23 2014-09-23 22:15:23 4 3 2014-09-23 14:15:32 2014-09-23 15:15:32 5 3 2014-09-23 14:06:26 2014-09-23 14:06:26 1 2

Use the below query to get the desired result

SELECT DATEDIFF(MINUTE, MIN(Starttime), MAX(Endtime)) TotalTimeSpentInMinutes, LocationID FROM MyTable WHERE EntityID = 2 GROUP BY LocationID

Result values would be

TotalTimeSpentInMinutes LocationID 0 1 120 2 360 3 420 8


select LocationId , SUM(DATEDIFF(MINUTE, StartTime,EndTime)) from locationdatetime group by LocationId

<img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/xM2D4.png" data-original="https://i.stack.imgur.com/xM2D4.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" />


  • Convert int 60 to a time value of 60 minutes
  • spawn random images in canvas in javascript
  • Websocket interval: performance problems on mobile devices?
  • How to acces variable in arrayadapter getView from onItemClick
  • SoX running slow using a ProcessBuilder
  • View Azure WebJob Schedule in Portal?
  • LINQ to Entities does not recognize the method 'Int64 Max(Int64, Int64)' method, and this
  • css background images not always displayed
  • You tube videos are not playing
  • Wrapping a c#/WPF GUI around c++/cli around native c++
  • Doctrine/Symfony entity generator and generating entity from one table
  • Primefaces :radioButton inside a ui:repeat
  • R convert summary result (statistics with all dataframe columns) into dataframe
  • Why cepheus don't send int without quotes to orion?
  • GAE: Way to get reference to an HttpSession from its ID?
  • Can someone please explain to me in the most layman terms how to use EventArgs?
  • Force show.bind execution
  • Breaking out column by groups in Pandas
  • Unable to get column index with table.getColumn method using custom table Model
  • x64 applications using gdi+: what are the consequences on performance?
  • how to display data from 1st point on words on y axis for line chart in d3.js
  • AJAX Html Editor Extender upload image appearing blank
  • ActiveRecord query for a count of new users by day
  • Row Count Is Returning the incorrect number using RaptureXML
  • Illegal mix of collations for operation for date/time comparison
  • Window Size for Mac application
  • WinForms: two way TextBox problem
  • Knitr HTML Loop - Some HTML output, some R output
  • ActionScript 2 vs ActionScript 3 performance
  • To display the title for the current loaction in map in iphone
  • Traverse Array and Display in markup
  • How to delete a row from a dynamic generate table using jquery?
  • json Serialization in asp
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • Benchmarking RAM performance - UWP and C#
  • Acquiring multiple attributes from .xml file in c#
  • How to set the response of a form post action to a iframe source?
  • How to stop GridView from loading again when I press back button?
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • How can I use threading to 'tick' a timer to be accessed by other threads?