CODEWITHSUNDEEP
sqlsql-serverjoin
carla
carla

Reputation: 23

How to create a join between 2 tables where the date from the second table should be between 2 consecutive dates from the first table

I have two tables and I need to create a join between them:

Table 1:

Session Player Date Start
1234 A 20 Sept 2022 13:25
1234 A 20 Sept 2022 14:23
4532 B 20 Sept 2022 14:43
4532 B 20 Sept 2022 14:46
1234 A 20 Sept 2022 14:50
1456 A 20 Sept 2022 16:24
1456 A 20 Sept 2022 16:48

Table 2:

Session Player Date End
1234 A 20 Sept 2022 13:28
1234 A 20 Sept 2022 14:30
4532 B 20 Sept 2022 14:45
4532 B 20 Sept 2022 14:49
1234 A 20 Sept 2022 14:52
1456 A 20 Sept 2022 16:29
1456 A 20 Sept 2022 16:49

The Date End should be between 2 consecutive Date Start for the same session and player

Session Player Date Start Date End
1234 A 20 Sept 2022 13:25 20 Sept 2022 13:28
1234 A 20 Sept 2022 14:23 20 Sept 2022 14:30
4532 B 20 Sept 2022 14:43 20 Sept 2022 14:45
4532 B 20 Sept 2022 14:46 20 Sept 2022 14:49
1234 A 20 Sept 2022 14:50 20 Sept 2022 14:52
1456 A 20 Sept 2022 16:24 20 Sept 2022 16:29
1456 A 20 Sept 2022 16:48 20 Sept 2022 16:49

Upvotes: 1

Views: 81

Answers (2)

Thom A
Thom A

Reputation: 95574

Assuming that you can use an arbitrarily high value for the upper boundary when there isn't another row, you could use LEAD with a default value to create your range, and then JOIN with >= and </<= (depends what you need) logic in the ON:

WITH T1 AS (
    SELECT Session,
           Player,
           DateStart,
           LEAD(DateStart,1,'99991231') OVER (PARTITION BY Session, Player ORDER BY DateStart) AS NextDateStart
    FROM dbo.Table1 T1)
SELECT T1.Session,
       T1.Player,
       T1.DateStart,
       T2.DateEnd
FROM T1
     JOIN dbo.Table2 T2 ON T1.Session = T2.Session
                       AND T1.Player = T2.Player
                       AND T1.DateStart <= T2.DateEnd
                       AND T1.NextDateStart >= T2.DateEnd;

db<>fiddle

Upvotes: 2

Stigi
Stigi

Reputation: 104

i think this query can help you, try it out.

with table_1 (Session,Player,Datestart)
as
(
  Select           '1234','A','2022-09-20 13:25:00.000'
  union all Select '1234','A','2022-09-20 14:23:00.000'
  union all Select '4532','B','2022-09-20 14:43:00.000'
  union all Select '4532','B','2022-09-20 14:46:00.000'
  union all Select '1234','A','2022-09-20 14:50:00.000'
  union all Select '1456','A','2022-09-20 16:24:00.000'
  union all Select '1456','A','2022-09-20 16:48:00.000'
),
table_2 (Session,Player,Dateend)
as
(
  Select           '1234','A','2022-09-20 13:28:00.000'
  union all Select '1234','A','2022-09-20 14:30:00.000'
  union all Select '4532','B','2022-09-20 14:45:00.000'
  union all Select '4532','B','2022-09-20 14:49:00.000'
  union all Select '1234','A','2022-09-20 14:52:00.000'
  union all Select '1456','A','2022-09-20 16:26:00.000'
  union all Select '1456','A','2022-09-20 16:49:00.000'
)
Select 
    x.Session,
    x.Player,
    x.Datestart,
    y.Dateend
from (
Select 
    Session,
    Player,
    Datestart,
    ROW_NUMBER() OVER (PARTITION BY Session,Player ORDER BY Datestart) a
FROM
    table_1
) x
inner join 
(
Select 
    Session as Session_2,
    Player as PLayer_2,
    Dateend,
    ROW_NUMBER() OVER (PARTITION BY Session,Player ORDER BY Dateend) b
FROM
table_2
) as y
on 
    x.Session = y.Session_2 and
    x.Player = y.PLayer_2 and
    x.a = y.b
order by 
    x.Datestart

Upvotes: 1

Related Questions