Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL
Hi,
 
the sql statement,
 

SELECT T1.Date, T2.max_speed, T1.location
FROM
 
(SELECT CONVERT(VARCHAR(10), OSD_UTCTIME, 121) AS [Date],
	OSD_SPEED AS [speed],OSD_LOCATION AS [location]
    FROM STS_OVER_SPEED_DETAILS
    WHERE OSD_DEVICEID = 'ST0001'
    AND OSD_UTCTIME BETWEEN '2012-07-02' AND '2012-11-12') AS T1
 
JOIN
 
(SELECT  CONVERT(VARCHAR(10), OSD_UTCTIME, 121) AS [Date],
         MIN(SUBSTRING(CONVERT(VARCHAR(23), OSD_UTCTIME, 121), 12,20)) AS [start_time],
         MAX(SUBSTRING(CONVERT(VARCHAR(23), OSD_UTCTIME, 121), 12,20)) AS [stop_time],
         MAX( cast(OSD_SPEED as decimal(12,2)))  AS [max_speed]
   FROM  STS_OVER_SPEED_DETAILS
  WHERE OSD_DEVICEID = 'ST0001'
    AND OSD_UTCTIME BETWEEN '2012-07-02' AND '2012-11-12'
GROUP BY CONVERT(VARCHAR(10), OSD_UTCTIME, 121)) AS T2
 
ON T1.Date = T2.Date AND T1.speed = T2.max_speed
 
 
give result as,
 

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
 

But, i want to join the below table column value VD_MAX_SPEED with the result.
 

VD_DEVICEID     VD_MAX_SPEED
-----------     ------------
ST0001               30
ST0002               40
 

That is, the result as below
 
Date            start_time         stop_time    max_speed    location      speed
  ----              ----------     ---------       ---------  --------     -----
  2012-07-02        10:59:26.000    11:33:36.000      40       tvm          30
                                                                        
  2012-09-06        18:45:29.000   19:46:28.000      0.36      kozhikode    30
 
  2012-10-09        12:10:12.000    01:12:12.000      50       idukki       30
the sql statement,
 
SELECT Deviceid,Date,max_speed,location,VD_MAXSPEED
FROM(
 
SELECT T1.Deviceid,T1.Date, T2.max_speed, T1.location
FROM
 
(SELECT CONVERT(VARCHAR(10), OSD_UTCTIME, 121) AS [Date],OSD_DEVICEID AS [Deviceid],
	OSD_SPEED AS [speed],'('+OSD_LATITUDE+', '+OSD_LONGITUDE+')' AS location
    FROM STS_OVER_SPEED_DETAILS
    WHERE OSD_DEVICEID = 'ST0001'
    AND OSD_UTCTIME BETWEEN '2012-07-02' AND '2012-11-12') AS T1
 
JOIN
 
(SELECT  CONVERT(VARCHAR(10), OSD_UTCTIME, 121) AS [Date],OSD_DEVICEID AS [Deviceid],
         MIN(SUBSTRING(CONVERT(VARCHAR(23), OSD_UTCTIME, 121), 12,20)) AS [start_time],
         MAX(SUBSTRING(CONVERT(VARCHAR(23), OSD_UTCTIME, 121), 12,20)) AS [stop_time],
         MAX( cast(OSD_SPEED as decimal(12,2)))  AS [max_speed]
   FROM  STS_OVER_SPEED_DETAILS
  WHERE OSD_DEVICEID = 'ST0001'
    AND OSD_UTCTIME BETWEEN '2012-07-02' AND '2012-11-12'
GROUP BY CONVERT(VARCHAR(10), OSD_UTCTIME, 121),OSD_DEVICEID) AS T2
 
ON T1.Date = T2.Date AND T1.speed = T2.max_speed)
 

INNER JOIN STS_VEHICLE_INFORMATION ON SD_VEHICLEID=OSD_VEHICLEID
 

give error as Incorrect syntax near the keyword 'INNER'.
 
How can write correct sql statement?
 
plz help...
Thanks...
Posted 18-Nov-12 19:24pm
hasbina1.2K
Edited 25-Dec-12 11:49am
Maciej Los136.1K
v4
Comments
digimanus at 19-Nov-12 3:45am
   
type as L1 before line inner join
hasbina at 19-Nov-12 3:53am
   
Yes..
got the solution
digimanus at 19-Nov-12 4:06am
   
great!
digimanus at 19-Nov-12 5:07am
   
if you have the solution, please close te question
Maksud Saifullah Pulak at 28-Nov-12 14:46pm
   
Great.

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

try this
 
SELECT Deviceid,Date,max_speed,location,VD_MAXSPEED
FROM(
 
SELECT T1.Deviceid,T1.Date, T2.max_speed, T1.location
FROM
 
(SELECT CONVERT(VARCHAR(10), OSD_UTCTIME, 121) AS [Date],OSD_DEVICEID AS [Deviceid],
	OSD_SPEED AS [speed],'('+OSD_LATITUDE+', '+OSD_LONGITUDE+')' AS location
    FROM STS_OVER_SPEED_DETAILS
    WHERE OSD_DEVICEID = 'ST0001'
    AND OSD_UTCTIME BETWEEN '2012-07-02' AND '2012-11-12') AS T1
 
JOIN
 
(SELECT  CONVERT(VARCHAR(10), OSD_UTCTIME, 121) AS [Date],OSD_DEVICEID AS [Deviceid],
         MIN(SUBSTRING(CONVERT(VARCHAR(23), OSD_UTCTIME, 121), 12,20)) AS [start_time],
         MAX(SUBSTRING(CONVERT(VARCHAR(23), OSD_UTCTIME, 121), 12,20)) AS [stop_time],
         MAX( cast(OSD_SPEED as decimal(12,2)))  AS [max_speed]
   FROM  STS_OVER_SPEED_DETAILS
  WHERE OSD_DEVICEID = 'ST0001'
    AND OSD_UTCTIME BETWEEN '2012-07-02' AND '2012-11-12'
GROUP BY CONVERT(VARCHAR(10), OSD_UTCTIME, 121),OSD_DEVICEID) AS T2
 
ON T1.Date = T2.Date AND T1.speed = T2.max_speed) as tbl
 
INNER JOIN STS_VEHICLE_INFORMATION ON SD_VEHICLEID=OSD_VEHICLEID
 

Hope this helps and by accepting this answer you can mark this as closed.
--RDBurmon
  Permalink  

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 430
1 Gihan Liyanage 347
2 ClimerChinna 222
3 vikinghunter 176
4 OriginalGriff 151
0 Sergey Alexandrovich Kryukov 8,373
1 OriginalGriff 7,112
2 CPallini 2,598
3 Richard MacCutchan 2,025
4 Abhinav S 1,788


Advertise | Privacy | Mobile
Web02 | 2.8.140827.1 | Last Updated 9 Jan 2013
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