April 24, 2014 at 9:32 am
Hello,
I've read many posts about hospital readmissions, but my question is a bit different, so I thought to post.
Fields in pt_table:
encounter_id --(unique per visit)
medical_record_num
admit_date
discharge_date
{many other fields}
Deliverable:
I'd like to scan the pt_table or join it to itself in order to create another table of all index_admissions which are all admissions into the hospital with a subsequent readmission or readmissions. A readmission for this task will be defined as any inpatient return to the hospital within 30 days after the Index admission's discharge_date and all readmissions are counted separately.
The other table will be the subsequent 30_day_readmissions table.
So, the index_admission table can have one patient(row) which can link to multiple rows in the 30_day_readmissions table if the patient was readmitted multiple times within 30 days after the Index admission. Patients can also be readmitted back to the hospital on the same day of discharge or readmitted twice on another following the discharge day. All rows in the 30_day_readmission table should have a linking row in the index_admission table.
Any help is greatly appreciated!
April 24, 2014 at 9:43 am
buckeye_sql3000 (4/24/2014)
Hello,I've read many posts about hospital readmissions, but my question is a bit different, so I thought to post.
Fields in pt_table:
encounter_id --(unique per visit)
medical_record_num
admit_date
discharge_date
{many other fields}
Deliverable:
I'd like to scan the pt_table or join it to itself in order to create another table of all index_admissions which are all admissions into the hospital with a subsequent readmission or readmissions. A readmission for this task will be defined as any inpatient return to the hospital within 30 days after the Index admission's discharge_date and all readmissions are counted separately.
The other table will be the subsequent 30_day_readmissions table.
So, the index_admission table can have one patient(row) which can link to multiple rows in the 30_day_readmissions table if the patient was readmitted multiple times within 30 days after the Index admission. Patients can also be readmitted back to the hospital on the same day of discharge or readmitted twice on another following the discharge day. All rows in the 30_day_readmission table should have a linking row in the index_admission table.
Any help is greatly appreciated!
Why do you want a separate table for this? This is a perfect example of where a view fits the bill perfectly.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 24, 2014 at 11:38 am
I have Access and Oracle users that require a table build.
Thanks for any assistance.
April 24, 2014 at 12:09 pm
buckeye_sql3000 (4/24/2014)
I have Access and Oracle users that require a table build.Thanks for any assistance.
Access and Oracle can both select from a view just as easily as a table.
Can you provide some ddl, sample data and desired output? Take a look at the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 24, 2014 at 1:18 pm
pt_table
encounter_id medical_record_num admit_date discharge_date {.....}
10001 7890123 01/05/2013 01/10/2013
10002 8901234 05/20/2013 05/23/2013
10003 8901234 06/20/2013 06/21/2013
10004 7890123 02/20/2013 02/23/2013
10005 7890123 02/25/2013 02/27/2013
10006 5678901 12/24/2013 12/24/2013
10007 5678901 12/24/2013 01/02/2014
10008 4567890 05/20/2013 06/01/2013
10009 4567890 07/02/2013 07/02/2013
10010 7890123 02/09/2013 02/18/2013
Desired output:
index_table
encounter_id medical_record_num Index_admit_date Index_discharge_date {.....}
10001 7890123 01/05/2013 01/10/2013
10002 8901234 05/20/2013 05/23/2013
10004 7890123 02/20/2013 02/23/2013
10006 5678901 12/24/2013 12/24/2013
10008 4567890 05/20/2013 06/01/2013
10009 4567890 07/02/2013 07/02/2013
readmission_table
encounter_id medical_record_num Rdm_admit_date Rdm_discharge_date {.....}
10003 8901234 06/20/2013 06/21/2013
10005 7890123 02/25/2013 02/27/2013
10007 5678901 12/24/2013 01/02/2014
10010 7890123 02/09/2013 03/15/2013
DDL:
encounter_id: key
medical_record_num: unique to patient
Index visit is any admission without an admission 30 days prior.
A readmission is a return to the hospital within 30 days and can be multiple based on Index.
All patient visits are either an Index admission or a readmission.
I'm attempting to build OLAP cubes with these tables too, so I'm not sure how to do that with views, yet.
Thanks.
April 24, 2014 at 1:27 pm
I am guessing you didn't look at the article I suggested. The idea is to make the ddl and sample data consumable. That means we don't have to create tables and generate insert statements. We spend our time working on your issue instead of setting it up.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 24, 2014 at 1:59 pm
CREATE TABLE Pt_table
(encounter_id varchar(255),medical_record_num varchar(255),admit_date datetime,discharge_date datetime);
INSERT INTO Pt_table (encounter_id,medical_record_num,admit_date,discharge_date)
VALUES ('10001','7890123','01/05/2013','01/10/2013'
'10002','8901234','05/20/2013','05/23/2013'
'10003','8901234','06/20/2013','06/21/2013'
'10004','7890123','02/20/2013','02/23/2013'
'10005','7890123','02/25/2013','02/27/2013'
'10006','5678901','12/24/2013','12/24/2013'
'10007','5678901','12/24/2013','01/02/2014'
'10008','4567890','05/20/2013','06/01/2013'
'10009','4567890','07/02/2013','07/02/2013'
'10010','7890123','02/09/2013','02/18/2013');
Thanks
April 24, 2014 at 2:05 pm
buckeye_sql3000 (4/24/2014)
CREATE TABLE Pt_table(encounter_id varchar(255),medical_record_num varchar(255),admit_date datetime,discharge_date datetime);
INSERT INTO Pt_table (encounter_id,medical_record_num,admit_date,discharge_date)
VALUES ('10001','7890123','01/05/2013','01/10/2013'
'10002','8901234','05/20/2013','05/23/2013'
'10003','8901234','06/20/2013','06/21/2013'
'10004','7890123','02/20/2013','02/23/2013'
'10005','7890123','02/25/2013','02/27/2013'
'10006','5678901','12/24/2013','12/24/2013'
'10007','5678901','12/24/2013','01/02/2014'
'10008','4567890','05/20/2013','06/01/2013'
'10009','4567890','07/02/2013','07/02/2013'
'10010','7890123','02/09/2013','02/18/2013');
Thanks
Did you actually test that???
Here is some inserts that will actually work...
CREATE TABLE Pt_table
(encounter_id varchar(255),medical_record_num varchar(255),admit_date datetime,discharge_date datetime);
INSERT INTO Pt_table (encounter_id,medical_record_num,admit_date,discharge_date)
select '10001','7890123','01/05/2013','01/10/2013' union all
select '10002','8901234','05/20/2013','05/23/2013' union all
select '10003','8901234','06/20/2013','06/21/2013' union all
select '10004','7890123','02/20/2013','02/23/2013' union all
select '10005','7890123','02/25/2013','02/27/2013' union all
select '10006','5678901','12/24/2013','12/24/2013' union all
select '10007','5678901','12/24/2013','01/02/2014' union all
select '10008','4567890','05/20/2013','06/01/2013' union all
select '10009','4567890','07/02/2013','07/02/2013' union all
select '10010','7890123','02/09/2013','02/18/2013'
select * from Pt_table
Now that I have something to work with let me see what I can do.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 25, 2014 at 10:16 am
Sorry for the errant code. Thanks again for your assistance.
April 25, 2014 at 1:57 pm
i just built this same report for my job...small world, what with the new rules for not getting paid if a re-admit occurs within 30 days.
without the sample data Sean cleaned up, i would not have tried this at all.
help us help you! next time, post syntactically correct sample data, and you'll get tested working answers a lot faster.
CREATE TABLE Pt_table
(encounter_id varchar(255),medical_record_num varchar(255),admit_date datetime,discharge_date datetime);
INSERT INTO Pt_table (encounter_id,medical_record_num,admit_date,discharge_date)
select '10001','7890123','01/05/2013','01/10/2013' union all
select '10002','8901234','05/20/2013','05/23/2013' union all
select '10003','8901234','06/20/2013','06/21/2013' union all
select '10004','7890123','02/20/2013','02/23/2013' union all
select '10005','7890123','02/25/2013','02/27/2013' union all
select '10006','5678901','12/24/2013','12/24/2013' union all
select '10007','5678901','12/24/2013','01/02/2014' union all
select '10008','4567890','05/20/2013','06/01/2013' union all
select '10009','4567890','07/02/2013','07/02/2013' union all
select '10010','7890123','02/09/2013','02/18/2013'
;With OrganizedData AS
(
select ROW_NUMBER() OVER (PARTITION BY medical_record_num ORDER BY medical_record_num,admit_date) AS RW, * from Pt_table
)
SELECT
Datediff(dd, T1.admit_date, T2.admit_date) AS DaysDifference,
T1.*,
T2.*
FROM OrganizedData T1
INNER JOIN OrganizedData T2
ON T1.medical_record_num = T2.medical_record_num
AND T1.RW < T2.RW
WHERE Datediff(dd, T1.admit_date, T2.admit_date) < 30
ORDER BY T1.medical_record_num,
T1.admit_date
Lowell
May 20, 2014 at 4:58 pm
Fantastic solution Lowell, I reckon you meant
WHERE Datediff(dd, T1.discharge_date, T2.admit_date) < 30
?
----------------------------------------------------
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply