1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36
| CREATE PROCEDURE [dbo].[spLastActivity]
-- Add the parameters for the stored procedure here
@pointQuantity int = 50
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT
ID,
latitude,
longitude,
date,
speed,
imei,
Vehicle.ChauffeurID,
'('+CAST(Vehicle.ChauffeurID as VarChar)+')-'+CH.naam AS ChauffeurName,
tour
FROM (SELECT
ROW_NUMBER() OVER ( PARTITION BY IMEI ORDER BY Date DESC ) AS 'RowNumber',
ID,
latitude,
longitude,
date,
speed,
imei,
tour
FROM [bcTracking].[dbo].[GPSTrace]
) dt
LEFT JOIN GPSDevice ON GPSDevice.DeviceSN = dt.imei
LEFT JOIN Vehicle ON Vehicle.DeviceID = GPSDevice.pkId
LEFT OUTER JOIN Chauffeurs CH ON Vehicle.ChauffeurID=CH.chauffeurID
WHERE RowNumber <= @pointQuantity
END |
Partager