How to write the sql statement

See more:

SQL

Hi,

the SQL,

SELECT CONVERT(VARCHAR(10), TI_UTCTIME, 121)as [TRAVEL_DATE],
MIN(SUBSTRING(CONVERT(VARCHAR(23), TI_UTCTIME, 121), 1,16)) AS [START_TIME],
MAX(SUBSTRING(CONVERT(VARCHAR(23), TI_UTCTIME, 121), 1,16)) AS [STOP_TIME],
SD_STUDENTID as [STUDENT_ID],SD_STUDENTNAME as [STUDENT_NAME],
TI_DEVICEID as [DEVICE_ID]

FROM
 
(SELECT TI_UTCTIME,TI_DEVICEID,	TI_DATA
 FROM STS_TRAVELING_INFORMATION) AS T1
 
 JOIN
 
(SELECT SD_STUDENTID,SD_STUDENTNAME
 FROM STS_STUDENT_DETAILS_ WHERE SD_STUDENTNAME='Haseeb'
 AND SD_STUDENTCLASS='12') AS T2
 
 ON T1.TI_DATA LIKE '%'+T2.SD_STUDENTID
 WHERE TI_UTCTIME BETWEEN '2012-07-02' AND '2012-10-01'  
 GROUP BY CONVERT(VARCHAR(10), TI_UTCTIME, 121),TI_DEVICEID,SD_STUDENTID,SD_STUDENTNAME

retrieve the TABLE as below,

DATE        START_TIME           STOP_TIME        STUDENT_ID STUDENT_NAME DEVICE_ID
----        ----------           ---------        ---------- ---------    -------
2012-07-02 2012-07-02 11:07	2012-07-02 15:52   DT906	Haseeb	ST0001
2012-07-06 2012-07-06 18:45	2012-07-06 18:48   DT906	Haseeb	ST0001
2012-09-04 2012-09-04 12:35	2012-09-04 17:42   DT906	Haseeb	ST0001

the SQL,

SELECT DATE,VRS_DEVICEID,LOCATION,TRAVEL_DATE FROM
(
    SELECT Row_number() over (
        PARTITION BY  SUBSTRING(CONVERT(VARCHAR(23), VRS_UTCTIME, 121),1,16)
        order by CONVERT(VARCHAR(10), VRS_UTCTIME, 121),VRS_DEVICEID,'('+VRS_LATITUDE+', '+VRS_LONGITUDE+')'
        ) as rowno,CONVERT(VARCHAR(10), VRS_UTCTIME, 121)as [TRAVEL_DATE],
         VRS_DEVICEID,'('+VRS_LATITUDE+', '+VRS_LONGITUDE+')' AS LOCATION,
    SUBSTRING(CONVERT(VARCHAR(23), VRS_UTCTIME, 121),1,16) AS DATE
    FROM STS_VEHICLE_RUNNING_STATUS
)AS T1
where rowno = 1

retrieve the TABLE as below,

 DATE                VRS_DEVICEID   LOCATION     TRAVEL DATE

2012-07-02 11:07       ST001        TVM           2012-07-02

2012-07-02 11:02       ST001        KANIYAPURAM    2012-07-02

2012-07-02 15:52       ST001        KOLLAM         2012-07-02

2012-07-06 18:45       ST001        KOTTAKKAL      2012-07-06

2012-07-06 18:45       ST001        CHANKUVETTI    2012-07-06

2012-07-06 18:48       ST001        MANNARKKAD     2012-07-06

2012-09-04 12:35       ST001        KERALA         2012-09-04


2012-09-04 14:42       ST001        MAVOOR         2012-09-04

2012-09-04 17:42       ST001        KOVALAM        2012-09-04

Then ,i want to combine above two table as below,

 DATE        START_TIME        STOP_TIME       STUDENT_ID  STUDENT_NAME DEVICE_ID ENTRY_LOC  EXIT_LOC
------      ------------       ---------       ---------   -----------  --------  ---------  --------
2012-07-02  2012-07-02 11:07  2012-07-02 15:53  DT906	    Haseeb	 ST0001    TVM       KOLLAM
2012-07-06  2012-07-06 18:45  2012-07-06 18:48  DT906	    Haseeb	 ST0001    KOTTAKKAL MANNARKKAD
2012-09-04  2012-09-04 12:35  2012-09-04 17:42  DT906	    Haseeb	 ST0001    KERALA    KOVALAM

That is,

compare the START_TIME column in first table with DATE column in 2nd table, then
retrieve the corresponding LOCATION value it add to 1st table as
ENRTY_LOC column.

compare the STOP_TIME column in first table with DATE column in 2nd table, then
retrieve the corresponding LOCATION value it add to 1st table as
EXIT_LOC column

Thanks..