retrieve column from nested query

Question

retrieve column from nested query

sharonmtowler

Default port

Points: 1495
More actions
August 6, 2015 at 12:50 pm

#310742

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

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

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

sharonmtowler Default port Points: 1495 More actions · Answer 2

Thank you very much. Appreciate your help. Happy Friday!!!