March 7, 2011 at 8:00 am
Hi
In the Data Warehouse we have recently installed i am trying to reproduce reports produced using SQL that were previously done manuallty in excel.
(See Code Below)
from the following table i need someway of identifying the following.
Only patients that have a discharge and a follwing admission on the same day.
This can be ID'd by the patient_no and subsiquent adm_no. eg: 45678 - 1, 45678 - 2 and 45678 - 3 Also the previous discharge date for the previous admission must match the new admission date.
So from the following table i would expect to see results for patients 45678 (admisison 2 & 3) and 54321 (admission 6)
i have many similar queries like this to set up so any help would greatly appreciated
CODE BELOW............
drop table PS_TestForOnline
CREATE TABLE PS_TestForOnline
(
patient_no varchar (10),
adm_no VARCHAR(5),
admission_date date ,
discharge_date date,
);
INSERT INTO PS_TestForOnline
VALUES('12345','1','01/02/2011','08/02/2011' );
INSERT INTO PS_TestForOnline
VALUES('45678','1','04/02/2011','04/02/2011' );
INSERT INTO PS_TestForOnline
VALUES('45678','2','04/02/2011','04/02/2011' );
INSERT INTO PS_TestForOnline
VALUES('45678','3','04/02/2011','06/02/2011' );
INSERT INTO PS_TestForOnline
VALUES('54321','5','10/02/2011','10/02/2011' );
INSERT INTO PS_TestForOnline
VALUES('54321','6','10/02/2011','11/02/2011' );
INSERT INTO PS_TestForOnline
VALUES('12345','2','03/02/2011','05/02/2011' );
INSERT INTO PS_TestForOnline
VALUES('12345','3','07/02/2011','10/02/2011' );
select * from PS_TestForOnline
March 7, 2011 at 8:10 am
select * from PS_TestForOnline
WHERE admission_date = discharge_date
March 7, 2011 at 8:21 am
Thanks for your reply,
this only gives me the the adm_no's where admission_date and discharge date are the same.
For Example: i need to look at previous admissions for each patient and compare discharge date from their previous stay in hospital with their admission date from subsiquent admission.
Patient 54321 has 2 admissions. admission 5 was discharged on the same day he was admitted for admission 6. 2011-10-02.
Thanks
March 7, 2011 at 8:47 am
In your example, your timedate stamp is consistent. I'm guessing that this is not going to be the case with your live data. Is the issue then just needing to match dates between the 2 columns or am I missing something? If it's just a matter of matching the dates, then though crude - this might not be a bad way to go...
select cast(convert(varchar, admission_date, 101) as smalldatetime) AS admission_date, cast(convert(varchar, discharge_date, 101) as smalldatetime) AS discharge_date
from PS_TestForOnline
WHERE admission_date = discharge_date
March 7, 2011 at 9:04 am
Hi
I think we are talking cross purposes here and its probably down to my explaination skills (or lack of)
This is a request for a hospital and i have to identify patients that were discharged and then came back on the same day.
Each Patient has a ID number (patient_no) and a number to show their admission. These start at 1 and continue untill you RIP.
If we look at patient 45678 in my table, they were unfortunatly admitted 3 times on the same day "2011-04-02" and in their last admission (3) they stayed in hosp for 4 days.
I need to select each of their admissions where their discharge date from one admission (in this case adm_no 1 and 2) = the admission date of the next admission (in this case adm_no 3 and 4).
sorry if i have not made sense.
thanks
March 7, 2011 at 9:12 am
OK - so it's not matching admission and discharge dates, but matching admission dates (where the discharge date happened, and then another admission happened on the same date).
My bad - let me rearrange the code.
March 7, 2011 at 9:41 am
OK - give this a try, and let me know if it gets you closer to your desired result (I have it on my end, and it looks pretty sound). What it will do is get all the dates that are a match, and guarantee that your admissions records go all the way back to the very first one issued for a given patient.
SELECT patient_no, adm_no, cast(convert(VARCHAR(18), admission_date, 101) AS SMALLDATETIME) AS admission_date, cast(convert(VARCHAR(18), discharge_date, 101) AS SMALLDATETIME) AS discharge_date
FROM PS_TestForOnline
WHERE discharge_date = admission_date
AND adm_no > 0
ORDER BY patient_no
Let me know if this gets you your desired result.
March 7, 2011 at 10:20 am
i've had a look on line and from what i can gather this is going to have to be a self join as one row is looking at another and returning values from that row.
you select highlights the problem rows but i need to see the info from the previous admission.
thanks
March 7, 2011 at 10:26 am
That's probably not a bad way to go (may even produce a better execution plan).
Just FYI - I was able to get the result you were looking for, and I cleaned it up a bit more by doing just a straight CONVERT without the CAST.
SELECT patient_no, adm_no, convert(NVARCHAR(18), admission_date, 101) AS admission_date, convert(NVARCHAR(18), discharge_date, 101) AS discharge_date
FROM PS_TestForOnline
WHERE discharge_date = admission_date
AND adm_no > 0
ORDER BY patient_no
March 7, 2011 at 11:05 am
Rich, I'm pretty sure you'll definately need to self-join this for the final requirements.
You're looking for something similar to the following. Note, I'm not sure if you want to see the multiple entries for patient 45678 like the following will display below:
select
PrevVisit.patient_no,
PrevVisit.adm_no AS AdminNo1,
NextVisit.adm_no AS AdmitNo2,
PrevVisit.Discharge_date AS DateOfRevisit
FROM
#PS_TestForOnline AS PrevVisit
JOIN
#PS_TestForOnline AS NextVisit
ONPrevVisit.Patient_no = NextVisit.Patient_no
AND PrevVisit.Discharge_date = NextVisit.Admission_Date
WHERE
PrevVisit.adm_no <> NextVisit.adm_no
If you're not, modify the join condition so it looks like this (note the additional item in the ON clause):
select
PrevVisit.patient_no,
PrevVisit.adm_no AS AdminNo1,
NextVisit.adm_no AS AdmitNo2,
PrevVisit.Discharge_date AS DateOfRevisit
FROM
#PS_TestForOnline AS PrevVisit
JOIN
#PS_TestForOnline AS NextVisit
ONPrevVisit.Patient_no = NextVisit.Patient_no
AND PrevVisit.Discharge_date = NextVisit.Admission_Date
AND PrevVisit.Adm_no = NextVisit.Adm_no - 1
WHERE
PrevVisit.adm_no <> NextVisit.adm_no
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 7, 2011 at 11:12 am
Ah - I see. He's also needing the same date for another admission, even though the discharge is then a different date, but still part of that group of admissions/discharges (this is where I wasn't fully grasping his business rule). I was only getting the matched dates.
My bad, and my apologies p.stevens.
Thank you Craig!
:w00t:
March 10, 2011 at 1:54 pm
CHEERS CRAIG
THAT WORKED A TREAT. THANKS A LOT
🙂
March 10, 2011 at 2:13 pm
p.stevens76 (3/10/2011)
CHEERS CRAIGTHAT WORKED A TREAT. THANKS A LOT
🙂
My pleasure, glad it worked... and thanks for the feedback. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply