1

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
D-Shih
  • 44,943
  • 6
  • 31
  • 51
Java
  • 1,208
  • 3
  • 15
  • 29

1 Answers1

2

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
D-Shih
  • 44,943
  • 6
  • 31
  • 51
  • 1
    I am getting some syntax error inside CROSS APPLY ( ). Incorrect syntax near ')'. I just fixed it. I had to delete extra ( ). Thanks! – Java Apr 28 '22 at 01:33
  • @D-Shih What is ps & ps1 here ?Just For my understanding. – Roshan Nuvvula Apr 28 '22 at 02:13
  • 1
    @Roshan That are alias name for `CROSS APPLY ...VALUE` column here is a sample for you https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=28c3944ca8f8d88d356b5dea7341ca32 – D-Shih Apr 28 '22 at 02:21