每位患者首次发生事件

Question

附加的代码应该返回第一个ORDER_PROC。每位患者ORDER_INST。在某些情况下，我得到了多条记录。关于更好方法的任何建议？谢谢史蒂夫

SELECT DISTINCT  
  ORDER_PROC.PAT_ENC_CSN_ID as ordercsn, Min(ORDER_PROC.ORDER_INST) as 
  CodeStatus_Datetime, CLARITY_SER.PROV_NAME as CodeStatus_OrderProvider
FROM   
  ORDER_PROC  with(nolock) , ORDER_METRICS with(nolock) , CLARITY_SER 
  with(nolock) 
WHERE
  ORDER_PROC.ORDER_PROC_ID = ORDER_METRICS.ORDER_ID AND
  ORDER_METRICS.ORDERING_PROV_ID = CLARITY_SER.PROV_ID AND
  --ORDER_PROC.REASON_FOR_CANC_C IS  NULL AND
  (ORDER_PROC.PROC_CODE = 'COD1' OR 
  ORDER_PROC.PROC_CODE = 'COD2' OR 
  ORDER_PROC.PROC_CODE = 'COD3' 
  )
GROUP by 
ORDER_PROC.PAT_ENC_CSN_ID, ORDER_PROC.ORDER_INST,CLARITY_SER.PROV_NAME

Answer 1

使用 ROW_NUMBER() 为每个患者创建分区

SELECT *
FROM (
    SELECT OP.PAT_ENC_CSN_ID as ordercsn, 
           OP.ORDER_INST,
           CodeStatus_Datetime, 
           CS.PROV_NAME as CodeStatus_OrderProvider,
           ROW_NUMBER() OVER (PARTITION BY OP.PAT_ENC_CSN_ID
                               ORDER BY OP.ORDER_INST) as rn
    FROM  ORDER_PROC  OP
    JOIN  ORDER_METRICS OM
      OP.ORDER_PROC_ID = OM.ORDER_ID 
    JOIN CLARITY_SER  CS
      OM.ORDERING_PROV_ID = CS.PROV_ID 
    WHERE
      OP.PROC_CODE IN ('COD1','COD2','COD3')
   ) as T
 WHERE rn = 1