使用交叉应用同时显示两列

Question

我在底部有大致相同的逻辑，但我不能放在一起显示两列。

我不知道如何将两个交叉应用查询合并为一个。

select


DateAdd(hour,hour_diff, ps)   punch_start
from [dbo].[Stage]
cross apply
(
values
( Try_Convert(datetime, punch_start) )
) x
(ps)
left join 
[dbo].[DIM] d on ps between d.start_dt and  d.end_dt

,
DateAdd(hour,hour_diff, ps1)  punch_end
from [dbo].[Stage]
cross apply
(
values
( Try_Convert(datetime, punch_end) )
) y
(ps1)
left join 
[dbo].[DIM] d on ps1 between d.start_dt and  d.end_dt


punch_start
Mar 29 2022  3:00AM
Mar 23 2022  6:28PM
Apr 11 2022  3:12AM
Apr  5 2022 10:18AM
Mar 30 2022  7:00AM
Apr  7 2022  2:57AM
Apr  6 2022  8:00PM
Mar 23 2022  2:44AM
Mar 24 2022 12:00PM
Apr 14 2022  7:18AM


punch_end
Mar 29 2022  7:50AM
Mar 23 2022  7:59PM
Apr 11 2022  9:33AM
Apr  5 2022  2:08PM
Mar 30 2022 10:39AM
Apr  7 2022  7:35AM
Apr  6 2022  9:32PM
Mar 23 2022  7:03AM
Mar 24 2022  7:01PM
Apr 14 2022  7:48AM

Answer 1

如果我理解正确，您可以尝试在VALUES中组合两列。

SELECT
    DateAdd(hour,hour_diff, ps)  punch_start,
    DateAdd(hour,hour_diff, ps1)  punch_end
FROM [dbo].[Stage]
CROSS APPLY
(
    values (Try_Convert(datetime, punch_start),Try_Convert(datetime, punch_end))
) x (ps,ps1)
left join 
[dbo].[DIM] d on 
    ps between d.start_dt and  d.end_dt
OR 
    ps1 between d.start_dt and  d.end_dt