sql - 在值为零的情况下返回标称值时出错

问题描述

下面的代码块在大多数情况下都能完美运行。问题是我正在尝试为特定行“注入”标称值，而该行不存在任何值...

  DECLARE @ReportTimeStart DATETIME = '2019-05-10 06:59:00'
  DECLARE @ReportTimeEnd DATETIME = '2019-05-10 23:01:00'
  ;

  --The "WITH" block returns a list of the total time spent in the state for each event in the list from the sub-query
  WITH StateTimes AS
  (
    SELECT
        [tbl_tg_machinestate_ndx],
        [Machine_State],
        DATEDIFF(second, [t_stamp], NextStateTime) AS TimeInState
    FROM
      (
      --This sub-query block ("SQ") returns a list of start and end times for each event. NULL value in the last recrd is replaced with the report end time.
      SELECT
            [tbl_tg_machinestate_ndx],
            [Machine_State],
            [t_stamp],
            ISNULL(LEAD([t_stamp]) OVER (ORDER BY [tbl_tg_machinestate_ndx]), @ReportTimeEnd) NextStateTime
      FROM [db_SPE_Carrig].[dbo].[tbl_TG_MachineState]
      WHERE [t_stamp] BETWEEN @ReportTimeStart AND @ReportTimeEnd
      ) SQ
  )

  --This final query returns an aggregated list of total time spent in each state over the report duration
  SELECT
    [Machine_State],

    CASE WHEN SUM(TimeInState) < 1 THEN 1 ELSE SUM(TimeInState) END AS [CumulativeTimeSec],

    CumulativeTime = CONVERT(varchar(10), DATEADD(SECOND, SUM(TimeInState), 0), 108)

  FROM
    StateTimes
  GROUP BY
    [Machine_State]
  ORDER BY
    [Machine_State]

WITH 块中的内部查询生成如下所示的数据：

_ndx            Machine_State   t_stamp                 NextStateTime
10211           170             2019-05-10 07:03:46.883 2019-05-10 07:03:48.087
10212           110             2019-05-10 07:03:48.087 2019-05-10 07:04:02.123
10213           120             2019-05-10 07:04:02.123 2019-05-10 07:04:07.333

整个 WITH 块产生一个像这样的表：

_ndx    Machine_State   TimeInState
10211   170             2
10212   110             14
10213   120             5
10214   130             20

每个不同状态的每个实例都有计算的时间差。

最后一步是聚合该表并总结每个状态的所有实例，如下所示：

Machine_State   CumulativeTimeSec   CumulativeTime  Description
100             6728                01:52:08        Safeties Down
110             464                 00:07:44        Initialising
120             93                  00:01:33        Ready to Start
130             14986               04:09:46        Running
140             1405                00:23:25        Dwell
150             161                 00:02:41        Stopped
170             33597               09:19:57        Faulted

问题是，我没有状态 160 的实例：好的，因为实际上没有，但这会导致报告出现问题。

在这种情况下，我要做的就是为 state=160 的 1 秒创建一个标称值。

我认为 CASE 语句可以实现这一点，但是 CASE 语句当然没有 state=160 的实例来捕获......

标签： sqlsql-servertsql

CASE WHEN SUM(TimeInState) < 1 THEN 1 ELSE SUM(TimeInState) END AS [CumulativeTimeSec] --Comma missing CumulativeTime = CONVERT(varchar(10), DATEADD(SECOND, SUM(TimeInState), 0), 108), -- extra Comma

sql - 在值为零的情况下返回标称值时出错

问题描述

解决方案

推荐阅读