I'm trying to create a query that divides a shifts production into hourly counts that can be ran through multiple shifts yet have the first hours of each shift fall under the same counts.
Sum(CASE WHEN DATEPART(HOUR,[creation_time]) = '1' THEN quantity_increment_D ELSE 0 END) C1,
Sum(CASE WHEN DATEPART(HOUR,[creation_time]) = '2' THEN quantity_increment_D ELSE 0 END) C2,
Sum(CASE WHEN DATEPART(HOUR,[creation_time]) = '3' THEN quantity_increment_D ELSE 0 END) C3,
This query works fine, in my report, for the first shift of the day, but second and third shifts won't restart on C1. I essentially need to ask something to the effect of:
If(shift_id_S)='1' then sum(quantity_increment_D) where (creation_time) between '00:00:00' and'07:59:59' as C1
If(shift_id_S)='2' then sum(quantity_increment_D) where (creation_time) between '08:00:00' and'15:59:59' as C1
If(shift_id_S)='3' then sum(quantity_increment_D) where (creation_time) between '16:00:00' and'23:59:59' as C1
If(shift_id_S)='4' then sum(quantity_increment_D) where (creation_time) between '00:00:00' and'11:59:59' as C1
If(shift_id_S)='5' then sum(quantity_increment_D) where (creation_time) between '12:00:00' and'23:59:59' as C1
If I can get help with this part of the query, I can take care of the rest of the counts.
Aucun commentaire:
Enregistrer un commentaire