Remisol / Beckman SQL 参考

SmartQC 处理样本信息 · 操作 Remisol 数据表

删除虚拟项目（最初 SQL）

用于删除虚拟项目；CHICODE='DXI1'、chiType=1。

Delete From beckman.ANALYSE
where TUBID in (select Tubid from beckman.Programmation where sampleid=@sampleid)
  and CHIID in (select CHIID from beckman.CHIMIE where chiType=1 and CHICODE='DXI1')

更新后删除虚拟项目 SQL

为解决 Remisol 项目名称与 SmartQC 虚拟项目名称不一致，改用 CHIMANA 与 CHIANACODE ='AU1'。

Delete From beckman.ANALYSE
where TUBID in (select Tubid from beckman.Programmation where sampleid=@sampleid)
  and CHIID in (SELECT CHIID FROM beckman.CHIMANA where CHIANACODE ='AU1')

删除编程消息

Delete From [beckman].[TestProg]
WHERE ProgID in (
  SELECT ProgID FROM [remisol].[beckman].[Programmation] where sampleid = @sampleid
)

更新分析状态

Update [beckman].[ANALYSE] set ACHARGER=@status
WHERE TUBID in (
  SELECT tubid FROM [remisol].[beckman].[Programmation] where sampleid = @sampleid
)
AND CHIID IN (
  SELECT CHIID FROM beckman.CHIMANA
  where CHIANACODE in ('A95','U045','U207','U125')
)

查询示例（样本号 Q303）

SELECT * FROM beckman.ANALYSE
WHERE TUBID IN (
  SELECT TUBID FROM [remisol].[beckman].[Programmation] where sampleid = 'Q303'
)
AND CHIID IN (
  SELECT CHIID FROM beckman.CHIMANA
  where CHIANACODE in ('A95','U045','U207','U125')
)

获取 CHIID

以下为应用层拼接 hosname 的示例写法，上线建议改为参数化查询。

select
  c.CHIID as ChiId,
  c.CHILIBELLE as TestName,
  c.CHICODE as TestCode,
  a.CHIANACODE as LisCode
from beckman.CHIMIE c,
     beckman.CHIMANA a,
     beckman.HOST h
where c.CHIID = a.CHIID
  and a.ANAID = (2000 + h.HOSID)
  and c.chiType = 0
  and h.HOSNAME = '" + hosname + "'

Cennexues · 质控数据样本历史及轨道信息查询

SELECT * from dbo.LbSample where SampleID like 'Q%' or SampleID like 'M%'

SELECT * FROM dbo.SampleHistory where SampleID like 'Q%' or SampleID like 'M%'

SELECT * FROM dbo.SampleHistoryEvent
where SampleNo in (
  SELECT SampleNo FROM dbo.SampleHistory
  where SampleID like 'Q%' or SampleID like 'M%'
)
order by dbo.SampleHistoryEvent.ProcessDate asc

SELECT * FROM dbo.SampleHistoryEventDetails
where HistoryEventID in (
  SELECT HistoryEventID FROM dbo.SampleHistoryEvent
  where SampleNo in (
    SELECT SampleNo FROM dbo.SampleHistory
    where SampleID like 'Q%' or SampleID like 'M%'
  )
)

SELECT * from dbo.SampleHistoryEventResource
where SHEventResourceID in (
  SELECT SHEventResourceID FROM dbo.SampleHistoryEventDetails
  where HistoryEventID in (
    SELECT HistoryEventID FROM dbo.SampleHistoryEvent
    where SampleNo in (
      SELECT SampleNo FROM dbo.SampleHistory
      where SampleID like 'Q%' or SampleID like 'M%'
    )
  )
)

处理质控数据 CNX 患者姓名不匹配

根据 SampleID 从告警中定位「已有样本不一致」类信息，并删除 LbSample 对应数据（仅 Q 开头样本）。

先查询待处理 SampleID

SELECT SampleID FROM Alert
WHERE Description LIKE N'%已有样本不一致%'
  AND AlertDate >= CAST(GETDATE() AS DATE)
  AND AlertDate < DATEADD(DAY, 1, CAST(GETDATE() AS DATE))
  AND SampleID LIKE 'Q%'

删除 LbSample

DELETE FROM LbSample
WHERE SampleID IN (
  SELECT SampleID FROM Alert
  WHERE Description LIKE N'%已有样本不一致%'
    AND AlertDate >= CAST(GETDATE() AS DATE)
    AND AlertDate < DATEADD(DAY, 1, CAST(GETDATE() AS DATE))
    AND SampleID LIKE 'Q%'
)