Can I id users in same instr at same time?

Programming
, , , ,
1

I need a query (in either Excel, VBA, or SQL) that will check if multiple users are using the same instruction during the same time interval.

To accomplish this, I have access to the data as an export in Excel and also in a table in Hive/SQL.

I need a query (in either Excel, VBA, or SQL) that will:

  1. Check if multiple users are using the same instruction during the same time interval
  2. Keep all records of users using the same instruction during the same time interval

For example, if both Adam and Eve are using instruction 111 during the same time interval, I want to keep both of those records, or make a note beside them on both records.

The data that I’m using has millions of records, and each instruction has up to 100 users inside it at various times.

Here is an example of the data that I’m working with:

Instruction | Username | From Time          | To Time
----------- | -------- | ----------------- | -------------
111         | Adam     | 06-10-2023 00:00:00 | 06-10-2023 04:00:00
111         | Adam     | 06-10-2023 04:00:00 | 06-10-2023 05:00:00
111         | Eve      | 06-10-2023 02:00:00 | 06-10-2023 06:00:00
222         | Adam     | 06-10-2023 00:00:00 | 06-10-2023 02:00:00
222         | Adam     | 06-10-2023 02:00:00 | 06-10-2023 05:00:00
222         | Eve      | 06-10-2023 03:00:00 | 06-10-2023 04:00:00

Below is an example of the SQL query that I have tried, but have not been able to get working correctly:

SELECT tlc.instruction, tlc.userfullname, tlc.logged_start_date, tlc2.logged_start_date, tlc.logged_end_date, tlc2.logged_end_date
    FROM table1 as tlc, table1 as tlc2

    WHERE tlc.instruction = tlc2.instruction
    AND tlc.userfullname <> tlc2.userfullname
    AND tlc.logged_start_date <= tlc2.logged_start_date
    AND tlc.logged_end_date <= tlc2.logged_start_date

I need a query (in either Excel, VBA, or SQL) that will check if multiple users are using the same instruction during the same time interval.

I have access to the data in an Excel export and a table in Hive/SQL. The data contains millions of records, and each instruction has up to 100 users using it at various times.

I need a query (in either Excel, VBA, or SQL) that will check if multiple users are using the same instruction during the same time interval. Specifically, I need the query to keep all records of users using the same instruction during the same time interval.

For example, if both Adam and Eve are using instruction 111 during the same time interval, I want to keep both of those records, or make a note beside them on both records.

Below is an example of a SQL query that I have tried:

SELECT tlc.instruction, tlc.userfullname, tlc.logged_start_date, tlc2.logged_start_date, tlc.logged_end_date, tlc2.logged_end_date
    FROM table1 as tlc, table1 as tlc2

    WHERE tlc.instruction = tlc2.instruction
    AND tlc.userfullname <> tlc2.userfullname
    AND tlc.logged_start_date <= tlc2.logged_start_date
    AND tlc.logged_end_date <= tlc2.logged_start_date
2

The SQL query you provided is almost correct. You just need to change the comparison operator in the last condition. Instead of using <=, you should use >= to check if the logged end date of the first user is greater than or equal to the logged start date of the second user. Here’s the corrected query:

SELECT tlc.instruction, tlc.userfullname, tlc.logged_start_date, tlc2.logged_start_date, tlc.logged_end_date, tlc2.logged_end_date
FROM table1 as tlc, table1 as tlc2
WHERE tlc.instruction = tlc2.instruction
AND tlc.userfullname <> tlc2.userfullname
AND tlc.logged_start_date <= tlc2.logged_end_date
AND tlc.logged_end_date >= tlc2.logged_start_date

This query will give you all the records where multiple users are using the same instruction during the same time interval.