Click here to Skip to main content
11,581,515 members (69,689 online)
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.3K
Edited 25-Dec-12 11:49am
Maciej Los186.5K
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 OriginalGriff 340
1 Sergey Alexandrovich Kryukov 160
2 Frankie-C 105
3 F-ES Sitecore 85
4 virusstorm 84
0 OriginalGriff 1,273
1 Sergey Alexandrovich Kryukov 900
2 Abhinav S 617
3 F-ES Sitecore 500
4 Suvendu Shekhar Giri 433


Advertise | Privacy | Mobile
Web04 | 2.8.150603.1 | Last Updated 9 Jan 2013
Copyright © CodeProject, 1999-2015
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