Help needed with query

Transact-SQL
1

I have a table with working time hours in relation to the shift:

Shift    Start      End
------   -------   ------
1st      06:00      09:30
1st      10:00      14:30
Office   07:30      09:45
Office   10:00      13:15
Office   14:00      17:00

Based on that data, I am trying to build query that returns for each Shift the breaks that they are having:

Shift    Start      End
------   -------   ------
1st      09:30      10:00
Office   09:45      10:00
Office   13:15      14:00

I was trying to achieve this by first ordering the working times with Row_Number() over (partition by Shift order by Start) as rn and use that number in the join expression:

Select x.Shift, x.Finish, y.Start
From range
	left outer join times x on range.IDDia = x.IDDia and x.rn%2 = 1
    left outer join times y on range.IDDia = y.IDDia and y.rn%2 = 0

but it does not work. Anybody has an idea of how to get the result I need?

Create the data:

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

CREATE TABLE #times (Shift nvarchar(10), start time, finish time)

INSERT INTO #times (Shift, start, finish) Values ('1st', '06:00', '09:30')
INSERT INTO #times (Shift, start, finish) Values ('1st', '10:00', '14:30')
INSERT INTO #times (Shift, start, finish) Values ('Office', '07:30', '09:45')
INSERT INTO #times (Shift, start, finish) Values ('Office', '10:30', '13:15')
INSERT INTO #times (Shift, start, finish) Values ('Office', '14:00', '17:00')
3 
;WITH cte_prev_finishes AS (
    SELECT *, LAG(finish) OVER(PARTITION BY shift ORDER BY finish) AS prev_finish
    FROM #times t
)
SELECT Shift, CONVERT(varchar(5), prev_finish, 8) AS Start, 
    CONVERT(varchar(5), start, 8) AS [End]
FROM cte_prev_finishes
WHERE prev_finish IS NOT NULL AND DATEDIFF(MINUTE, prev_finish, start) > 0
ORDER BY 1, 2
1 Like
4

hi

hope this helps

Its a different solution from Scotts using JOINs

SELECT 
     a.shift
	 ,a.finish
	 ,min(b.start) 
FROM
     #times a join #times b on a.Shift=b.Shift 
WHERE
    a.finish < b.start 
GROUP BY 
     a.Shift,a.finish

image
image842×382 18.1 KB

1 Like
5

Thank you for your solutions. Right after I had posted my question yesterday, another solution had come to my mind, and I actually wanted to delete the post right afterwards, which wasn't possible. Anyway I like your solutions better and getting to know different approaches always teaches you something. Never heard about the LAG function.

My solution was:

;with data as (
select *, ROW_NUMBER() over (partition by shift order by start) as rn
from #times)

select a.shift, b.finish, a.start
from Data a
   left outer join Data b on a.Shift = b.Shift and a.rn = b.rn+1
where b.finish is not null
1 Like
6

hi

there are many ways to do the same thing
which is the best approach and why

there are other things to consider

  1. Data .. how much is it
  2. Performance Tuning ( for example from 1 hour to 2 seconds )
  3. Company specific requirements
  4. UpStream DownStream

And many others

Do not want to "BORE" you with all these

:+1: :+1: