How to get information from a table with a specific date that does not match exactly a date in that-CodePudding

I have 2 tables. One logs activity of taxis and one is all the gps pings of the taxis.

I would like to select all the gps pings of taxis and join with the logging table to also show the driver of the taxi at the time.

I cannot figure out how to tell who was the driver on a specified date/time as it does not match a date/time in the logging table.

Tables:

Logging:
Taxi Varchar
Status int
Status_Date DateTime
Driver Varchar

GPS:
Taxi Varchar
Ping_Date DateTime
Latitude int
Longitude int

Using the following data:

Logging
Taxi Status Status_Date              Driver
5z32 6      2021-08-20 15:22:18.000  Fred
5z40 5      2021-08-20 15:29:15.000  George
5z32 6      2021-08-20 15:31:22.000  Stella
5z40 5      2021-08-20 15:36:32.000  George
5z32 5      2021-08-20 15:38:03.000  Stella

GPS
Taxi Ping_Date               Latitude Longitude
5z32 2021-08-20 15:22:18.000 4585418  -6486982
5z40 2021-08-20 15:32:48.000 4589418  -6438982
5z32 2021-08-20 15:37:18.000 4559418  -6436982
5z32 2021-08-20 15:39:48.000 4589458  -6436882

The dataset here is very small, in reality there is a ping ever 10 seconds.

The select I wish to accomplish would look like this:

Taxi Ping_Date               Latitude Longitude Driver
5z32 2021-08-20 15:22:18.000 4585418  -6486982  Fred
5z40 2021-08-20 15:32:48.000 4589418  -6438982  George
5z32 2021-08-20 15:37:18.000 4559418  -6436982  Stella
5z32 2021-08-20 15:39:48.000 4589458  -6436882  Stella

Thanks

CodePudding user response：

Thank you for posting sample data and desired output. It made this a lot easier to work with. Here is one way you could tackle this.

select * 
from GPS g
outer apply
(
    select top 1 Driver 
    from Logging l 
    where l.Status_Date <= g.Ping_Date 
        and l.Taxi = g.Taxi 
    order by l.Status_Date
) x

CodePudding user response：

Since you have a range where the ping come after the logging, you have to check forward and backward in time. This increases the complexity of the code. Here is my best guess to answer what you're aiming for.

CREATE TABLE #Logging(
    Taxi VARCHAR(30)
    ,[Status] INT
    ,Status_Date DATETIME
    ,Driver VARCHAR(30)
)

INSERT INTO #Logging (
    Taxi
   ,[Status]
   ,Status_Date
   ,Driver
)
VALUES
('5z32', 6,'2021-08-20 15:22:18.000','Fred')
,('5z40', 5,'2021-08-20 15:29:15.000','George')
,('5z32', 6,'2021-08-20 15:31:22.000','Stella')
,('5z40', 5,'2021-08-20 15:36:32.000','George')
,('5z32', 5,'2021-08-20 15:38:03.000','Stella')


CREATE TABLE #GPS(
    Taxi VARCHAR(30)
    ,Ping_DATE DATETIME
    ,Latitutde INT
    ,Longitude INT
)

INSERT INTO #GPS (
    Taxi
   ,Ping_DATE
   ,Latitutde
   ,Longitude
)
VALUES
('5z32','2021-08-20 15:22:18.000', 4585418,  -6486982),
('5z40','2021-08-20 15:32:48.000', 4589418,  -6438982),
('5z32','2021-08-20 15:37:18.000', 4559418,  -6436982),
('5z32','2021-08-20 15:39:48.000', 4589458,  -6436882)

SELECT DISTINCT
    G.Taxi
    ,G.Ping_DATE
    ,G.Latitutde
    ,G.Longitude
    ,L.Driver
FROM
    #GPS G
LEFT OUTER JOIN
    #Logging L
    ON L.Taxi = G.Taxi
    AND ABS(DATEDIFF(SECOND, L.Status_Date, G.Ping_DATE)) <= 240

You can fudge around the "240" value to move the targeted seconds range you are aiming for.