August 6, 2015 at 12:50 pm
I have the following query and where I need to use the t_PrevSession.DischargeTime which is in the nested query that is bolded below. How do i bring it up to the main select statement?
how do i do that please?
SELECT
s.facilityid,
s.sessionid,
s.MRN,
s.LastName +', '+ s.FirstName as PATIENT_NAME,
isnull(dbo.EFgetFindingValue(30741,default,S.Sessionid),999) as GESTATIONAL_AGE,
DATEDIFF(D,MT.DeliveryTime,MT.DISCHARGETIME)AS LENGTH_OF_STAY,
CASE WHEN MDT.VaginalDelivery ='YES' THEN 'VAGINAL'
WHEN MDT.CesareanDelivery = 'YES' THEN 'CESAREAN'
WHEN MDT.VAGINALDELIVERY IS NULL AND MDT.CESAREANDELIVERY IS NULL THEN 'NO DELIVERY TYPE' END AS DELIVERY_TYPE,
S.OPEN_TIME,
mt.DeliveryTime,
mt.DischargeTime,
mt.chartstarttime,
(select top 1 f.ValueStr from BVFindings f
where f.sessionid = s.SessionID and f.objectname = 'APH_Enum') AS CC_Bleeding,
(select top 1 f.ValueStr from BVFindings f
where f.sessionid = s.SessionID and f.objectname = 'Headache_F!Main_Complaint') AS CC_Headache,
(select top 1 f.ValueStr from BVFindings f
where f.sessionid = s.SessionID and f.objectname = 'Fever_FU!Main_Complaint') AS CC_Fever,
(select top 1 f.ValueStr from BVFindings f
where f.sessionid = s.SessionID and f.objectname = 'Nausea_FU!Main_Complaint') AS CC_Nausea,
(select top 1 f.ValueStr from BVFindings f
where f.sessionid = s.SessionID and f.objectname = 'Vomiting_FU!Main_Complaint') AS CC_Vomiting,
(select top 1 f.ValueStr from BVFindings f
where f.sessionid = s.SessionID and f.objectname = 'Flu_Like_Symptoms!Main_Complaint') AS CC_Flu_Like_Symptoms,
(select top 1 f.ValueText from BVFindings f
where f.sessionid = s.SessionID and f.objectname = 'Assessment_Text!Admitting_Attending') AS Assessment_Attending_Assessment,
(select top 1 f.ValueText from BVFindings f
where f.sessionid = s.SessionID and f.objectname = 'Assessment_Text!Admitting_Resident') AS Assessment_Resident_Assessment,
pl.locationid,
l.location_name,
pl.enter_time,
pl.exit_time,
CASE WHEN pl.locationid =1 THEN l.location_name END AS LOCATION1,
CASE WHEN pl.locationid =2 THEN l.location_name END AS LOCATION2,
CASE WHEN pl.locationid =3 THEN l.location_name END AS LOCATION3,
CASE WHEN pl.locationid =4 THEN l.location_name END AS LOCATION4,
CASE WHEN pl.locationid =5 THEN l.location_name END AS LOCATION5,
CASE WHEN pl.locationid =6 THEN l.location_name END AS LOCATION6,
CASE WHEN pl.locationid =7 THEN l.location_name END AS LOCATION7,
CASE WHEN pl.locationid =8 THEN l.location_name END AS LOCATION8,
CASE WHEN pl.locationid =9 THEN l.location_name END AS LOCATION9,
CASE WHEN pl.locationid =10 THEN l.location_name END AS LOCATION10
FROM
dbo.BLSession_Extended AS s
LEFT OUTER JOIN
dbo.MO_Times_table AS mt ON s.sessionID = mt.SessionID
INNER JOIN
dbo.BLPatient_Location pl ON s.sessionID = pl.sessionID
INNER JOIN
dbo.BLLocation l ON pl.location_num = l.location_num
LEFT OUTER JOIN
dbo.MO_DeliveryTypePatient_table AS MDT ON s.sessionID = MDT.SessionID
WHERE
S.FacilityID = @FACILITYID
AND s.Open_Time BETWEEN @StartTimeOut and @EndTimeOut
AND s.MRN in
(SELECT s_PrevSession.MRN
FROM
BLSession_Extended s_PrevSession
INNER JOIN MO_Times t_PrevSession ON s_PrevSession.sessionID = t_PrevSession.SessionID
WHERE
s_PrevSession.SessionID <> s.SessionID
AND t_PrevSession.DeliveryTime IS NOT NULL
AND s.Open_Time between t_PrevSession.DischargeTime and DateAdd(day,@PREV_DISCHARGE,t_PrevSession.DischargeTime))
order by s.mrn
August 6, 2015 at 1:21 pm
This is untested, but it might work. What you asked is included as a CROSS APPLY. I also changed a part of your query to prevent multiple reads to BVFindings.
WITH cteFindings AS(
SELECT f.sessionid,
MAX( CASE WHEN f.objectname = 'APH_Enum' THEN f.ValueStr END) AS CC_Bleeding,
MAX( CASE WHEN f.objectname = 'Headache_F!Main_Complaint' THEN f.ValueStr END) AS CC_Headache,
MAX( CASE WHEN f.objectname = 'Fever_FU!Main_Complaint' THEN f.ValueStr END) AS CC_Fever,
MAX( CASE WHEN f.objectname = 'Nausea_FU!Main_Complaint' THEN f.ValueStr END) AS CC_Nausea,
MAX( CASE WHEN f.objectname = 'Vomiting_FU!Main_Complaint' THEN f.ValueStr END) AS CC_Vomiting,
MAX( CASE WHEN f.objectname = 'Flu_Like_Symptoms!Main_Complaint' THEN f.ValueStr END) AS CC_Flu_Like_Symptoms,
MAX( CASE WHEN f.objectname = 'Assessment_Text!Admitting_Attending' THEN f.ValueStr END) AS Assessment_Attending_Assessment,
MAX( CASE WHEN f.objectname = 'Assessment_Text!Admitting_Resident' THEN f.ValueStr END) AS Assessment_Resident_Assessment
FROM BVFindings f
GROUP BY f.sessionid
)
SELECT
s.facilityid,
s.sessionid,
s.MRN,
s.LastName +', '+ s.FirstName as PATIENT_NAME,
isnull(dbo.EFgetFindingValue(30741,default,S.Sessionid),999) as GESTATIONAL_AGE,
DATEDIFF(D,MT.DeliveryTime,MT.DISCHARGETIME)AS LENGTH_OF_STAY,
CASE WHEN MDT.VaginalDelivery ='YES' THEN 'VAGINAL'
WHEN MDT.CesareanDelivery = 'YES' THEN 'CESAREAN'
WHEN MDT.VAGINALDELIVERY IS NULL AND MDT.CESAREANDELIVERY IS NULL THEN 'NO DELIVERY TYPE' END AS DELIVERY_TYPE,
S.OPEN_TIME,
mt.DeliveryTime,
mt.DischargeTime,
mt.chartstarttime,
f.CC_Bleeding,
f.CC_Headache,
f.CC_Fever,
f.CC_Nausea,
f.CC_Vomiting,
f.CC_Flu_Like_Symptoms,
f.Assessment_Attending_Assessment,
f.Assessment_Resident_Assessment,
pl.locationid,
l.location_name,
pl.enter_time,
pl.exit_time,
CASE WHEN pl.locationid =1 THEN l.location_name END AS LOCATION1,
CASE WHEN pl.locationid =2 THEN l.location_name END AS LOCATION2,
CASE WHEN pl.locationid =3 THEN l.location_name END AS LOCATION3,
CASE WHEN pl.locationid =4 THEN l.location_name END AS LOCATION4,
CASE WHEN pl.locationid =5 THEN l.location_name END AS LOCATION5,
CASE WHEN pl.locationid =6 THEN l.location_name END AS LOCATION6,
CASE WHEN pl.locationid =7 THEN l.location_name END AS LOCATION7,
CASE WHEN pl.locationid =8 THEN l.location_name END AS LOCATION8,
CASE WHEN pl.locationid =9 THEN l.location_name END AS LOCATION9,
CASE WHEN pl.locationid =10 THEN l.location_name END AS LOCATION10
FROM
dbo.BLSession_Extended AS s
LEFT OUTER JOIN dbo.MO_Times_table AS mt ON s.sessionID = mt.SessionID
INNER JOIN dbo.BLPatient_Location pl ON s.sessionID = pl.sessionID
INNER JOIN dbo.BLLocation l ON pl.location_num = l.location_num
LEFT OUTER JOIN dbo.MO_DeliveryTypePatient_table AS MDT ON s.sessionID = MDT.SessionID
LEFT OUTER JOIN cteFindings f ON s.sessionID = f.SessionID
CROSS APPLY (
SELECT t_PrevSession.DischargeTime --Should it be MAX or MIN?
FROM BLSession_Extended s_PrevSession
INNER JOIN MO_Times t_PrevSession ON s_PrevSession.sessionID = t_PrevSession.SessionID
WHERE s_PrevSession.SessionID <> s.SessionID
AND s_PrevSession.MRN = s.MRN
AND t_PrevSession.DeliveryTime IS NOT NULL
AND s.Open_Time between t_PrevSession.DischargeTime and DateAdd(day,@PREV_DISCHARGE,t_PrevSession.DischargeTime)
) cteS
WHERE S.FacilityID = @FACILITYID
AND s.Open_Time BETWEEN @StartTimeOut and @EndTimeOut
order by s.mrn
August 7, 2015 at 6:30 am
Thank you very much. Appreciate your help. Happy Friday!!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply