Display Two columns at the same time using Cross Apply

April 28, 2022

I have pretty much the same logic on the bottom but I am not able to put together to display two columns.

I am not sure how to combine two Cross Apply queries into one.

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

>Solution :

If I understand correctly, you can try combining two-column in 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