Question

我正在生成线路转移报告：

在我的申请中，我提供了一个下拉列表，用于选择班次和线路，他们将从日历中选择日期

我有三班

shift1 starts at 7am and ends at 3pm
shift2 starts at 3pm and ends at 11pm
shift3 starts at 11pm and ends at 3am

我有一个名为数据记录的表，其中将存储登录信息，如下所示：

Name       Shiftname       ID          operatorname   Date           plantname    line     Machine
Pradeepa  Shift2(11-7)     3           Operator 3     2011-05-28     Plant 3      Line5    mc10
Ashwini   Shift1(7-3)      1           Operator 1     2011-05-29     Plant 3      Line6    mc12
Deepika   Shift2(11-7)     2           Operator 3     2011-05-29     Plant 5      Line9    mc18
Ashwini   Shift1(7-3)      1           Operator 1     2011-05-24     Plant 1      Line1    mc1
Deepika   Shift2(3-11)     2           Operator 2     2011-05-24     Plant 2      Line3    mc5
Ashwini   Shift2(3-11)     1           Operator 2     2011-05-25     Plant 2      Line3    mc5

等等

我有一个参数表，比如温度、压力、ph、速度、二氧化碳等

温度表包含以下数据，该表将包含从早上7点到凌晨3点的所有读数

Temperature      Time              Date
27               13:13:54.000      2011-05-25
27.3             13:14:04.000      2011-05-25 
27.6             13:14:14.000      2011-05-25 
27.9             13:14:24.000      2011-05-25
28.2             13:14:34.000      2011-05-25 
28.5             13:14:44.000      2011-05-25 
27               16:13:29.000      2011-05-26 
27               16:13:31.000      2011-05-26

等等

The user will select a line from dropdownlist and shift and he will select a date from th calender If the user select shift2,line3 and date 25/05/2011 what are the readings are there between 3pm to 11pm should be displayed in my report

我的报告应该是：

Machine     Shiftname   Date               Time             Temperature
mc5         Shift2      25/05/2011         13:13:54.000     27
mc5         Shift2      25/05/2011         13:14:04.000     27.3
mc5         Shift2      25/05/2011         13:14:14.000     27.6

我也在做线路日报

其中shiftname应根据时间更改，例如，如果时间更改为23:00:00，则在我的报告中，shiftname应该更改为shift3

如果用户选择了特定的班次和日期，例如，如果用户选择班次1、第1行和日期，我的报告应该包含早上7点到下午3点之间的所有读数

有人能帮我吗。

Answer 1

您可以通过以下查询获得报告

SELECT d.Machine
       , CASE WHEN t.time BETWEEN  19:00:00.000  AND  23:59:59.999  THEN  Shift1 
              WHEN t.time BETWEEN  00:00:00.000  AND  02:59:59.999  THEN  Shift1 
              WHEN t.time BETWEEN  03:00:00.000  AND  10:59:59.999  THEN  Shift2 
              WHEN t.time BETWEEN  11:00:00.000  AND  18:59:59.999  THEN  Shift3 
          END
       , t.Date
       , t.Time
       , t.Temperature
FROM   Datalogging d
       INNER JOIN Temperature t ON t.Date = d.Date
WHERE  d.Shifname =  Shift2(3-11) 
       AND d.Line =  Line3 
       AND t.Date =  25/05/2011

但是，如果我们可以假设每台机器每天都有温度读数，那么很明显，temperature和Datalogging表之间缺少关系。

友情链接