更新时间:2023-02-05 12:32:09
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