62008

Calculating total time in a place with SQL Server datetime

Question:

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

Answer1:

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

SELECT * FROM MyTable

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

Answer2:

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" />

Recommend

  • 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?