• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 294
  • Last Modified:

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

0
HiranB
Asked:
HiranB
1 Solution
 
dqmqCommented:
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))


0
 
HiranBAuthor Commented:
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?
0
 
GhunaimaCommented:
Try this

 sql1.sql
0
 
Alpesh PatelAssistant ConsultantCommented:
Its look good.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now