SQL query to find the visitor together with the date time

Question

My visitor log table has id, visitor, department,vtime fields.

    id  |   visitor |           Visittime    |     Department_id
    --------------------------------------------------------------
    1         1           2019-05-07 13:53:50           1 
    2         2           2019-05-07 13:56:54           1
    3         1           2019-05-07 14:54:10           3
    4         2           2019-05-08 13:54:49           1
    5         1           2019-05-08 13:58:15           1 
    6         2           2019-05-08 18:54:30           2
    7         1           2019-05-08 18:54:37           2

And I have already have the following index

CREATE INDEX Idx_VisitorLog_Visitor_VisitTime_Includes ON VisitorLog 
    (Visitor, VisitTime) INCLUDE (DepartmentId, ID)

From the above table 4 filters are passed from User interface, visitor 1 and visitor 2 and visiting start time and end time.

In what are the department visitor 1 and visitor 2 both together with the VisitTime difference with in 5 mins those need to be filtered

Output shout be

    id  |   visitor |           Visittime    |     Department_id
    --------------------------------------------------------------
    1         1           2019-05-07 13:53:50           1 
    2         2           2019-05-07 13:56:54           1
    4         2           2019-05-08 13:54:49           1
    5         1           2019-05-08 13:58:15           1 

For that I had used the following query,

;with CTE1 AS(
Select id,visitor,Visittime,department_id from visitorlog where visitor=1
)
,CTE2 AS(
Select id,visitor,Visittime,department_id from visitorlog where visitor=2
)
select * from CTE2 V2
Inner join CTE1 V1 on V2.department_id=V1.department_id and DATEDIFF(minute,V2.Visittime,V1.Visittime)between -5 and 5**

The above query takes too much of time to give response. Because in my table, almost 20 million records are available

Could any one suggest the correct way for my requirement. Thanks in advance

Answer 1

This is a completely revised answer, based upon your additional information above.

After reviewing the data file above and the results you desire, this seems like the cleanest way to provide your results. First, we need a different index:

create index idx_POC_visitorlog on visitorlog
    (visitor, Department_id, Visittime) include(id);

With this index, we can limit the queries to only the two passed in IDs. To simulate that, I created variables to hold their values. This query returns the data you are looking for.

DECLARE @Visitor1 int = 1,
    @Visitor2 int = 2

;with t as (
    select Department_id,
        dateadd(minute, -5, visittime) as EarlyTime,
        dateadd(minute, 5, Visittime) as LateTime,
        id
    from visitorlog
    where visitor = @Visitor1
    ),
v as (
    select v.id, 
        t.id as tid
    from visitorlog v
    INNER JOIN t 
        ON v.visitor = @Visitor2
        AND v.Department_id = t.Department_id
        and v.Visittime BETWEEN t.EarlyTime and t.LateTime
    )
SELECT *
FROM visitorlog vl
WHERE ID IN (
    SELECT v.id
    FROM v
    UNION
    SELECT v.tid
    FROM v
    )
ORDER BY visittime;