[SOLVED] how to write the 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...

Tags: SQL