计算车辆行程 [英] Calculate vehicle trip

问题描述

我想计算车辆行程但面临问题。没有Groupby  工作。

当前结果

 ZoneId Objectid Number commants StartTrip TimeFirst Inside 
 540 3414 VT-0678T VOLVO Oil Fd 2016-03-17 17:10 ：31 1 
 540 3414 VT-0678T VOLVO Oil Fd 2016-03-18 14:15:19 0 
 543 3414 VT-0678T VOLVO Khour 2016-03-19 09:38:30 1 
 543 3414 VT-0678T VOLVO Khour 2016-03-19 11:55:04 0 
 540 3414 VT-0678T VOLVO Oil Fd 2016-03-21 07:32:58 1 
 540 3414 VT -0678T VOLVO Oil Fd 2016-03-21 13:34:25 0 
 543 3414 VT-0678T VOLVO Khour 2016-03-22 07:29:56 1 
 543 3414 VT-0678T VOLVO Khour 2016 -03-22 10:09:58 0 
 540 3414 VT-0678T VOLVO Oil Fd 2016-03-22 15:22:41 1 
 540 3414 VT-0678T VOLVO Oil Fd 2016-03-22 16:45:02 0 
 543 3414 VT-0678T VOLVO Khour 2016-03-24 07： 41:27 1 

必填结果

 ObjId Number StartTrip EndTrip TimeFirst TimeLast 
 3414 VT-0678T Oil Fd Khour 2016-03-18 14:15:19 2016-03-21 07:32:58 
 3414 VT-0678T Oil Fd Khour 2016-03-21 13:34： 25 2016-03-22 15:22:41 
 3414 VT-0678T Oil Fd Khour 2016-03-22 16:45:02 cont 

我尝试过：

 选择 z.ZoneId，o.ObjectId，o.Number，o.Comment，z.Name StartTrip，zs.TimeFirst，zs.Inside，z.ZoneGroupId 
 
 来自 dbo.Object o  join  
 dbo.GroupObject gobj  on  o.ObjectId = gobj.ObjectId  join  
 dbo。[ Group ] g   gobj.GroupId = g.GroupId  join  
 dbo。[ZoneState] zs  on  o.ObjectId = zs.ObjectId  join  
 dbo。[区域] z   zs.ZoneId = z.ZoneId 
 其中（z.ZoneGroupId = 1096  OR  z.ZoneGroupId = 1095）和 o.ObjectId = 3414 和（ convert （ date ，zs.TimeFirst）> =   2016/2/15'）和（ convert （ date ，zs.TimeFirst）< = '  2016/3/24'）
  ORDER   BY  o.Number 

解决方案

您需要使用SQL pivot <无线电通信/>
Sql Server中的PIVOT和UNPIVOT | SqlHints.com [ ^ ]

I want to calculate the vehicle trip but facing problem. No Groupby is working .

Current Result

ZoneId  Objectid Number commants StartTrip TimeFirst          Inside 
 540    3414    VT-0678T VOLVO   Oil Fd  2016-03-17 17:10:31    1   
 540    3414    VT-0678T VOLVO   Oil Fd  2016-03-18 14:15:19    0   
 543    3414    VT-0678T VOLVO   Khour   2016-03-19 09:38:30    1   
 543    3414    VT-0678T VOLVO   Khour   2016-03-19 11:55:04    0   
 540    3414    VT-0678T VOLVO   Oil Fd  2016-03-21 07:32:58    1   
 540    3414    VT-0678T VOLVO   Oil Fd  2016-03-21 13:34:25    0
 543    3414    VT-0678T VOLVO   Khour   2016-03-22 07:29:56    1
 543    3414    VT-0678T VOLVO   Khour   2016-03-22 10:09:58    0
 540    3414    VT-0678T VOLVO   Oil Fd  2016-03-22 15:22:41    1
 540    3414    VT-0678T VOLVO   Oil Fd  2016-03-22 16:45:02    0   
 543    3414    VT-0678T VOLVO   Khour   2016-03-24 07:41:27    1   

Required Result

ObjId Number    StartTrip  EndTrip    TimeFirst            TimeLast 
3414  VT-0678T  Oil Fd     Khour    2016-03-18 14:15:19   2016-03-21 07:32:58
3414  VT-0678T  Oil Fd     Khour    2016-03-21 13:34:25   2016-03-22 15:22:41
3414  VT-0678T  Oil Fd     Khour    2016-03-22 16:45:02   cont

What I have tried:

select z.ZoneId, o.ObjectId,o.Number,o.Comment,z.Name StartTrip,zs.TimeFirst,zs.Inside,z.ZoneGroupId

from dbo.Object o join 
dbo.GroupObject gobj on o.ObjectId = gobj.ObjectId join 
dbo.[Group] g on gobj.GroupId=g.GroupId join 
dbo.[ZoneState] zs on o.ObjectId=zs.ObjectId join 
dbo.[Zone] z on zs.ZoneId=z.ZoneId
where (z.ZoneGroupId=1096 OR z.ZoneGroupId=1095) and o.ObjectId=3414 and (convert(date,zs.TimeFirst)>='2016/2/15') and (convert(date,zs.TimeFirst)<='2016/3/24')  
ORDER BY o.Number

解决方案

You need to use the SQL pivot
PIVOT and UNPIVOT in Sql Server | SqlHints.com[^]

这篇关于计算车辆行程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！