July 31, 2015 at 11:30 am
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')
July 31, 2015 at 12:12 pm
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
July 31, 2015 at 12:19 pm
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