Join 2 tables where the first table has the time stamp and the second table has the applications opened during that time

Question

I need to join 2 tables in SQL Server. First data set has the total time spent by an agent on a particular activity during the day and the second data set is a breakup of that time spent per application during that period.

So, the first table has around 10k rows, and the second table has 60k rows.

Table 1:

Employee	Activity	Date	Start Time	End Time
Jeff	Call	12/20/2023	10:00	10:15
Jeff	Break	12/20/2023	10:16	10:30

Table 2:

Employee Name	Application	Date	Start Time Stamp	End Time Stamp
Jeff	AWS	12/20/2023	10:00	10:05
Jeff	Outlook	12/20/2023	10:06	10:10
Jeff	Teams	12/20/2023	10:11	10:15
Jeff	Chrome	12/20/2023	10:16	10:23
Jeff	Teams	12/20/2023	10:24	10:30

The resulting table should look like this:

Employee	Activity	Date	Start Time	End Time	Application	Start Time Stamp	End Time Stamp
Jeff	Call	12/20/2023	10:00	10:15	AWS	10:00	10:05
Jeff	Call	12/20/2023	10:00	10:15	Outlook	10:06	10:10
Jeff	Call	12/20/2023	10:00	10:15	Teams	10:11	10:15
Jeff	Break	12/20/2023	10:15	10:30	Chrome	10:16	10:23
Jeff	Break	12/20/2023	10:15	10:30	Teams	10:24	10:30

I tried the following code, however, each time it returns an empty table:

SELECT 
    A.Employee,
    A.Activity,
    A.Date,
    A.Start Time,
    A.End Time,
    B.Application,
    B.Start Time Stamp,
    B.End Time Stamp
FROM Employee_Data A 
RIGHT OUTER JOIN Application_Data B ON A.[Employee] = B.[Employee Name]
    AND A.Date = B.Date
    AND B.[Start Time Stamp] >= A.[Start Time]
    AND B.[End Time Stamp] <= A.[End Time]
ORDER BY
    A.[Employee],
    A.Date,
    A.[Start Time],
    A.[End Time];


Sample data:

CREATE TABLE Employee_Data(
Employee varchar(25),
Activity varchar(25),
[Date] varchar(10),
[Start Time] varchar(10),
[End Time] varchar(10))
 
insert into Employee_Data values 
('Jeff','Call', '12/12/2020','10:00','10:15'),
('Jeff','Break','12/12/2020','10:15','10:30')


CREATE TABLE Application_Data(
[Employee Name] varchar(25),
[Application] varchar(25),
[Date] varchar(10),
[Start Time Stamp] varchar(10),
[End Time Stamp] varchar(10))
 
INSERT INTO Application_Data values 
('Jeff','AWS',  '12/12/2020','10:00','10:05'),
('Jeff','Outlook','12/12/2020','10:06','10:10'),
('Jeff','Teams','12/12/2020','10:11','10:15'),
('Jeff','Chrome','12/12/2020','10:16','10:23'),
('Jeff','Teams','12/12/2020','10:24','10:30')

Answer 1

This query returns the rows whose timeranges overlaps (overlapping criteria - any range start is less than opposite range end).

SELECT *
FROM Employee_Data t1
JOIN Application_Data t2 ON t1.Employee = t2.[Employee Name]
                        AND t1.[Date] = t2.[Date]
                        AND t1.[Start Time] < t2.[End Time Stamp]
                        AND t1.[End Time] > t2.[Start Time Stamp]

Employee	Activity	Date	Start Time	End Time	Employee Name	Application	Date	Start Time Stamp	End Time Stamp
Jeff	Call	12/12/2020	10:00	10:15	Jeff	AWS	12/12/2020	10:00	10:05
Jeff	Call	12/12/2020	10:00	10:15	Jeff	Outlook	12/12/2020	10:06	10:10
Jeff	Call	12/12/2020	10:00	10:15	Jeff	Teams	12/12/2020	10:11	10:15
Jeff	Break	12/12/2020	10:15	10:30	Jeff	Chrome	12/12/2020	10:16	10:23
Jeff	Break	12/12/2020	10:15	10:30	Jeff	Teams	12/12/2020	10:24	10:30

fiddle

PS. The query assumes that the time values in VARCHAR columns contains leading zeros. I.e. not '9:15' but '09:15'.