Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL
HI,
 
i have a table with data as below,
 

  VRS_DEVICEID          VRS_UTCTIME                    VRS_SPEED    VRS_LOCATION
 -------------          -----------                    ---------    -------------
 
  ST0001                2012-07-02 10:59:26.000        0.36         attigal
  ST0001                2012-07-02 11:33:36.000        40           tvm
  ST0001                2012-09-06 18:45:29.000        0.26         malappuram
  ST0001                2012-09-06 19:46:28.000        0.36         kozhikode
  ST0001                2012-10-09 12:10:12.000        50           idukki
  ST0001                2012-10-09 01:12:12.000        40           vayanadu
 
the sql statement,
 
 SELECT CONVERT(VARCHAR(10), VRS_UTCTIME, 121) AS [Date],
MIN(SUBSTRING(CONVERT(VARCHAR(23), VRS_UTCTIME, 121), 12,20)) AS [start_time],
MAX(SUBSTRING(CONVERT(VARCHAR(23), VRS_UTCTIME, 121), 12,20)) AS [stop_time],
MAX(VRS_SPEED) AS [max_speed]
FROM STS_VEHICLE_RUNNING_STATUS
WHERE VRS_DEVICEID = 'ST0001' AND  VRS_UTCTIME BETWEEN '2012-07-02' AND '2012-11-09'
GROUP BY CONVERT(VARCHAR(10), VRS_UTCTIME, 121)
 
retrieve data from table as below,
 

 Date            start_time         stop_time    max_speed   
  ----              ----------     ---------       ---------
  2012-07-02        10:59:26.000    11:33:36.000      40
  
  2012-09-06        18:45:29.000   19:46:28.000      0.36
 
  2012-10-09        12:10:12.000    01:12:12.000      50
 
But, i want to retrieve the location column with the result.That is,
retrieve the location at max_speed value as below
 
 Date            start_time         stop_time    max_speed    location
  ----              ----------     ---------       ---------  --------
  2012-07-02        10:59:26.000    11:33:36.000      40       tvm
  
  2012-09-06        18:45:29.000   19:46:28.000      0.36      kozhikode
 
  2012-10-09        12:10:12.000    01:12:12.000      50       idukki
 
Then how to write sql statement?
 
plz help me...
 
Thanks...>
 
[edit]Code block added - OriginalGriff[/edit]
Posted 17-Nov-12 1:06am
hasbina1.2K
Edited 17-Nov-12 1:12am
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

The following query gives the result you want:
SELECT T1.Date, T2.start_time, T2.stop_time, T2.max_speed, T1.location
FROM
 
(SELECT CONVERT(VARCHAR(10), VRS_UTCTIME, 121) AS [Date],
	VRS_SPEED AS [speed],
        VRS_LOCATION AS [location]
   FROM STS_VEHICLE_RUNNING_STATUS
  WHERE VRS_DEVICEID = 'ST0001'
    AND VRS_UTCTIME BETWEEN '2012-07-02' AND '2012-11-09') AS T1
 
JOIN
 
(SELECT  CONVERT(VARCHAR(10), VRS_UTCTIME, 121) AS [Date],
         MIN(SUBSTRING(CONVERT(VARCHAR(23), VRS_UTCTIME, 121), 12,20)) AS [start_time],
         MAX(SUBSTRING(CONVERT(VARCHAR(23), VRS_UTCTIME, 121), 12,20)) AS [stop_time],
         MAX(VRS_SPEED) AS [max_speed]
   FROM  STS_VEHICLE_RUNNING_STATUS
  WHERE  VRS_DEVICEID = 'ST0001'
    AND  VRS_UTCTIME BETWEEN '2012-07-02' AND '2012-11-09'
GROUP BY CONVERT(VARCHAR(10), VRS_UTCTIME, 121)) AS T2
 
ON T1.Date = T2.Date AND T1.speed = T2.max_speed
  Permalink  
Comments
Maciej Los at 19-Nov-12 11:20am
   
Great work! +5!
anil.luck at 19-Nov-12 12:28pm
   
@André Kraak
Excellent job
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

SELECT CONVERT(time,VRS_UTCTIME) FROM  STS_VEHICLE_RUNNING_STATUS
 

above code is for sql 2008 for example to solve your problem
  Permalink  
Comments
Milind Thakkar at 19-Nov-12 0:59am
   
Did you read the question ?

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 598
1 OriginalGriff 235
2 George Jonsson 230
3 CPallini 210
4 PIEBALDconsult 150
0 OriginalGriff 5,835
1 Sergey Alexandrovich Kryukov 5,263
2 CPallini 4,750
3 George Jonsson 3,227
4 Gihan Liyanage 2,487


Advertise | Privacy | Mobile
Web04 | 2.8.140916.1 | Last Updated 19 Nov 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100