SQL Server 2008:使用多个dts范围构建一组日期

Question

我正在尝试为一个医学数据库建立一个查询，该数据库统计从一类药物(下面在FAST_MEDS CTE中列出的药物)中至少服用了一种药物并患有以下任一种疾病的患者的数量: 1)肌病的诊断( FAST_DX CTE中的诊断列表) 2) CPK值大于1000 ( FAST_LABS CTE中的实验室值)，并且此诊断或实验室发生在患者服用他汀类药物后。

我在下面包含的查询是在假设患者一旦服用他汀类药物，他们就会永远服用他汀类药物的情况下完成的。第一个CTE收集服用他汀类药物的患者的ids，以及他们的第一个诊断日期，第二个是诊断的患者，第三个是实验室价值高的患者。在此之后，我计算那些符合上述标准的。

我想要做的是放弃这样的假设，即患者一旦服用他汀类药物，他们就会终生服用。表edw_dm.patient_medications有一个名为start_dts和end_dts的列。此表中的每个处方都有一行，其中start_dts和end_dts表示处方的开始日期和结束日期。End_dts可能为空，我将假定患者当前正在使用此药物(它可能是丢失的记录，但我对此无能为力)。如果患者服用两种不同的他汀类药物，开始日期和结束日期可能会重叠，同一患者可能有多个相同药物的记录，如一个记录显示3-11-2000至4-5-2003，另一个记录显示为5-6-2007至7-8-2009。

我想使用这两列来构建一个查询，其中我只计算在已经服用他汀类药物的一段时间内，或在停止服用他汀类药物后的前n个月(例如3个月)内进行实验室检查或诊断的患者。我真的不确定如何重写第一个CTE来获得这些信息，以及在CTE构建之后如何进行比较。我知道这是一个模糊的问题，但我真的被难住了。有什么想法吗？

一如既往地，提前感谢您。

下面是当前的查询：

    WITH FAST_MEDS AS
    (
    select distinct
     statins.mrd_pt_id, min(year(statins.order_dts)) as statin_yr
     from
      edw_dm.patient_medications as statins
      inner join mrd.medications as mrd
        on statins.mrd_med_id = mrd.mrd_med_id
        WHERE mrd.generic_nm in (
           'Lovastatin (9664708500)',
           'lovastatin-niacin',
           'Lovastatin/Niacin',
           'Lovastatin',
           'Simvastatin (9678583966)',
           'ezetimibe-simvastatin',
           'niacin-simvastatin',
           'ezetimibe/Simvastatin',
           'Niacin/Simvastatin',
           'Simvastatin',
           'Aspirin Buffered-Pravastatin',
           'aspirin-pravastatin',
           'Aspirin/Pravastatin',
           'Pravastatin',
           'amlodipine-atorvastatin',
           'Amlodipine/atorvastatin',
           'atorvastatin',
           'fluvastatin',
           'rosuvastatin'
            )
        and YEAR(statins.order_dts) IS NOT NULL
        and statins.mrd_pt_id IS NOT NULL
     group by statins.mrd_pt_id
    )

    select *
    into #meds
    from FAST_MEDS
    ;

    --return patients who had a diagnosis in the list and the year that
    --diagnosis was given
    with
    FAST_DX AS
    (
     SELECT pd.mrd_pt_id, YEAR(pd.init_noted_dts) as init_yr
      FROM edw_dm.patient_diagnoses as pd
        inner join mrd.diagnoses as mrd
          on pd.mrd_dx_id = mrd.mrd_dx_id
          and mrd.icd9_cd in
    ('728.89','729.1','710.4','728.3','729.0','728.81','781.0','791.3')
    )
    select *
    into #dx
    from FAST_DX;

    --return patients who had a high cpk value along with the year the cpk
    --value was taken
    with
    FAST_LABS AS
    (
     SELECT
      pl.mrd_pt_id, YEAR(pl.order_dts) as lab_yr
     FROM
      edw_dm.patient_labs as pl
      inner join mrd.labs as mrd
        on pl.mrd_lab_id = mrd.mrd_lab_id
        and mrd.lab_nm = 'CK (CPK)'
     WHERE
       pl.lab_val between 1000 AND 999998
    )
    select *
    into #labs
    from FAST_LABS;

    -- count the number of patients who had a lab value or a medication
    -- value taken sometime AFTER their initial statin diagnosis
    select
     count(distinct p.mrd_pt_id) as ct
    from
     mrd.patient_demographics as p
     join #meds as m
      on p.mrd_pt_id = m.mrd_pt_id
     AND 
     (
       EXISTS (
             SELECT 'A' FROM #labs l WHERE p.mrd_pt_id = l.mrd_pt_id
             and l.lab_yr >= m.statin_yr
       ) 
       OR
       EXISTS(
           SELECT 'A' FROM #dx d WHERE p.mrd_pt_id = d.mrd_pt_id
           AND d.init_yr >= m.statin_yr
       )
     )

Answer 1

您可能不需要将所有CTE定义的查询都选择到临时表中。

我认为您想要的查询具有以下形式：

WITH FAST_MEDS(PatientID, StartDate, EndDate) AS
(
    --your query for patients on statins, projecting the patient ID and the start/end date for the medication
),
FAST_DX(PatientID, Date) AS
(
    --your query for patients with certain diagnosis, projecting the patient ID and the date
),
FAST_LABS(PatientID, Date) AS
(
    --your query for patients with certain labs, projecting the patient ID and the date
)
SELECT PatientID
FROM FAST_MEDS
WHERE PatientID IN (SELECT PatientID FROM FAST_DX WHERE Date BETWEEN StartDate AND EndDate OR EndDate IS NULL AND StartDate < Date)
  OR  PatientID IN (SELECT PatientID FROM FAST_LABS WHERE Date BETWEEN StartDate AND EndDate OR EndDate IS NULL AND StartDate < Date)