CODEWITHSUNDEEP
sql-serverrecursive-query
Bullfrog
Bullfrog

Reputation: 229

SQL get start of shift and end of shift based off consecutive tasks

I am in need of getting the true length of a shift based off the tasks in the shift. There is no record of start and end time of a shift only start and end of each task. I have been trying to get the time the shift starts and the time the shift ends. Or the length of the shift measured in minutes.

|----|-------------|-----------|-------------------|-------------------|-----------|--------|  
| ID | EMPLOYEE_ID | TASK_TYPE |        START_TIME |          END_TIME | START_DAY | LENGTH |
|----|-------------|-----------|-------------------|-------------------|-----------|--------|
|  1 |       12344 |    TASK A | 28-Sep-2018 11:00 | 28-Sep-2018 12:00 |     43371 |     60 |
|  2 |       12344 |    TASK C | 28-Sep-2018 12:00 | 28-Sep-2018 19:00 |     43371 |    420 |
|  3 |      457547 |    TASK C | 28-Sep-2018 19:00 | 28-Sep-2018 21:00 |     43371 |    120 |
|  4 |      457547 |    TASK F | 28-Sep-2018 21:00 | 28-Sep-2018 23:00 |     43371 |    120 |
|  5 |      457547 |    TASK C | 28-Sep-2018 23:00 | 29-Sep-2018 02:00 |     43371 |    180 |
|  6 |       12344 |    TASK A | 30-Sep-2018 08:00 | 30-Sep-2018 14:00 |     43373 |    360 |
|----|-------------|-----------|-------------------|-------------------|-----------|--------|

The following string of SQL creates the table above.

SELECT quintiq_id, 
   employee_id, 
   task_type, 
   Cast(start_time AS DATETIME)               AS START_TIME, 
   Cast(end_time AS DATETIME)                 AS END_TIME, 
   Cast(Cast(start_time AS DATE) AS DATETIME) AS START_DAY, 
   Datediff(minute, start_time, end_time)     AS LENGTH 
FROM   daysheet_active_shift 
WHERE  ( NOT task_type = 'OFF' ) 
   AND ( NOT task_type = 'OFF*' ) 
   AND employee_id <> 0;

The filtering of the task_type OFF and OFF* is important because they link all the tasks together so there is an off start and end time that is between each shift.

I only have read access to the backend of the database and only have Access 2013 to run the query as a Pass-Through query. I have tried to convert the query above to become a recursive query to get the total length of the shift. However i can't get it to run at all. Yes cumulative queries do work on the server. Any ideas??

WITH L AS (
       SELECT QUINTIQ_ID,
                    EMPLOYEE_ID, 
                    TASK_TYPE, 
                    START_TIME, 
                    END_TIME, 
                    datediff(minute, START_TIME, END_TIME) as LENGTH, 
                    0 as TOT 
       FROM DAYSHEET_ACTIVE_SHIFT 
       WHERE QUNITIQ_ID IN (
              SELECT A.QUINTIQ_ID
              FROM DAYSHEET_ACTIVE_SHIFT AS A LEFT JOIN DAYSHEET_ACTIVE_SHIFT AS B ON
                     (A.EMPLOYEE_ID = B.EMPLOYEE_ID) And (A.START_TIME = B.END_TIME)
              WHERE B.QUINTIQ_ID IS NOT NULL 
                            AND (NOT A.TASK_TYPE = 'OFF') 
                            AND (NOT A.TASK_TYPE = 'OFF*') 
                            AND A.EMPLOYEE_ID <> 0
              )
      UNION ALL

      SELECT C.QUINTIQ_ID,
                    C.EMPLOYEE_ID, 
                    C.TASK_TYPE, 
                    C.START_TIME, 
                    C.END_TIME, 
                    datediff(minute, C.START_TIME, C.END_TIME) as LENGTH, 
                    C.TOT + D.TOT as TOT 
      FROM  DAYSHEET_ACTIVE_SHIFT AS C JOIN L AS D ON 
                    (C.EMPLOYEE_ID = D.EMPLOYEE_ID) And (D.START_TIME = 
C.END_TIME)
       )

SELECT * FROM L

What I want the query to produce:

|----|-------------|-----------|-------------------|-------------------|-----------|--------|-----|  
| ID | EMPLOYEE_ID | TASK_TYPE |        START_TIME |          END_TIME | START_DAY | LENGTH | TOT |
|----|-------------|-----------|-------------------|-------------------|-----------|--------|-----|
|  1 |       12344 |    TASK A | 28-Sep-2018 11:00 | 28-Sep-2018 12:00 |     43371 |     60 | 480 |
|  2 |       12344 |    TASK C | 28-Sep-2018 12:00 | 28-Sep-2018 19:00 |     43371 |    420 | 480 |
|  3 |      457547 |    TASK C | 28-Sep-2018 19:00 | 28-Sep-2018 21:00 |     43371 |    120 | 420 |
|  4 |      457547 |    TASK F | 28-Sep-2018 21:00 | 28-Sep-2018 23:00 |     43371 |    120 | 420 |
|  5 |      457547 |    TASK C | 28-Sep-2018 23:00 | 29-Sep-2018 02:00 |     43371 |    180 | 420 |
|  6 |       12344 |    TASK A | 30-Sep-2018 08:00 | 30-Sep-2018 14:00 |     43373 |    360 | 360 |

|  7 |       12344 |    TASK A | 02-Oct-2018 06:00 | 02-Sep-2018 14:00 |     43375 |    480 | 480 |

|  8 |       12344 |    TASK A | 02-Oct-2018 23:00 | 03-Oct-2018 06:00 |     43375 |    420 | 420 |

|  9 |       12344 |    TASK A | 06-Oct-2018 08:00 | 06-Oct-2018 09:00 |     43379 |     60 | 420 |
| 10 |       12344 |    TASK B | 06-Oct-2018 09:00 | 06-Oct-2018 15:00 |     43379 |    360 | 420 |

| 11 |       12344 |    TASK A | 06-Oct-2018 22:00 | 07-Oct-2018 04:00 |     43379 |    360 | 480 |
| 12 |       12344 |    TASK A | 07-Oct-2018 04:00 | 06-Oct-2018 06:00 |     43380 |    120 | 480 |
|----|-------------|-----------|-------------------|-------------------|-----------|--------|-----|

Upvotes: 0

Views: 112

Answers (1)

iSR5
iSR5

Reputation: 3498

is this what are you looking for ? 

SELECT 
    quintiq_id               
,   employee_id     
,   task_type       
,   start_time      
,   end_time
,   DATEDIFF(DAY, '1899-12-30T00:00:00', start_time)  START_DAY
,   CASE WHEN DATEDIFF(MINUTE, start_time, end_time) < 1 THEN DATEDIFF(MINUTE, end_time, start_time) ELSE DATEDIFF(MINUTE, start_time, end_time) END  [LENGTH]
,   SUM(CASE WHEN DATEDIFF(MINUTE, start_time, end_time) < 1 THEN DATEDIFF(MINUTE, end_time, start_time) ELSE DATEDIFF(MINUTE, start_time, end_time) END) OVER(PARTITION BY EMPLOYEE_ID,  CAST(end_time AS DATE)) TOT
FROM 
    daysheet_active_shift
WHERE  
    ( NOT task_type = 'OFF' ) 
AND ( NOT task_type = 'OFF*' ) 
AND employee_id <> 0

Upvotes: 1

Related Questions