SQL Server 2008: Count Number of Keys In Multiple Date Ranges Across Multiple Tables

Question

Thanks to the people who answered my question this morning:

http://stackoverflow.com/questions/2087664/sql-server-2008-count-number-of-keys-in-multiple-date-ranges

This is an extension of it and I'm not clear how to generalize the previous solutions to this case.

The database I'm working with has medications, lab values, and diagnoses for a set of patients in several tables. Similar to the above question:

For years x = 1996 to 2008:

I want to count the number of patients that had a specific medication prescribed in year x, a specific lab value taken in year x, (this last one is a little different!) and a specific diagnosis first given in 1996 or later, which has not yet been resolved in year x.

EDIT: It turns out that pt_id is not a primary key in any of the tables - from what I've been reading SELECT COUNT DISTINCT(pm.pt_id) (because of DISTINCT being slow) but I would be fine with a solution that used distinct if it worked.

Here is the query for year 2008 alone, but again I want a query that will count the values for each year from 1996 to 2008.

SELECT COUNT (pm.pt_id)
FROM dm.medications pm
 /*patient was prescribed statins together with fibrates this year*/
WHERE pm.pt_id IN 
(
  SELECT statins.pt_id
  FROM dm.patient_medications statins 
  INNER JOIN dm.patient_medications other_meds
  ON statins.pt_id = other_meds.pt_id
  WHERE Year(other_meds.order_dts) = 2008
  AND Year(statins.order_dts) = 2008
  AND statins.generic_nm in ('Atorvastatin','Cerivastatin')
  AND other_meds.generic_nm in ('Clofibrate','Fenofibrate','Gemfibrozil')
)
/* patient had a diagnosis code in the list first diagnosed in 1996 or later and not yet resolved in this year */
WHERE pm.pt_id in
(
  SELECT pd.pt_id, 
  FROM dm.diagnoses pd
  WHERE pd.icd9_cd IN('728.89','729.1','710.4','728.3','729.0','728.81','781.0','791.3')
  AND Year(pd.init_noted_dts) >= 1996
  AND pd.rslvd_dts IS NOT NULL
  AND Year(pd.rslvd_dts) >= 2008
)
/* patient had a lab value above 1000 this year */ 
AND pm.pt_id IN
(
  SELECT pl.pt_id
  FROM dm.labs pl
  WHERE pl.lab_val > 1000
  AND pl.lab_val IS NOT NULL
  AND pl.lab_val < 999999
  AND pl.lab_nm = 'CK (CPK)'
  AND Year(pm.order_dts) = 2008
)
/* we have demographic information about this patient */
AND pm.pt_id IN
(
  SELECT p.pt_id
  FROM mrd.demographics p  
)
/* this is a real person */
AND pm.pt_id IS NOT NULL

Answer 1

Try this:

SELECT COUNT(*), t1.year
FROM dm.medications pm
 /*patient was prescribed statins together with fibrates this year*/
inner join (
    SELECT statins.pt_id, Year(other_meds.order_dts) as Year
    FROM dm.patient_medications statins 
    INNER JOIN dm.patient_medications other_meds ON statins.pt_id = other_meds.pt_id
        AND Year(other_meds.order_dts) = Year(statins.order_dts)
    WHERE statins.generic_nm in ('Atorvastatin','Cerivastatin')
        AND other_meds.generic_nm in ('Clofibrate','Fenofibrate','Gemfibrozil')
) t1 on pm.pt_id = t1.pt_id
/* patient had a diagnosis code in the list first diagnosed in 1996 or later and not yet resolved in this year */
left outer join 
(
    SELECT pd.pt_id, Year(pd.rslvd_dts) as Year
    FROM dm.diagnoses pd
    WHERE pd.icd9_cd IN ('728.89','729.1','710.4','728.3','729.0','728.81','781.0','791.3')
        AND Year(pd.init_noted_dts) >= 1996
) t2 on pm.pt_id = t2.pt_id and t1.Year = t2.Year 
/* patient had a lab value above 1000 this year */ 
inner join (
SELECT pl.pt_id, Year(pm.order_dts) as Year
    FROM dm.labs pl
    WHERE pl.lab_val > 1000
        AND pl.lab_val < 999999
        AND pl.lab_nm = 'CK (CPK)'
) t3 on pm.pt_id = t3.pt_id and t2.Year = t3.Year
/* we have demographic information about this patient */
inner join (
    SELECT p.pt_id
    FROM mrd.demographics p    
) t4 on pm.pt_id = t4.pt_id and t3.Year = t4.Year
/* this is a real person */
where pm.pt_id IS NOT NULL
    and t1.year between 1996 and 2008
    and t2.Year is null
group by t1.year