Find records comparing two lists

  • Below is the code for two data sets and I can't seem to get my head around the issue. I need to find the number of 'ER' visits and 'IN' visits, separately, in dbo.VisitData for the 'Active' patients in dbo.PatientStatus. So, consider patient 69. He is Active on 5/5/2014 but becomes Inactive on 9/15/2014. I only want to count the number of visits ER or IN that are between those dates. In addition if patient 69 becomes active again after 9/15/2014, I need to capture that data as well. Patients can change there status multiple times.

    create table dbo.PatientStatus

    as

    (

    patient_id varchar(10),

    status_type varchar(10),

    status_date datetime

    )

    insert into TEST(patient_id, status_type, status_date values( '69','Active','2014-05-05')

    insert into TEST(patient_id, status_type, status_date values( '72','Active','2014-05-05')

    insert into TEST(patient_id, status_type, status_date values( '76','Active','2014-06-19')

    insert into TEST(patient_id, status_type, status_date values( '78','Active','2014-06-19')

    insert into TEST(patient_id, status_type, status_date values( '36','Inactive','2014-04-22')

    insert into TEST(patient_id, status_type, status_date values( '23','Inactive','2014-04-10')

    insert into TEST(patient_id, status_type, status_date values( '29','Inactive','2014-06-04')

    insert into TEST(patient_id, status_type, status_date values( '29','Active','2014-08-07')

    insert into TEST(patient_id, status_type, status_date values( '109','Active','2014-09-12')

    insert into TEST(patient_id, status_type, status_date values( '110','Active','2014-09-12')

    insert into TEST(patient_id, status_type, status_date values( '101','Active','2014-09-12')

    insert into TEST(patient_id, status_type, status_date values( '100','Active','2014-09-16')

    insert into TEST(patient_id, status_type, status_date values( '69','Inactive','2014-09-15')

    insert into TEST(patient_id, status_type, status_date values( '102','Active','2014-09-18')

    insert into TEST(patient_id, status_type, status_date values( '109','Inactive','2014-09-30')

    insert into TEST(patient_id, status_type, status_date values( '111','Active','2014-10-02')

    insert into TEST(patient_id, status_type, status_date values( '112','Active','2014-10-14')

    insert into TEST(patient_id, status_type, status_date values( '29','Inactive','2014-10-15')

    insert into TEST(patient_id, status_type, status_date values( '113','Active','2014-10-17')

    insert into TEST(patient_id, status_type, status_date values( '114','Active','2014-10-17')

    insert into TEST(patient_id, status_type, status_date values( '116','Active','2014-11-05')

    insert into TEST(patient_id, status_type, status_date values( '64','Inactive','2014-09-04')

    insert into TEST(patient_id, status_type, status_date values( '117','Active','2014-11-19')

    insert into TEST(patient_id, status_type, status_date values( '117','Inactive','2014-11-20')

    insert into TEST(patient_id, status_type, status_date values( '118','Active','2014-12-11')

    insert into TEST(patient_id, status_type, status_date values( '119','Active','2014-12-15')

    insert into TEST(patient_id, status_type, status_date values( '27','Inactive','2014-12-02')

    insert into TEST(patient_id, status_type, status_date values( '101','Inactive','2014-12-02')

    insert into TEST(patient_id, status_type, status_date values( '66','Inactive','2014-12-03')

    insert into TEST(patient_id, status_type, status_date values( '17','Inactive','2014-12-02')

    insert into TEST(patient_id, status_type, status_date values( '76','Inactive','2014-12-03')

    insert into TEST(patient_id, status_type, status_date values( '120','Active','2014-12-30')

    insert into TEST(patient_id, status_type, status_date values( '32','Inactive','2015-01-03')

    insert into TEST(patient_id, status_type, status_date values( '123','Active','2015-01-12')

    insert into TEST(patient_id, status_type, status_date values( '120','Inactive','2015-01-10')

    insert into TEST(patient_id, status_type, status_date values( '124','Active','2015-01-15')

    insert into TEST(patient_id, status_type, status_date values( '22','Inactive','2015-01-06')

    insert into TEST(patient_id, status_type, status_date values( '24','Inactive','2015-02-03')

    insert into TEST(patient_id, status_type, status_date values( '123','Inactive','2015-02-08')

    insert into TEST(patient_id, status_type, status_date values( '125','Active','2015-02-25')

    insert into TEST(patient_id, status_type, status_date values( '21','Inactive','2015-02-25')

    insert into TEST(patient_id, status_type, status_date values( '126','Active','2015-03-06')

    insert into TEST(patient_id, status_type, status_date values( '127','Active','2015-03-06')

    insert into TEST(patient_id, status_type, status_date values( '22','Active','2015-03-09')

    insert into TEST(patient_id, status_type, status_date values( '19','Inactive','2015-03-01')

    insert into TEST(patient_id, status_type, status_date values( '112','Inactive','2015-02-11')

    insert into TEST(patient_id, status_type, status_date values( '128','Active','2015-03-19')

    insert into TEST(patient_id, status_type, status_date values( '129','Active','2015-03-26')

    insert into TEST(patient_id, status_type, status_date values( '129','Inactive','2015-03-27')

    insert into TEST(patient_id, status_type, status_date values( '130','Active','2015-03-30')

    insert into TEST(patient_id, status_type, status_date values( '133','Active','2015-04-30')

    insert into TEST(patient_id, status_type, status_date values( '24','Active','2014-12-23')

    insert into TEST(patient_id, status_type, status_date values( '102','Inactive','2015-05-06')

    insert into TEST(patient_id, status_type, status_date values( '130','Inactive','2015-05-08')

    insert into TEST(patient_id, status_type, status_date values( '126','Inactive','2015-05-18')

    insert into TEST(patient_id, status_type, status_date values( '100','Inactive','2015-05-18')

    insert into TEST(patient_id, status_type, status_date values( '116','Inactive','2015-05-18')

    insert into TEST(patient_id, status_type, status_date values( '111','Inactive','2015-06-11')

    insert into TEST(patient_id, status_type, status_date values( '119','Inactive','2015-06-14')

    insert into TEST(patient_id, status_type, status_date values( '67','Inactive','2015-06-03')

    insert into TEST(patient_id, status_type, status_date values( '134','Active','2015-05-21')

    insert into TEST(patient_id, status_type, status_date values( '138','Active','2015-06-26')

    insert into TEST(patient_id, status_type, status_date values( '72','Inactive','2015-07-20')

    insert into TEST(patient_id, status_type, status_date values( '134','Inactive','2015-07-28')

    insert into TEST(patient_id, status_type, status_date values( '140','Active','2015-07-29')

    insert into TEST(patient_id, status_type, status_date values( '141','Active','2015-07-23')

    insert into TEST(patient_id, status_type, status_date values( '142','Active','2015-07-28')

    insert into TEST(patient_id, status_type, status_date values( '143','Active','2015-07-23')

    create table dbo.VisitData

    as

    (

    patient_id varchar(10),

    pt_status varchar(10),

    admit_date datetime

    )

    insert into TEST(patient_id, pt_status, admit_date values( '64','ER','2014-11-14')

    insert into TEST(patient_id, pt_status, admit_date values( '18','ER','2015-02-07')

    insert into TEST(patient_id, pt_status, admit_date values( '113','ER','2014-12-01')

    insert into TEST(patient_id, pt_status, admit_date values( '142','ER','2014-12-26')

    insert into TEST(patient_id, pt_status, admit_date values( '76','ER','2014-12-28')

    insert into TEST(patient_id, pt_status, admit_date values( '123','ER','2015-03-08')

    insert into TEST(patient_id, pt_status, admit_date values( '66','ER','2015-03-17')

    insert into TEST(patient_id, pt_status, admit_date values( '67','ER','2015-06-03')

    insert into TEST(patient_id, pt_status, admit_date values( '112','ER','2015-06-05')

    insert into TEST(patient_id, pt_status, admit_date values( '141','ER','2015-07-05')

    insert into TEST(patient_id, pt_status, admit_date values( '113','IN','2014-10-13')

    insert into TEST(patient_id, pt_status, admit_date values( '24','IN','2014-10-20')

    insert into TEST(patient_id, pt_status, admit_date values( '116','IN','2014-10-29')

    insert into TEST(patient_id, pt_status, admit_date values( '29','IN','2014-11-20')

    insert into TEST(patient_id, pt_status, admit_date values( '141','IN','2014-11-09')

    insert into TEST(patient_id, pt_status, admit_date values( '29','IN','2014-11-12')

    insert into TEST(patient_id, pt_status, admit_date values( '117','IN','2014-11-30')

    insert into TEST(patient_id, pt_status, admit_date values( '123','IN','2014-12-04')

    insert into TEST(patient_id, pt_status, admit_date values( '120','IN','2014-12-07')

    insert into TEST(patient_id, pt_status, admit_date values( '22','IN','2014-12-15')

    insert into TEST(patient_id, pt_status, admit_date values( '124','IN','2014-12-19')

    insert into TEST(patient_id, pt_status, admit_date values( '21','IN','2014-12-24')

    insert into TEST(patient_id, pt_status, admit_date values( '120','IN','2014-12-20')

    insert into TEST(patient_id, pt_status, admit_date values( '29','IN','2015-01-10')

    insert into TEST(patient_id, pt_status, admit_date values( '67','IN','2015-01-13')

    insert into TEST(patient_id, pt_status, admit_date values( '76','IN','2015-02-03')

    insert into TEST(patient_id, pt_status, admit_date values( '24','IN','2015-02-03')

    insert into TEST(patient_id, pt_status, admit_date values( '123','IN','2015-02-08')

    insert into TEST(patient_id, pt_status, admit_date values( '118','IN','2015-02-13')

    insert into TEST(patient_id, pt_status, admit_date values( '21','IN','2015-02-25')

    insert into TEST(patient_id, pt_status, admit_date values( '19','IN','2015-03-13')

    insert into TEST(patient_id, pt_status, admit_date values( '23','IN','2015-04-09')

    insert into TEST(patient_id, pt_status, admit_date values( '134','IN','2015-04-29')

    insert into TEST(patient_id, pt_status, admit_date values( '120','IN','2015-05-09')

    insert into TEST(patient_id, pt_status, admit_date values( '78','IN','2015-05-18')

    insert into TEST(patient_id, pt_status, admit_date values( '120','IN','2015-04-05')

    insert into TEST(patient_id, pt_status, admit_date values( '78','IN','2015-05-26')

    insert into TEST(patient_id, pt_status, admit_date values( '67','IN','2015-05-27')

    insert into TEST(patient_id, pt_status, admit_date values( '21','IN','2015-05-31')

    insert into TEST(patient_id, pt_status, admit_date values( '133','IN','2015-04-26')

    insert into TEST(patient_id, pt_status, admit_date values( '109','IN','2015-06-11')

    insert into TEST(patient_id, pt_status, admit_date values( '21','IN','2015-06-15')

    insert into TEST(patient_id, pt_status, admit_date values( '19','IN','2015-05-23')

    insert into TEST(patient_id, pt_status, admit_date values( '141','IN','2015-07-08')

    insert into TEST(patient_id, pt_status, admit_date values( '21','IN','2015-06-12')

    insert into TEST(patient_id, pt_status, admit_date values( '21','IN','2015-06-25')

    insert into TEST(patient_id, pt_status, admit_date values( '140','IN','2015-07-19')

    insert into TEST(patient_id, pt_status, admit_date values( '143','ER','2015-07-21')

  • Your sample data doesn't help much to test and validate. I had to make several corrections which mean that you didn't test your sample data.

    This might be an option, but it expects that you won't have any consecutive status (2 o more "actives" without "inactive" between them or the other way around).

    After getting the periods, the second cross tab is easier.

    create table dbo.PatientStatus

    (

    patient_id varchar(10),

    status_type varchar(10),

    status_date datetime

    )

    insert into PatientStatus(patient_id, status_type, status_date) values( '69','Active','2014-05-05')

    ,( '72','Active','2014-05-05')

    ,( '76','Active','2014-06-19')

    ,( '78','Active','2014-06-19')

    ,( '36','Inactive','2014-04-22')

    ,( '23','Inactive','2014-04-10')

    ,( '29','Inactive','2014-06-04')

    ,( '29','Active','2014-08-07')

    ,( '109','Active','2014-09-12')

    ,( '110','Active','2014-09-12')

    ,( '101','Active','2014-09-12')

    ,( '100','Active','2014-09-16')

    ,( '69','Inactive','2014-09-15')

    ,( '102','Active','2014-09-18')

    ,( '109','Inactive','2014-09-30')

    ,( '111','Active','2014-10-02')

    ,( '112','Active','2014-10-14')

    ,( '29','Inactive','2014-10-15')

    ,( '113','Active','2014-10-17')

    ,( '114','Active','2014-10-17')

    ,( '116','Active','2014-11-05')

    ,( '64','Inactive','2014-09-04')

    ,( '117','Active','2014-11-19')

    ,( '117','Inactive','2014-11-20')

    ,( '118','Active','2014-12-11')

    ,( '119','Active','2014-12-15')

    ,( '27','Inactive','2014-12-02')

    ,( '101','Inactive','2014-12-02')

    ,( '66','Inactive','2014-12-03')

    ,( '17','Inactive','2014-12-02')

    ,( '76','Inactive','2014-12-03')

    ,( '120','Active','2014-12-30')

    ,( '32','Inactive','2015-01-03')

    ,( '123','Active','2015-01-12')

    ,( '120','Inactive','2015-01-10')

    ,( '124','Active','2015-01-15')

    ,( '22','Inactive','2015-01-06')

    ,( '24','Inactive','2015-02-03')

    ,( '123','Inactive','2015-02-08')

    ,( '125','Active','2015-02-25')

    ,( '21','Inactive','2015-02-25')

    ,( '126','Active','2015-03-06')

    ,( '127','Active','2015-03-06')

    ,( '22','Active','2015-03-09')

    ,( '19','Inactive','2015-03-01')

    ,( '112','Inactive','2015-02-11')

    ,( '128','Active','2015-03-19')

    ,( '129','Active','2015-03-26')

    ,( '129','Inactive','2015-03-27')

    ,( '130','Active','2015-03-30')

    ,( '133','Active','2015-04-30')

    ,( '24','Active','2014-12-23')

    ,( '102','Inactive','2015-05-06')

    ,( '130','Inactive','2015-05-08')

    ,( '126','Inactive','2015-05-18')

    ,( '100','Inactive','2015-05-18')

    ,( '116','Inactive','2015-05-18')

    ,( '111','Inactive','2015-06-11')

    ,( '119','Inactive','2015-06-14')

    ,( '67','Inactive','2015-06-03')

    ,( '134','Active','2015-05-21')

    ,( '138','Active','2015-06-26')

    ,( '72','Inactive','2015-07-20')

    ,( '134','Inactive','2015-07-28')

    ,( '140','Active','2015-07-29')

    ,( '141','Active','2015-07-23')

    ,( '142','Active','2015-07-28')

    ,( '143','Active','2015-07-23')

    create table dbo.VisitData

    (

    patient_id varchar(10),

    pt_status varchar(10),

    admit_date datetime

    )

    insert into VisitData(patient_id, pt_status, admit_date) values( '64','ER','2014-11-14')

    ,( '18','ER','2015-02-07')

    ,( '113','ER','2014-12-01')

    ,( '142','ER','2014-12-26')

    ,( '76','ER','2014-12-28')

    ,( '123','ER','2015-03-08')

    ,( '66','ER','2015-03-17')

    ,( '67','ER','2015-06-03')

    ,( '112','ER','2015-06-05')

    ,( '141','ER','2015-07-05')

    ,( '113','IN','2014-10-13')

    ,( '24','IN','2014-10-20')

    ,( '116','IN','2014-10-29')

    ,( '29','IN','2014-11-20')

    ,( '141','IN','2014-11-09')

    ,( '29','IN','2014-11-12')

    ,( '117','IN','2014-11-30')

    ,( '123','IN','2014-12-04')

    ,( '120','IN','2014-12-07')

    ,( '22','IN','2014-12-15')

    ,( '124','IN','2014-12-19')

    ,( '21','IN','2014-12-24')

    ,( '120','IN','2014-12-20')

    ,( '29','IN','2015-01-10')

    ,( '67','IN','2015-01-13')

    ,( '76','IN','2015-02-03')

    ,( '24','IN','2015-02-03')

    ,( '123','IN','2015-02-08')

    ,( '118','IN','2015-02-13')

    ,( '21','IN','2015-02-25')

    ,( '19','IN','2015-03-13')

    ,( '23','IN','2015-04-09')

    ,( '134','IN','2015-04-29')

    ,( '120','IN','2015-05-09')

    ,( '78','IN','2015-05-18')

    ,( '120','IN','2015-04-05')

    ,( '78','IN','2015-05-26')

    ,( '67','IN','2015-05-27')

    ,( '21','IN','2015-05-31')

    ,( '133','IN','2015-04-26')

    ,( '109','IN','2015-06-11')

    ,( '21','IN','2015-06-15')

    ,( '19','IN','2015-05-23')

    ,( '141','IN','2015-07-08')

    ,( '21','IN','2015-06-12')

    ,( '21','IN','2015-06-25')

    ,( '140','IN','2015-07-19')

    ,( '143','ER','2015-07-21');

    WITH cteRows AS(

    SELECT patient_id,

    status_type,

    status_date,

    ROW_NUMBER() OVER(PARTITION BY patient_id ORDER BY status_date) AS rn

    FROM PatientStatus

    ), ctePeriods AS(

    SELECT patient_id,

    MAX( CASE WHEN status_type = 'Active' THEN status_date END) AS startdate,

    MAX( CASE WHEN status_type = 'Inactive' THEN status_date END) AS enddate

    FROM cteRows

    GROUP BY patient_id,

    CASE WHEN status_type = 'Active' AND rn % 2 = 1 THEN (rn+1) / 2

    WHEN status_type = 'Inactive' AND rn % 2 = 0 THEN (rn+1) / 2

    WHEN status_type = 'Active' AND rn % 2 = 0 THEN (rn) / 2

    WHEN status_type = 'Inactive' AND rn % 2 = 1 THEN (rn) / 2

    END

    )

    SELECT p.patient_id,

    p.startdate,

    p.enddate,

    COUNT( CASE WHEN v.pt_status = 'ER' THEN pt_status END) AS ERCount,

    COUNT( CASE WHEN v.pt_status = 'IN' THEN pt_status END) AS INCount

    FROM VisitData v

    JOIN ctePeriods p ON v.patient_id = p.patient_id

    AND v.admit_date BETWEEN p.startdate AND ISNULL( p.enddate, '99991231')

    GROUP BY p.patient_id,

    p.startdate,

    p.enddate

    ORDER BY p.patient_id,

    startdate;

    GO

    DROP TABLE PatientStatus

    DROP TABLE VisitData

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • My bad. Sorry for the inconvenience and thank you very much for your help. I'll do a better job of validating test data before I create future posts.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply