SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table aa (
Stop_Date_Time dateTime
,MonitorID int
,SpanRef decimal(18,2)
);

insert into aa
Values('2021-03-23 15:00','1','20')
,('2021-03-24 15:00','1','20')
,('2021-03-25 15:00','1','40')
,('2021-03-26 15:00','1','40')
,('2021-03-23 15:00','2','20')
,('2021-03-24 15:00','2','20')
,('2021-03-25 15:00','2','40')
,('2021-03-26 15:00','2','40')
,('2021-03-23 15:00','3','20')
,('2021-03-24 15:00','3','20')
,('2021-03-25 15:00','3','40')
,('2021-03-26 15:00','3','40')
-- 額外加入
,('2025-03-23 08:00','1','7777');

select *
from aa;


Stop_Date_Time
MonitorID
SpanRef


2021-03-23 15:00:00.000
1
20.00

2021-03-24 15:00:00.000
1
20.00

2021-03-25 15:00:00.000
1
40.00

2021-03-26 15:00:00.000
1
40.00

2021-03-23 15:00:00.000
2
20.00

2021-03-24 15:00:00.000
2
20.00

2021-03-25 15:00:00.000
2
40.00

2021-03-26 15:00:00.000
2
40.00

2021-03-23 15:00:00.000
3
20.00

2021-03-24 15:00:00.000
3
20.00

2021-03-25 15:00:00.000
3
40.00

2021-03-26 15:00:00.000
3
40.00

2025-03-23 08:00:00.000
1
7777.00


create table bb (
Date_Time dateTime
,MonitorID int
,Value decimal(18,2)
);

insert into bb
Values('2021-03-23 16:00','1','30')
,('2021-03-26 17:00','1','50')
,('2021-03-23 16:00','2','30')
,('2021-03-26 17:00','2','50')
,('2021-03-23 16:00','3','30')
,('2021-03-26 17:00','3','50')
-- 額外加入
,('2025-03-24 14:59','1','3333');

select *
from bb;


Date_Time
MonitorID
Value


2021-03-23 16:00:00.000
1
30.00

2021-03-26 17:00:00.000
1
50.00

2021-03-23 16:00:00.000
2
30.00

2021-03-26 17:00:00.000
2
50.00

2021-03-23 16:00:00.000
3
30.00

2021-03-26 17:00:00.000
3
50.00

2025-03-24 14:59:00.000
1
3333.00


SELECT xx.Date_Time,xx.MonitorID,xx.Value,yy.Stop_Date_Time,yy.SpanRef
FROM (
SELECT CONVERT(varchar(12), DATEADD(hour,-15,bb.Date_Time), 112) AS Date_Time_New,
bb.Date_Time,bb.MonitorID,bb.Value
FROM bb) AS xx
LEFT JOIN (
SELECT CONVERT(varchar(12), aa.Stop_Date_Time, 112) AS Stop_Date_Time_New,
aa.Stop_Date_Time,aa.MonitorID,aa.SpanRef
FROM aa
) AS yy ON yy.Stop_Date_Time_New=xx.Date_Time_New AND yy.MonitorID=xx.MonitorID


Date_Time
MonitorID
Value
Stop_Date_Time
SpanRef


2021-03-23 16:00:00.000
1
30.00
2021-03-23 15:00:00.000
20.00

2021-03-26 17:00:00.000
1
50.00
2021-03-26 15:00:00.000
40.00

2021-03-23 16:00:00.000
2
30.00
2021-03-23 15:00:00.000
20.00

2021-03-26 17:00:00.000
2
50.00
2021-03-26 15:00:00.000
40.00

2021-03-23 16:00:00.000
3
30.00
2021-03-23 15:00:00.000
20.00

2021-03-26 17:00:00.000
3
50.00
2021-03-26 15:00:00.000
40.00

2025-03-24 14:59:00.000
1
3333.00
2025-03-23 08:00:00.000
7777.00

Stop_Date_Time	MonitorID	SpanRef
2021-03-23 15:00:00.000	1	20.00
2021-03-24 15:00:00.000	1	20.00
2021-03-25 15:00:00.000	1	40.00
2021-03-26 15:00:00.000	1	40.00
2021-03-23 15:00:00.000	2	20.00
2021-03-24 15:00:00.000	2	20.00
2021-03-25 15:00:00.000	2	40.00
2021-03-26 15:00:00.000	2	40.00
2021-03-23 15:00:00.000	3	20.00
2021-03-24 15:00:00.000	3	20.00
2021-03-25 15:00:00.000	3	40.00
2021-03-26 15:00:00.000	3	40.00
2025-03-23 08:00:00.000	1	7777.00

Date_Time	MonitorID	Value
2021-03-23 16:00:00.000	1	30.00
2021-03-26 17:00:00.000	1	50.00
2021-03-23 16:00:00.000	2	30.00
2021-03-26 17:00:00.000	2	50.00
2021-03-23 16:00:00.000	3	30.00
2021-03-26 17:00:00.000	3	50.00
2025-03-24 14:59:00.000	1	3333.00