Click here to Skip to main content
12,503,635 members (51,454 online)
Rate this:
 
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.4K
Updated 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 19-Nov-12 11:20am
   
Great work! +5!
anil.luck 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 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
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160919.1 | Last Updated 19 Nov 2012
Copyright © CodeProject, 1999-2016
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