使用SQL Server datetime计算在一个地方的总时间 [英] Calculating total time in a place with SQL Server datetime

问题描述

使用SQL Server 2012我想找到给定的总时间实体在几分钟内就在一个位置上。每个实体有多个条目，因为它们可能在白天进入和退出给定位置多次，我只是想要找到在给定位置花费的所有时间花费的总时间。

该表有两个 datetime 列，显示 Starttime 和 Endtime ，以及位置ID和实体ID。

我已经设法找出一种方法来计算开始时间和 Endtime 用于一个事件，但是我想将所有这些事件的总和作为总时间显示。

请让我知道，如果我需要提供更多信息

解决方案

p>您需要做的是计算实体花费时间的地点之间的日期差异，如下所示

  SELECT 
 * 
 FROM MyTable 
  

结果值

 开始时间结束时间位置ID 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 
  

使用以下查询获取所需的结果

  SELECT 
 DATEDIFF（MINUTE，MIN（Starttime），MAX（Endtime））TotalTimeSpentInMinutes，
 LocationID 
 FROM MyTable 
 WHERE EntityID = 2 
 GROUP BY LocationID 
  

结果值为

 code> TotalTimeSpentInMinutes LocationID 
 0 1 
 120 2 
 360 3 
 420 8 
  

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

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

这篇关于使用SQL Server datetime计算在一个地方的总时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！