May 30, 2017 at 2:57 pm
Hi all,
I have a table called Tbl_Authorized01, and I'm trying to find cases where a patient was readmitted within 30 days from the initial discharge date.
I would like to tag the event of a readmission for each authcaseid with either a Yes or No flag indicator.
I'm assuming a self-join, or CTE routine would be the best approach?
The data:
| authcaseid | PatientID | admissiondate | dischargedate |
| 410000 | 123456 | 4/28/2014 | 5/1/2014 |
| 100000 | 123456 | 10/22/2014 | 10/31/2014 |
| 816433 | 123456 | 8/15/2016 | 8/25/2016 |
| 0812E8 | 123456 | 8/26/2016 | 9/19/2016 |
| 09165E | 123456 | 9/19/2016 | 9/26/2016 |
| 101699 | 123456 | 9/26/2016 | 10/17/2016 |
| 7815C0 | 891011 | 8/21/2015 | 8/27/2015 |
| 091532 | 891011 | 9/8/2015 | 9/29/2015 |
| 1015E7 | 891011 | 10/28/2015 | 11/5/2015 |
| 041646 | 891011 | 4/16/2016 | 4/22/2016 |
| 516138 | 891011 | 5/18/2016 | 5/24/2016 |
| 1016F5 | 891011 | 9/30/2016 | 10/7/2016 |
| 1016D6 | 891011 | 10/19/2016 | 10/23/2016 |
May 30, 2017 at 4:49 pm
I don't see any code you developed to link any re-admissions to the original case nrs, but I'll get you started on determining the days between admission and discharge dates.
select
authcaseid,
PatientID,
datediff(day,dischargedate, admissiondate) Days,
CASE WHEN datediff(day, dischargedate, admissiondate) > 30
then 'More than 30 Days' else 'Less than 30 Days' end as CustomFlagMessage
from yourtablename
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
May 30, 2017 at 5:45 pm
Without any DDL for the table and data, I made this:
IF OBJECT_ID('dbo.Admissions', 'u') IS NOT NULL DROP TABLE dbo.Admissions;
CREATE TABLE dbo.Admissions (
AuthCaseID Varchar(6),
PatientID Integer,
Admission Date,
Discharge Date);
INSERT INTO dbo.Admissions(AuthCaseID, PatientID, Admission, Discharge)
VALUES('410000', 123456, '4/28/2014', '5/1/2014'),
('100000', 123456, '10/22/2014', '10/31/2014'),
('816433', 123456, '8/15/2016', '8/25/2016'),
('0812E8', 123456, '8/26/2016', '9/19/2016'),
('09165E', 123456, '9/19/2016', '9/26/2016'),
('101699', 123456, '9/26/2016', '10/17/2016'),
('7815C0', 891011, '8/21/2015', '8/27/2015'),
('091532', 891011, '9/8/2015', '9/29/2015'),
('1015E7', 891011, '10/28/2015', '11/5/2015'),
('041646', 891011, '4/16/2016', '4/22/2016'),
('516138', 891011, '5/18/2016', '5/24/2016'),
('1016F5', 891011, '9/30/2016', '10/7/2016'),
('1016D6', 891011, '10/19/2016', '10/23/2016');
From this table, this is how I read what you're looking for.
WITH cteRows AS (
SELECT AuthCaseID, PatientID, Admission, Discharge, RN = ROW_NUMBER() OVER(PARTITION BY PatientID ORDER BY PatientID, Admission)
FROM dbo.Admissions
),
cteDiffs AS (
SELECT R1.RN, r1.AuthCaseID, NextAuthCaseID = r2.AuthCaseID,
r1.PatientID, r1.Admission, r1.Discharge, NextAdmission = r2.Admission,
Days = DATEDIFF(day, r1.Discharge, r2.Admission)
FROM cteRows r1
LEFT OUTER JOIN cteRows r2 ON r1.RN + 1 = r2.RN
WHERE r1.PatientID = r2.PatientID
)
SELECT PatientID, RN, AuthCaseID, NextAuthCaseID, Admission, Discharge, NextAdmission, Days,
Within30Days = CASE WHEN Days < 30 THEN 'Yes' ELSE 'No' END
FROM cteDiffs
ORDER BY PatientID, RN;
If this isn't what you're looking for, please post the output you expect to see from your data. The link Joe posted or the link in my signature shows the type of information to post in your question.
Hope this helps.
May 30, 2017 at 9:27 pm
Guys,
Sorry for the half ass post, I was kind in a rush and I was trying to formulate a code, but my code or approach was not making sense, so I decided just to post my question to get a conversation started and pick it back up once home.
Below is my starting point:
Create Table #Tbl_Authorized01 (
authcaseid Varchar(6),
PatientID Integer,
admissiondate Date,
Dischargedate Date);
insert into #Tbl_Authorized01 values ('410000', '123456', '4/28/2014', '5/1/2014');
insert into #Tbl_Authorized01 values ('100000', '123456', '10/22/2014', '10/31/2014');
insert into #Tbl_Authorized01 values ('816433', '123456', '8/15/2016', '8/25/2016');
insert into #Tbl_Authorized01 values ('0812E8', '123456', '8/26/2016', '9/19/2016');
insert into #Tbl_Authorized01 values ('09165E', '123456', '9/19/2016', '9/26/2016');
insert into #Tbl_Authorized01 values ('101699', '123456', '9/26/2016', '10/17/2016');
insert into #Tbl_Authorized01 values ('7815C0', '891011', '8/21/2015', '8/27/2015');
insert into #Tbl_Authorized01 values ('091532', '891011', '9/8/2015', '9/29/2015');
insert into #Tbl_Authorized01 values ('1015E7', '891011', '10/28/2015', '11/5/2015');
insert into #Tbl_Authorized01 values ('041646', '891011', '4/16/2016', '4/22/2016');
insert into #Tbl_Authorized01 values ('516138', '891011', '5/18/2016', '5/24/2016');
insert into #Tbl_Authorized01 values ('1016F5', '891011', '9/30/2016', '10/7/2016');
insert into #Tbl_Authorized01 values ('1016D6', '891011', '10/19/2016', '10/23/2016')
Select t1.authcaseid, t1.Dischargedate, Min(t2.admissiondate) AS MinOfadmitDate, Min(Datediff(day, t2.admissiondate, t1.Dischargedate)) as Span FROM # Tbl_Authorized01 t1 INNER JOIN # Tbl_Authorized01 t2 ON t1.authcaseid = t2.authcaseid WHERE (((t2.admissiondate) > t1.Dischargedate) )GROUP BY t1.authcaseid, t1.Dischargedate HAVING Min(Datediff(day, t2.admissiondate, t1.Dischargedate) <= 30))
From my brief look, it seems like the examples you guys posted thus far is a better direction than my starting point.
May 31, 2017 at 9:56 am
I'd agree with Ed's solution, the only difference is depending on how many rows you need to process for this, it may make more sense to do the ROW_NUMBER() OVER evaluation just once in a temp table, doing it in the CTE will cause it to sort twice when the self join happens.
May 31, 2017 at 10:41 am
Ed Wagner, your solution was very helpful. It provided me the insight that I was looking for, which is for the last discharge date for the said patient id, give me the next authcaseid for the same patient id that had another admission within 30 days. Thanks.
Thank you to the other members that contributed their input as well.
May 31, 2017 at 11:27 am
Briceston - Wednesday, May 31, 2017 10:41 AMEd Wagner, your solution was very helpful. It provided me the insight that I was looking for, which is for the last discharge date for the said patient id, give me the next authcaseid for the same patient id that had another admission within 30 days. Thanks.Thank you to the other members that contributed their input as well.
Glad to hear it. Thanks for the feedback.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply