Retrieve Column from nested query

Transact-SQL
1

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

2

Perhaps something like the query below. Note, I collapsed all the nested selects into a pivot. Can't test it with your data of course, but it should work and is shorter (easier to read/maintain). We could probably do something similar with the bunch of case statements for the locations.

To get your new column, you need to join on the table that holds it. I added that too.

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
	-- add columns from pivot in outer apply, below
	"APH_Enum"
	,"Headache_F!Main_Complaint"
	,"Fever_FU!Main_Complaint"
	,"Nausea_FU!Main_Complaint"
	,"Vomiting_FU!Main_Complaint"
	,"Flu_Like_Symptoms!Main_Complaint"
	,"Assessment_Text!Admitting_Attending"
	,"Assessment_Text!Admitting_Resident"
	---
	,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
	-- add desired new column
	,t_PrevSession.DischargeTime
---
FROM dbo.BLSession_Extended AS s
LEFT 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 JOIN dbo.MO_DeliveryTypePatient_table AS MDT
	ON s.sessionID = MDT.SessionID
-- outer apply to get the valuetext based on object name
OUTER APPLY (
	SELECT "APH_Enum"
		,"Headache_F!Main_Complaint"
		,"Fever_FU!Main_Complaint"
		,"Nausea_FU!Main_Complaint"
		,"Vomiting_FU!Main_Complaint"
		,"Flu_Like_Symptoms!Main_Complaint"
		,"Assessment_Text!Admitting_Attending"
		,"Assessment_Text!Admitting_Resident"
	FROM BVFindings f
	pivot(max(ValueText) FOR objectname IN (
				"APH_Enum"
				,"Headache_F!Main_Complaint"
				,"Fever_FU!Main_Complaint"
				,"Nausea_FU!Main_Complaint"
				,"Vomiting_FU!Main_Complaint"
				,"Flu_Like_Symptoms!Main_Complaint"
				,"Assessment_Text!Admitting_Attending"
				,"Assessment_Text!Admitting_Resident"
				)) p
	WHERE f.sessionid = s.SessionID
	) vals
--  left join to expose the new column
LEFT JOIN MO_Times t_PrevSession
	ON s_PrevSession.sessionID = t_PrevSession.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
3

eg. for case expressions:

OUTER APPLY
(

select [1] AS LOCATION1
     , [2] AS LOCATION2
     , [3] AS LOCATION3
     , [4] AS LOCATION4
     , [5] AS LOCATION5
     , [6] AS LOCATION6
     , [7] AS LOCATION7
     , [8] AS LOCATION8
     , [9] AS LOCATION9
     , [10]AS LOCATION10
from 
( 
    select l.location_num, l.locationname 
    from dbo.BLPatient_Location pl
    join dbo.BLLocation l
      ON pl.location_num = l.location_num
) s
pivot 
(
    max(locationmame) for locationnum in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])
) p
where s.sessionID = pl.sessionID
) locs