Google BigQuery SQL - calculate production by each hour of the shift

1. Change the date of 3rd shift after midnight to day -1

SELECT  

    -- Change the date of 3rd shift after midnight to day -1

    IF (TIME(ProdDateTime) >= '00:00:00' and TIME(ProdDateTime) < '06:00:00',DATE_ADD(DATE(ProdDateTime), INTERVAL -1 DAY),DATE(ProdDateTime)) as Production_Date

...

2. Calculate the shift of production

SELECT  

    -- Calculate the shift

    IF (TIME(ProdDateTime) >= '06:00:00' and TIME(ProdDateTime) < '14:00:00', "1",

      IF (TIME(ProdDateTime) >= '14:00:00' and TIME(ProdDateTime) < '22:00:00', "2","3"))

    as Shift

...

3. Calculate the time difference between the beginning of the shift and production time

SELECT

 -- Calculate the time difference between the begining of the shift and production date

IF (TIME(ProdDateTime) >= '06:00:00' and TIME(ProdDateTime) < '14:00:00',TIME_DIFF(TIME(ProdDateTime),'06:00:00',HOUR)+1,

IF (TIME(ProdDateTime) >= '14:00:00' and TIME(ProdDateTime) < '22:00:00', TIME_DIFF(TIME(ProdDateTime),'14:00:00',HOUR)+1,

IF (TIME(ProdDateTime) >= '22:00:00' and TIME(ProdDateTime) <= '23:59:59', TIME_DIFF(TIME(ProdDateTime),'22:00:00',HOUR)+1,

TIME_DIFF(TIME(ProdDateTime),'00:00:00',HOUR)+1))) 

as HOUR_OF_PROD

...

4. Create the pivot table

WITH Production as

(

  SELECT  

    WorkCenter, SerialNumber, 

    -- Change the date of 3rd shift after midnight to day -1

    IF (TIME(ProdDateTime) >= '00:00:00' and TIME(ProdDateTime) < '06:00:00',DATE_ADD(DATE(ProdDateTime), INTERVAL -1 DAY),DATE(ProdDateTime)) as Production_Date,

    

    -- Calculate the shift

    IF (TIME(ProdDateTime) >= '06:00:00' and TIME(ProdDateTime) < '14:00:00', "1",

      IF (TIME(ProdDateTime) >= '14:00:00' and TIME(ProdDateTime) < '22:00:00', "2","3"))

    as Shift,

    -- Calculate the time difference between the begining of the shift and production date

    IF (TIME(ProdDateTime) >= '06:00:00' and TIME(ProdDateTime) < '14:00:00',TIME_DIFF(TIME(ProdDateTime),'06:00:00',HOUR)+1,

      IF (TIME(ProdDateTime) >= '14:00:00' and TIME(ProdDateTime) < '22:00:00', TIME_DIFF(TIME(ProdDateTime),'14:00:00',HOUR)+1,

      IF (TIME(ProdDateTime) >= '22:00:00' and TIME(ProdDateTime) <= '23:59:59', TIME_DIFF(TIME(ProdDateTime),'22:00:00',HOUR)+1,

      TIME_DIFF(TIME(ProdDateTime),'00:00:00',HOUR)+1))) 

    as HOUR_OF_PROD

FROM `PROJECT.TEST.TABLE`

)

select * from Production

PIVOT(COUNT(SerialNumber) FOR HOUR_OF_PROD IN (1,2,3,4,5,6,7,8))

5. Results