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 |
CodePudding user response：
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
CodePudding user response：
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;