September 28, 2016 at 6:59 am
valeryk2000 (9/27/2016)
Thank you. However the task may be more complicated: in some cases there is more than 2 IDs (check 10011580, 10014918, just for example, in my table posted @ 10:05:34)
Actually, what I was looking for was a hand jammed table of what the results should be, not a verbal description. I am a visual problem solver. If I can see the beginning state and the ending state it is easier for me to realize a solution.
September 28, 2016 at 7:30 am
Sergiy - looks logically right. I'll work on it tomorrow, when I'm back in office. Not sure it's going to work in more than two events for the same patient.
Thank you
September 29, 2016 at 11:37 am
To everybody:
thanks a lot for the discussion and suggestions. So far I tested only Sergiy's codes - work perfectly well - thanks again, Sergiy! Next week I'll test other codes and will be back to you.
Val
September 29, 2016 at 1:03 pm
Testing update:
J Livingston's code runs OK - same results as in Sergiy's.
Thanks a lot!
September 29, 2016 at 1:14 pm
valeryk2000 (9/29/2016)
Testing update:J Livingston's code runs OK - same results as in Sergiy's.
Thanks a lot!
on what size table did you run this?
have you got any time/io results.....?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 29, 2016 at 3:01 pm
valeryk2000 (9/29/2016)
Testing update:J Livingston's code runs OK - same results as in Sergiy's.
Thanks a lot!
Actually, our codes return quite different results.
Probably not on the set you're looking at, but nevertheless.
Here is the script where you can see several cases when those scripts act differently:
CREATE TABLE #mytable(
ID INT NOT NULL
,ArrivalDateTime DATETIME NOT NULL
);
-- same time
INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001000,'20160820 22:25');
INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001000,'20160820 22:25');
-- exactly 72 hours between admissions
INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001072,'20160728 11:08');
INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001072,'20160731 11:08');
-- more than 1 readmission within 72 hours
INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001003,'20160729 14:53');
INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001003,'20160730 11:14');
INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001003,'20160731 16:21');
-- no < 72 hours readmission for 1st appointment, but there is one for a second one
INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001023,'20160729 14:53');
INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001023,'20160822 15:12');
INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001023,'20160824 09:15');
-- 2 pairs of readmissions - <72 hours within each pair, > 72 hours between pairs
INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001202,'20160729 14:53');
INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001202,'20160730 11:14');
INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001202,'20160819 08:21');
INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001202,'20160821 09:34');
WITH pt AS (
SELECT Id,
MAX(CASE WHEN rn = 1 THEN at END )AS at1,
MAX(CASE WHEN rn =2 THEN at END) AS at2
FROM (
SELECT Id,
dbo.#mytable.ArrivalDateTime AS at,
ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ArrivalDateTime) rn
FROM #mytable) x
group by id)
SELECT Id,
at1,
at2,
DATEDIFF(hh, at1, at2) AS hrdiff
FROM pt
WHERE(DATEDIFF(hh, at1, at2) < 72);
select A1.[ID], A1.[ArrivalDateTime], AN.ArrivalDateTime NextAdmissionDatetime
from [dbo].#mytable A1
INNER JOIN [dbo].#mytable AN ON A1.ID = AN.ID
and AN.ArrivalDateTime > A1.ArrivalDateTime
and AN.ArrivalDateTime <= DATEADD(hh, 72, A1.[ArrivalDateTime])
Order by 1,2,3
DROP TABLE #mytable
I can't tell which is right and which is wrong.
You'll have to verify the outcome of both scripts against the business rules in place.
_____________
Code for TallyGenerator
September 29, 2016 at 3:05 pm
Sergiy (9/29/2016)
valeryk2000 (9/29/2016)
Testing update:J Livingston's code runs OK - same results as in Sergiy's.
Thanks a lot!
Actually, our codes return quite different results.
Probably not on the set you're looking at, but nevertheless.
Here is the script where you can see several cases when those scripts act differently:
CREATE TABLE #mytable(ID INT NOT NULL
,ArrivalDateTime DATETIME NOT NULL
);
-- same time
INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001000,'20160820 22:25');
INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001000,'20160820 22:25');
-- exactly 72 hours between admissions
INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001072,'20160728 11:08');
INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001072,'20160731 11:08');
-- more than 1 readmission within 72 hours
INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001003,'20160729 14:53');
INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001003,'20160730 11:14');
INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001003,'20160731 16:21');
-- no < 72 hours readmission for 1st appointment, but there is one for a second one
INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001023,'20160729 14:53');
INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001023,'20160822 15:12');
INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001023,'20160824 09:15');
-- 2 pairs of readmissions - <72 hours within each pair, > 72 hours between pairs
INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001202,'20160729 14:53');
INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001202,'20160730 11:14');
INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001202,'20160819 08:21');
INSERT INTO #mytable(ID,ArrivalDateTime) VALUES (10001202,'20160821 09:34');
WITH pt AS (
SELECT Id,
MAX(CASE WHEN rn = 1 THEN at END )AS at1,
MAX(CASE WHEN rn =2 THEN at END) AS at2
FROM (
SELECT Id,
dbo.#mytable.ArrivalDateTime AS at,
ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ArrivalDateTime) rn
FROM #mytable) x
group by id)
SELECT Id,
at1,
at2,
DATEDIFF(hh, at1, at2) AS hrdiff
FROM pt
WHERE(DATEDIFF(hh, at1, at2) < 72);
select A1.[ID], A1.[ArrivalDateTime], AN.ArrivalDateTime NextAdmissionDatetime
from [dbo].#mytable A1
INNER JOIN [dbo].#mytable AN ON A1.ID = AN.ID
and AN.ArrivalDateTime > A1.ArrivalDateTime
and AN.ArrivalDateTime <= DATEADD(hh, 72, A1.[ArrivalDateTime])
Order by 1,2,3
DROP TABLE #mytable
I can't tell which is right and which is wrong.
You'll have to verify the outcome of both scripts against the business rules in place.
to confirm what I originally posted
.....so here is one method for you to try..it might not be the best...and assume that you only have two rows per ID.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 30, 2016 at 8:19 am
I run both codes on my original dataset. Sergiy's returned 130 records, J's - 79. The reason - Sergiy's counted all cases with counts 2 and more, J's - only 2.
September 30, 2016 at 8:26 am
valeryk2000 (9/30/2016)
I run both codes on my original dataset. Sergiy's returned 130 records, J's - 79. The reason - Sergiy's counted all cases with counts 2 and more, J's - only 2.
so if I understand your requirement correctly, then you must use Sergiys code....hope you have it all sorted now.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 30, 2016 at 8:35 am
If somebody's interested, I attach my test data set with results for both codes.
Sorry for Excel but it can be easily imported into SS2K8 using import wizard
September 30, 2016 at 11:59 am
To DesNorton - this code returns the original data set
Thank you
Val
October 3, 2016 at 3:13 am
valeryk2000 (9/30/2016)
If somebody's interested, I attach my test data set with results for both codes.Sorry for Excel but it can be easily imported into SS2K8 using import wizard
Thanks for the feedback.
I must say - before you start talking about the number of seconds you need to make sure the outcome is correct.
For example, my script returns an entry for ID = 10009903.
I'd say it's correct, but is it actually?
I can't tell because it's not my call.
Similar situation with ID = 10016395
My script returns 3 rows for this ID.
J's script returns only 1.
Which one is the desired outcome?
Only after the resultsets are matching the expectations we could start talking about possible performance improvements.
_____________
Code for TallyGenerator
October 3, 2016 at 8:46 am
The difference between yours and J's is that his singled out only first 2 records while yours - any number of records. The original dataset is selected on n>1 during certain time range. 10009903 has 3 records, J's code ignores it. Now, the time lag between 1st and the 2nd is 11 (?) days, so it is not included. And your code correctly picks 2nd and 3d.
In 10016395 - yours picks 2 with the same starting point. That's OK. The decision point is to check if the second admission (btw this is about emergency room) is for the same reason as the first one (which means that the pt was not treated properly during the first admission). Once we singled out all readmissions the nurse would check diagnoses and select only visits with the same diagnoses.
There are 4 admissions in 10016395 and J's code picks the first two.
Thank again
Val
October 3, 2016 at 3:52 pm
valeryk2000 (10/3/2016)
The difference between yours and J's is that his singled out only first 2 records while yours - any number of records. The original dataset is selected on n>1 during certain time range. 10009903 has 3 records, J's code ignores it. Now, the time lag between 1st and the 2nd is 11 (?) days, so it is not included. And your code correctly picks 2nd and 3d.In 10016395 - yours picks 2 with the same starting point. That's OK. The decision point is to check if the second admission (btw this is about emergency room) is for the same reason as the first one (which means that the pt was not treated properly during the first admission). Once we singled out all readmissions the nurse would check diagnoses and select only visits with the same diagnoses.
There are 4 admissions in 10016395 and J's code picks the first two.
Thank again
Val
Yes, I understand what each of our scripts does.
I don't know what they are expected to do.
How the outcome should look?
This question is not even so much for you to answer, it's for you to ask.
You must show the options to the users and ask them which one would be correct.
What is the definition of "the first admission"?
If I've been admitted to the emergency room 3 years after being admitted for the same reason - would it be treated as a "first admission"?
How long should it take for a first admission to "be forgotten" by the system?
If there are 3 or more re-admissions - is anybody interested in seeing them all?
Should readmissions of readmissions be reported?
you have a lot of questions to ask here.
_____________
Code for TallyGenerator
October 3, 2016 at 4:56 pm
We select all admissions within the defined TIME RANGE. If 'your' record is within this range (say month of August 2016) and there is another one in August 2016, and the second comes in less then 72 hrs after the first one, then we select this pair and, using ID/ArrivalDateTime, present other fields (diagnosis, hospital, attending physician, etc) to a medical professional (may be a nurse or physicians assistant) who make a decision and pick qualifying cases for their reports. 'Your' visit to the ED 3 years ago is not relevant. And your code completely satisfies these requirements.
Thanks
Hope it helps
Val
Viewing 15 posts - 16 through 30 (of 52 total)
You must be logged in to reply to this topic. Login to reply