在不同的班次中创造每小时的计数

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:

在我的报告中,此查询在当天的第一个班次中正常工作,但第二个和第三个班次不会在C1上重新启动。我基本上需要问一些影响:

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.

如果我可以获得这部分查询的帮助,我可以处理其余的计数。

1 个解决方案

#1

I'm not sure what your issue us. Do you just need to figure out the logic? Your second query doesn't seem to match what you were doing with your first query though.

我不确定你的问题是什么。你只需要找出逻辑吗?您的第二个查询似乎与您对第一个查询的查询不匹配。

You can add your other criteria to your CASE statement and maybe use another CASE for the times so you don't need separate ones. I haven't used much MySQL but this should work:

您可以将其他条件添加到CASE语句中,也可以使用其他CASE,以便您不需要单独的CASE语句。我没有使用太多的MySQL,但这应该工作:

Sum(CASE WHEN DATEPART(HH, [creation_time]) BETWEEN 
CASE WHEN shift_id_S ='1' OR shift_id_S ='4' THEN '00'
     WHEN shift_id_S ='2' THEN '08'
     WHEN shift_id_S ='3' THEN '16'
     WHEN shift_id_S ='5' THEN '12' END
AND 
CASE WHEN shift_id_S ='3' OR shift_id_S ='5' THEN '23'
     WHEN shift_id_S ='1' THEN '07'
     WHEN shift_id_S ='2' THEN '15'
     WHEN shift_id_S ='4' THEN '11' END
THEN quantity_increment_D ELSE 0 END) AS C1

Then duplicate this for the other Shifts (C2, C3...).

然后将其复制到其他移位(C2,C3 ......)。

#1