asked on

Creating a list of values from a TSQL query

I have created a series of unions to create a list of values for 'Admission Tasks Outstanding'. I wondered if there is a more efficient way to achieve the same outcome?

SELECT     
dbc.encounterId
, dbc.BedNumber
, CASE WHEN BedNumber BETWEEN 1 AND 9 THEN 'POD1' 
WHEN BedNumber BETWEEN 10 AND 18 THEN 'POD2' 
WHEN BedNumber BETWEEN 19 AND 27 THEN 'POD3' 
WHEN BedNumber BETWEEN 27 AND 36 THEN 'POD4' END AS PODS
, dbc.ICUAdmDateTime
,CASE WHEN (dbc.encounterId NOT IN
	(SELECT encounterId FROM dbo.PtDemographic WITH (NOLOCK) WHERE (interventionId IN
			(SELECT interventionId 
				FROM dbo.D_Intervention WITH (NOLOCK)
				WHERE(shortLabel = ('Height (cm)') AND conceptcode = '50373000'))))) THEN 'Height' END AS 'Admission Tasks Outstanding'
FROM         
dbo.V_DailyBedCensus AS dbc 
INNER JOIN dbo.PtCensus AS c ON dbc.encounterId = c.encounterId
WHERE     
(c.inTime IS NOT NULL) AND 
(dbc.encounterId NOT IN (SELECT Encounterid FROM PMIMaster.dbo.RBWH_Excluded_Encounters))
UNION ALL
SELECT     
dbc.encounterId
, dbc.BedNumber
, CASE WHEN BedNumber BETWEEN 1 AND 9 THEN 'POD1' 
WHEN BedNumber BETWEEN 10 AND 18 THEN 'POD2' 
WHEN BedNumber BETWEEN 19 AND 27 THEN 'POD3' 
WHEN BedNumber BETWEEN 27 AND 36 THEN 'POD4' END AS PODS
, dbc.ICUAdmDateTime
,CASE WHEN (dbc.encounterId NOT IN
(SELECT encounterId FROM dbo.PtDemographic WITH (NOLOCK) WHERE (interventionId IN
			(SELECT interventionId
				FROM dbo.D_Intervention WITH (NOLOCK)
				WHERE(shortLabel = ('Weight (Kg) (Admission)') AND conceptcode = '27113001'))))) THEN 'Weight' END  AS 'Admission Tasks Outstanding'
FROM         
dbo.V_DailyBedCensus AS dbc 
INNER JOIN dbo.PtCensus AS c ON dbc.encounterId = c.encounterId
WHERE     
(c.inTime IS NOT NULL) AND 
(dbc.encounterId NOT IN (SELECT Encounterid FROM PMIMaster.dbo.RBWH_Excluded_Encounters))

UNION ALL
SELECT     
dbc.encounterId
, dbc.BedNumber
, CASE WHEN BedNumber BETWEEN 1 AND 9 THEN 'POD1' 
WHEN BedNumber BETWEEN 10 AND 18 THEN 'POD2' 
WHEN BedNumber BETWEEN 19 AND 27 THEN 'POD3' 
WHEN BedNumber BETWEEN 27 AND 36 THEN 'POD4' END AS PODS
, dbc.ICUAdmDateTime
, CASE WHEN (dbc.encounterId NOT IN
	(SELECT encounterId FROM dbo.PtIntervention WITH (NOLOCK) WHERE(interventionId IN
		(SELECT interventionId FROM dbo.D_Intervention WITH (NOLOCK)
		WHERE (shortLabel = ('Property List Completed') AND conceptcode = '370855008'))))) THEN 'PropertyList' END  AS 'Admission Tasks Outstanding'
FROM         
dbo.V_DailyBedCensus AS dbc 
INNER JOIN dbo.PtCensus AS c ON dbc.encounterId = c.encounterId
WHERE     
(c.inTime IS NOT NULL) AND 
(dbc.encounterId NOT IN (SELECT Encounterid FROM PMIMaster.dbo.RBWH_Excluded_Encounters))
ORDER BY DBC.BEDNUMBER

Open in new window

dqmq

This should help:

SELECT
dbc.encounterId
, dbc.BedNumber
, CASE WHEN BedNumber BETWEEN 1 AND 9 THEN 'POD1'
WHEN BedNumber BETWEEN 10 AND 18 THEN 'POD2'
WHEN BedNumber BETWEEN 19 AND 27 THEN 'POD3'
WHEN BedNumber BETWEEN 27 AND 36 THEN 'POD4' END AS PODS
, dbc.ICUAdmDateTime
, CASE WHEN (dbc.encounterId NOT IN
(SELECT encounterId FROM dbo.PtIntervention WITH (NOLOCK) WHERE(interventionId IN
(SELECT interventionId FROM dbo.D_Intervention WITH (NOLOCK)
WHERE (shortLabel = ('Property List Completed')
AND conceptcode in ('370855008','27113001',50373000'))))))
THEN 'PropertyList' END AS 'Admission Tasks Outstanding'
FROM
dbo.V_DailyBedCensus AS dbc
INNER JOIN dbo.PtCensus AS c ON dbc.encounterId = c.encounterId
WHERE
(c.inTime IS NOT NULL) AND
(dbc.encounterId NOT IN (SELECT Encounterid FROM PMIMaster.dbo.RBWH_Excluded_Encounters))

HiranB

ASKER

Hi, Height and Weight (the first two select statements) come from ptdemographic and not ptintervention. How do I create the list when I am looking at multiple tables?

ASKER CERTIFIED SOLUTION

G Trurab Khan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Alpesh Patel

Its look good.