Click here to Skip to main content
15,897,704 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
C#
I want to calculate the vehicle trip but facing problem. No Groupby is working .

C#
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:

SQL
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
Posted
Updated 23-Mar-16 23:29pm
v2

1 solution

You need to use the SQL pivot
PIVOT and UNPIVOT in Sql Server | SqlHints.com[^]
 
Share this answer
 
Comments
atif_jatt 24-Mar-16 5:43am    
@ppolymorphe please give me hint on which i apply pivot
CHill60 24-Mar-16 6:00am    
Follow the link in the solution - there are hints there
Patrice T 24-Mar-16 6:04am    
:)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900