how to write the sql?

Question

0.00/5 (No votes)

See more:

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,

SQL

 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

hasbina

Updated 17-Nov-12 1:12am

OriginalGriff

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

kankeyan · Answer 1 · 2012-11-18T18:42:00

Solution 2

SQL

SELECT CONVERT(time,VRS_UTCTIME) FROM  STS_VEHICLE_RUNNING_STATUS

above code is for sql 2008 for example to solve your problem

Posted 18-Nov-12 18:42pm

kankeyan

Comments

MT_ 19-Nov-12 0:59am

Did you read the question ?

André Kraak · Answer 2 · 2012-11-17T07:51:00

The following query gives the result you want:

SQL

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