Hi,
the sql statement,
SELECT DATE,VD_MAXSPEED AS ALLOWED_SPEED,
OVERSPEED,LOCATION
FROM(
SELECT T1.DEVICE_ID,T1.DATE, T2.OVERSPEED, T1.LOCATION
FROM
(SELECT CONVERT(VARCHAR(10), OSD_UTCTIME, 121) AS [DATE],OSD_DEVICEID AS [DEVICE_ID],
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],OSD_DEVICEID AS [DEVICE_ID],
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 [OVERSPEED]
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.OVERSPEED) as [BASE]
INNER JOIN STS_VEHICLE_INFORMATION ON VD_DEVICEID=DEVICE_ID
executed successfully and got result as below,
DATE ALLOWED_SPEED OVERSPEED LOCATION
2012-07-02 30 40 tvm
2012-09-06 30 80 kozhikode
2012-10-09 30 50 idukki
I change the above sql into stored procedure as below,
--------------------------------------------------------
CREATE PROCEDURE [dbo].[STS_GET_OVER_SPEED_DETAILS_REPORT]
@DEVICEID AS VARCHAR(50),
@OSD_FROM_DATE AS DATETIME,
@OSD_TO_DATE AS DATETIME
AS
BEGIN
SELECT DATE,VD_VEHICLENUMBER AS VEHICLE_NUMBER,VD_MAXSPEED AS ALLOWED_SPEED,
OVERSPEED,LOCATION
FROM(
SELECT T1.DEVICE_ID,T1.DATE, T2.OVERSPEED, T1.LOCATION
FROM
(SELECT CONVERT(VARCHAR(10), OSD_UTCTIME, 121) AS [DATE],OSD_DEVICEID AS [DEVICE_ID],
OSD_SPEED AS [SPEED],'('+OSD_LATITUDE+', '+OSD_LONGITUDE+')' AS LOCATION
FROM STS_OVER_SPEED_DETAILS
WHERE OSD_DEVICEID = @DEVICEID
AND OSD_UTCTIME BETWEEN @OSD_FROM_DATE AND @OSD_TO_DATE) AS T1
JOIN
(SELECT CONVERT(VARCHAR(10), OSD_UTCTIME, 121) AS [DATE],OSD_DEVICEID AS [DEVICE_ID],
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 [OVERSPEED]
FROM STS_OVER_SPEED_DETAILS
WHERE OSD_DEVICEID = @DEVICEID
AND OSD_UTCTIME BETWEEN @OSD_FROM_DATE AND @OSD_TO_DATE
GROUP BY CONVERT(VARCHAR(10), OSD_UTCTIME, 121),OSD_DEVICEID) AS T2
ON T1.DATE = T2.DATE AND T1.SPEED = T2.OVERSPEED) as [BASE]
INNER JOIN STS_VEHICLE_INFORMATION ON VD_DEVICEID=@DEVICEID
END
---------------------------------------------------------------------------
But, when execute the sp using,
EXEC [dbo].[STS_GET_OVER_SPEED_DETAILS_REPORT]
'ST0001','2012-07-02','2012-11-12'
giving zero record.
why?
plz help me...
Thanks..