0

We have a dataset containing shift activity times from employees, looking like this: Data Table

The results set should look like this:

Date        - Employee ID - Start Time - End Time - Fixed Start Day
2020-01-02  - 17207       - 00:00      - 07:00    - NULL
2020-01-02  - 17207       - 23:00      - 07:00    - P

For most employees, they only work daytime and you can simply use min(starttime) and max(endtime) and group by date. For one project, however, the employees work night shifts. Here's where my main questions come from:

1) If their shift started the previous day, the fixed date column should show a "P"

2) When they ended a shift on the same day they started a new one, it gets hard to define the correct start/end times.

What would be the best approach for this? I had this as starting point before trying to tackle the fixed date issue, but this obviously won't do:

            SELECT      schedDate,
                        tvID,
                        NULL as 'Fixed Start Day',
                        CONVERT(char(20),MIN(StartTime),108) as 'Fixed Start Time',
                        CONVERT(char(20),MAX(EndTime),108) as 'Fixed End Time'
            FROM        agentschedule   
            WHERE       DATEPART(yy,schedDate) = 2020 
            GROUP BY    schedDate
                        tvID

I tried a case when for defining when fixed start day should be null and when it should be P, but with the start/end time I'm not sure where to start..
Important note: due to company restrictions I can't use create table... so the end result has to be a select query.

Rafaqat Ali
  • 676
  • 7
  • 26
Nebur
  • 3
  • 1

1 Answers1

0

Schematically: 

SELECT date,
       starttime,
       CASE WHEN FixedStartDay IS NULL 
            THEN endtime
            ELSE '24:00'
            END endtime
FROM table
UNION ALL
SELECT DATEADD(DAY, 1, date),
       '00:00',
       endtime
FROM table
WHERE FixedStartDay IS NOT NULL

Of course if I understand the problem correctly.

Akina
  • 39,301
  • 5
  • 14
  • 25