Idea needed to solve this query

Transact-SQL
1

I have a table with times of the timetable of workers belonging to the same shift

Start       End              Start     End
-----      -----             -----    -----
08:30      10:00             08:30    10:00
10:15      13:00             10:15    13:00
13:45      17:30     -->     13:45    14:30
                     -->     14:30    17:30

I am looking for tricky join operation that does this:

  • find the row that contains 14:30
  • replace it with two new rows where
    -- row1 goes from Start to 14:30
    -- row2 goes from 14:30 to End
  • if 14:30 already is a Start time then do nothing

Somebody has an idea?

2

I finally came up with this:

IF OBJECT_ID('tempdb..#A') IS NOT NULL
DROP TABLE #A
IF OBJECT_ID('tempdb..#B') IS NOT NULL
DROP TABLE #B

CREATE TABLE #A ( StartTime time, EndTime time)

INSERT INTO #A (StartTime, EndTime) VALUES ('08:30', '10:00')
INSERT INTO #A (StartTime, EndTime) VALUES ('10:15', '13:00')
INSERT INTO #A (StartTime, EndTime) VALUES ('13:45', '17:30')

CREATE TABLE #B ( Change time, ord int)

INSERT INTO #B (Change, ord) VALUES ('14:30', 1)
INSERT INTO #B (Change, ord) VALUES ('14:30', 2)

select CASE WHEN ord = 2 THEN Change ELSE StartTime END as StartTime, CASE WHEN ord = 1 THEN Change ELSE EndTime END as EndTime
from #A as A
left outer join #B x on StartTime < Change and EndTime > Change
3

Here's a different option that should be a bit more performant...

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
BEGIN DROP TABLE #TestData; END;

CREATE TABLE #TestData (
	beg_time TIME(0) NOT NULL,
	end_time TIME(0) NOT NULL 
	);
INSERT #TestData(beg_time, end_time) VALUES
	('08:30', '10:00'),	('10:15', '13:00'), ('13:45', '17:30'), ('18:45', '20:30');

--========================================================================

SELECT 
	beg_time = ISNULL(x.beg_time, td.beg_time),
	end_time = ISNULL(x.end_time, td.end_time)
FROM
	#TestData td
	OUTER APPLY ( 
		SELECT 
			t.beg_time,
			t.end_time
		FROM
			( VALUES (td.beg_time, '14:30'), ('14:30', td.end_time) ) t (beg_time, end_time)
		WHERE 
			td.beg_time <= '14:30'
			AND td.end_time >= '14:30'
		) x;
1 Like
4

Hi

I tried to solve it like this .. Hope it helps

Data Create Script 
IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
BEGIN DROP TABLE #TestData; END;

CREATE TABLE #TestData (
	beg_time TIME(0) NOT NULL,
	end_time TIME(0) NOT NULL 
	);
INSERT #TestData(beg_time, end_time) VALUES
	('08:30', '10:00'),	('10:15', '13:00'), ('13:45', '17:30'), ('18:45', '20:30');
SQL 
SELECT a.beg_time, 
       '14:30' 
FROM   (SELECT * 
        FROM   #testdata 
        WHERE  '14:30' BETWEEN beg_time AND end_time) a 
UNION ALL 
SELECT '14:30', 
       a.end_time 
FROM   (SELECT * 
        FROM   #testdata 
        WHERE  '14:30' BETWEEN beg_time AND end_time) a 
UNION ALL 
SELECT * 
FROM   #testdata 
WHERE  '14:30' NOT BETWEEN beg_time AND end_time 
ORDER  BY 1, 
          2
5

@Jason_A_Long: Cool, feels good! I wanted to make some brenchmark tests to compare it to my solution, but when implemented in my queries it returns less records than it should. I can't figure out why but unfortunately don't have the time now to investigate - beside the performance is pretty much the same.
Thanks anyway, apreciate your suggestion.
Martin

PS: to fulfill the last condition ("if 14:30 already is a Start time then do nothing") you need to exclude 14:30 in your where clause ->

WHERE 
		td.beg_time < '14:30'
		AND td.end_time > '14:30'

@harishgg1: Thank you for your idea. When applying to my real monster query, things are getting a bit complicated as the table #testdata appears 3 times in your query. So I stick to my solution with one single join.
The last comment for Jason applies for your query too. If 14:30 is already a starting time, no replacement should be done.