I am struggling how to approach this concept in SQL. I have the first layout as a return in SQL:

Emp   | Start |  End  | Option | 
Bob   | 7:30  | 16:00 | Shift  |
Bob   |10:15  | 10:30 | Break  |
Bob   |12:45  | 13:15 | Lunch  |
Bob   |15:00  | 15:15 | Break  |
Frank | 8:30  | 17:00 | Shift  |
Frank |10:30  | 10:45 | Break  |
Frank |12:00  | 12:30 | Lunch  |
Frank |15:15  | 15:30 | Break  |

I am looking for my query to return a whole shift breakdown:

Emp   | Start |  End  | Option | 
Bob   | 7:30  | 10:15 | Work   |
Bob   |10:15  | 10:30 | Break  |
Bob   |10:30  | 12:45 | Work   |
Bob   |12:45  | 13:15 | Lunch  |
Bob   |13:15  | 15:00 | Work   |
Bob   |15:00  | 15:15 | Break  |
Bob   |15:15  | 16:00 | Work   |
Frank | 8:30  | 10:30 | Work   |
Frank |10:30  | 10:45 | Break  |
Frank |10:45  | 12:00 | Work   |
Frank |12:00  | 12:30 | Lunch  |
Frank |12:30  | 15:15 | Work   |
Frank |15:15  | 15:30 | Break  |
Frank |15:30  | 17:00 | Work   |

I was thinking of using a case statement but I didn't know how to approach taking the shift and breaking it down across the remainder of the shift as work. Any help or recommendations are appreciated.

CodePudding user response：

So, let's do this with the following caveats:

1. Your table layout is precisely how you indicated at the top where you have four rows per person.
2. Each person has a pattern of WORK-BREAK-WORK-LUNCH-WORK-BREAK-WORK.

You can use a combination of CTE's with some UNIONed queries such as this. I changed a few column names because they are reserved words (start, end, option).

  with breaks as (
  select emp, c_start, c_end, rank() over (partition by emp order by c_start asc) as b_rank
  from my_table
  where category = 'Break'
  ), 
  lunch as (
  select emp, c_start, c_end
  from my_table
  where category = 'Lunch'
  ), 
  shift as (
  select emp, c_start, c_end
  from my_table
  where category = 'Shift'
  )
  select distinct t.emp, s.c_start as c_start, b.c_start as c_end, 'Work' as category
    from my_table t 
    join breaks b on t.emp = b.emp 
    join shift s on t.emp = s.emp 
   where b.b_rank = 1
  union
  select distinct t.emp, b.c_start as c_start, b.c_end as c_end, 'Break' as category
    from my_table t 
    join breaks b on t.emp = b.emp 
   where b.b_rank = 1
  union
  select distinct t.emp, b.c_end as c_start, l.c_start as c_end, 'Work' as category
    from my_table t 
    join breaks b on t.emp = b.emp 
    join lunch l on t.emp = l.emp
   where b.b_rank = 1
  union
  select distinct t.emp, l.c_start as c_start, l.c_end as c_end, 'Lunch' as category
    from my_table t 
    join lunch l on t.emp = l.emp
  union
  select distinct t.emp, l.c_end as c_start, b.c_start as c_end, 'Work' as category
    from my_table t 
    join lunch l on t.emp = l.emp
    join breaks b on t.emp = b.emp
   where b.b_rank = 2
  union
  select distinct t.emp, b.c_start as c_start, b.c_end as c_end, 'Break' as category
    from my_table t 
    join breaks b on t.emp = b.emp
   where b.b_rank = 2
  union
  select distinct t.emp, b.c_end as c_start, s.c_end as c_end, 'Work' as category
    from my_table t 
    join breaks b on t.emp = b.emp
    join shift s on t.emp = s.emp
   where b.b_rank = 2
  order by 1,2

Output:

Db-fiddle can be found here.

CodePudding user response：

Here's a solution that's a little more terse. It can accommodate any number of breaks, and you can define additional break types if you want. I think the only assumption is that work always follows a break, but that could probably be adjusted if need be.

BEGIN
declare @temp table (emp varchar(10),t1 time, t2 time, opt varchar(10))
declare @index table (optval varchar(10),eval int,shiftname varchar(10))
insert into @temp values  --this is your original data
('Bob','7:30','16:00','Shift'),
('Bob','10:15','10:30','Break'),
('Bob','12:45','13:15','Lunch'),
('Bob','15:00','15:15','Break'),
('Frank','8:30','17:00','Shift'),
('Frank','10:30','10:45','Break'),
('Frank','12:00','12:30','Lunch'),
('Frank','15:15','15:30','Break')
insert into @index values  --auxiliary definitions
('Break',0,'Work'),
('Break',1,'Break'),
('Lunch',0,'Work'),
('Lunch',1,'Lunch'),
('Shift',0,'error'),
('Shift',1,'Work')

select aa.emp,aa.s,aa.t1,aa.shiftname from
(select a.*,lag(a.t1,1) over(partition by emp order by a.emp,a.t1) as s,i.shiftname  from
((select emp,t1,opt,0 as e from @temp)
union all
(select emp,t2,opt,1 as e from @temp)) a
left join @index i on i.optval=a.opt and i.eval=a.e
) aa
where aa.s is not null
order by aa.emp,aa.s
END

And the results:

Thanks for reading, let me know if you want additional commentary!