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 |
