将具有不同时间戳的两行的数据匹配到一行(SQL Server)

Question

我正面临SQL Server查询的问题。

您可以在下面的屏幕截图中看到我的数据库中的数据示例。包含一个较大数值的属性的行实际上是读卡器读取的票据的事件。

还有一些行包含像Eticket result这样的字符串作为属性。当工单失效，系统显示工单失效原因时，会产生此事件。

​

​

我想要做的是创建一个新的列(命名为"reason")，它将包含类似于'Eticket Result‘的所有属性。问题是，所有这些包含“ETICKET结果”的属性都必须与正确的票号相匹配。原因属性和无效票证的时间戳之间的时间差不能超过500毫秒。

为了让它更容易理解，我在下面给你另一个我想要做的截图。

​

​

这有可能做到吗？我已经尝试了几个小时，并创建了不能生成正确结果的脚本。如果它对你有帮助，我给你下面的查询，我已经做了，但没有显示正确的数据。

DECLARE @alarm_table TABLE (
 /*1*/server_timestamp     DATETIME ,
 /*2*/museum                 VARCHAR(255),
 /*3*/turnstile          VARCHAR(255),
 /*4*/entrance            VARCHAR(255),
 /*5*/cardnumber          VARCHAR(255),
 /*6*/result              VARCHAR(255),
 /*7*/reason              VARCHAR(255),
 /*8*/attributes             VARCHAR(255)

);

INSERT INTO @alarm_table
SELECT
  /*1*/servertimestamp,
  /*2*/hostname,
  /*3*/substring([hostname], PatIndex('%[0-9]%', [hostname]), len([hostname])),
  /*4*/substring(attributes, 31, 39),
  /*5*/attributes,
  /*6*/'NOT OK',
  /*7*/'',
  /*8*/attributes--substring(attributes, 70, 30)
FROM
  eventlog el
where
 (el.servertimestamp  BETWEEN '8/24/2018' AND DATEADD(dd, +1, '8/27/2019'))
 and (attributes like '%ticket %' and attributes like '%eticketing%' )
 and hostname <> 'tapaeos'
order by
  el.timestamp

 UPDATE @alarm_table
  SET cardnumber = substring(attributes, 31, 39)


  UPDATE @alarm_table
  SET result =  case
                   when 
                        (attributes like '%ticket 8%'
                        or attributes like '%ticket 9%'
                        or attributes like '%ticket 10%'
                        or attributes like '%ticket 11%' 
                        or attributes like '%ticket 12%' 
                        or attributes like '%ticket 13%' 
                        or attributes like '%ticket 14%' 
                        or attributes like '%knossos ticket 5%'
                        or attributes like '%knossos ticket 6%'
                        or attributes like '%knossos ticket 7%'
                        or attributes like '%klitys ticket 5%'
                        or attributes like '%klitys ticket 6%'
                        or attributes like '%klitys ticket 7%'
                        or attributes like '%olympieio ticket 5%'
                        or attributes like '%olympieio ticket 6%'
                        or attributes like '%olympieio ticket 7%'
                        ) 
                    then 'NOT OK'
                   else 'OK'
              end

  UPDATE @alarm_table
    SET reason = case
                    when result = 'NOT OK' then 
                    (SELECT top 1 attributes 
                     FROM eventlog 
                     WHERE DATEDIFF(second,servertimestamp,server_timestamp)<=1)
                   else ' '
              end

  UPDATE @alarm_table
    SET museum  = case
                 when museum like '%olymp%' then 'Olympieio'
                 when museum like '%knoss%' then 'Knossos'
                 when museum like '%sslope%' then 'Klitys'
                 when museum like '%acrop%' then 'Acropolis'
                end

  select
  server_timestamp,
  museum,
  turnstile,
  cardnumber,
  result,
  reason
 -- attributes
from
  @alarm_table



  order by server_timestamp desc  

您的帮助将不胜感激，感谢您的宝贵时间。

Answer 1

试试这个：

select e1.*, e2.attributes reason
from (
    select *
    from eventlog
    where charindex('ETICKET Result', attributes) = 0
) e1 left join (
    select timestamp, attributes
    from eventlog
    where charindex('ETICKET Result', attributes) > 0
) e2 on abs(datediff(millisecond, e1.timestamp, e2.timestamp)) <= 500

在e1中，我们查询所有正确的记录(没有ETICKET Reason)，在e2中，我们选择所有不正确的记录。然后，我们以毫秒为单位连接两个结果的时间差。