RE: Easy SQL help...but its Friday - Brain freeze

[allyne]

Hello,

I'm using SQL Server 2017 and have a table that looks like the below:

1_Start_Time 1_End_Time 2_Start_Time 2_End_Time
null null 11:12 11:18
null null 12:35 14:10
10:15 12:39 null null
13:59 14:36 null null

What I need to do is write a query so that the results looks like the below:

1_Start_Time 1_End_Time 2_Start_Time 2_End_Time
10:15 12:39 11:12 11:18
13:59 14:36 12:35 14:10

Thanks for all your help!

 

[DaveInIowa]

Since there is no inherent order to rows in SQL you'll need to assign an order before matching up rows. I'm going on the assumption that the lowest Start_Time_1 should match to the lowest Start_Time_2 and so on, but what happens if the are more rows of one type than the other?

;WITH t1 AS (
SELECT ROW_NUMBER() OVER (ORDER BY Start_Time_1) RowNo, Start_Time_1, End_Time_1
  FROM @Table
 WHERE Start_Time_1 IS NOT NULL
),

t2 AS (
SELECT ROW_NUMBER() OVER (ORDER BY Start_Time_2) RowNo, Start_Time_2, End_Time_2
  FROM @Table
 WHERE Start_Time_2 IS NOT NULL
)

SELECT t1.Start_Time_1, t1.End_Time_1, t2.Start_Time_2, t2.End_Time_2
  FROM t1
  FULL
  JOIN t2
    ON t1.RowNo = t2.RowNo

 

[allyne]

Thanks for the quick response! You are correct. I didn't think of that case. There can be multiple start times and less end times. not sure how to handle that situation.

Thanks much!