text: DROP TABLE IF EXISTS #ENCOUNTERS_VITAL_JOIN;
  
  SELECT e.ENC_ID
  	,e.PERSON_ID
  	,e.ENCTYPE
  	,height.measure_date
  	,ht
  	,wt
  INTO #ENCOUNTERS_VITAL_JOIN
  FROM @SCHEMA.ENCOUNTERS e
  JOIN (
  	SELECT e.ENC_ID
  		,e.PERSON_ID
  		,e.ENCTYPE
  		,v.measure_date
  		,ht
  	FROM @SCHEMA.ENCOUNTERS e
  	JOIN @SCHEMA.VITAL_SIGNS v ON e.ENC_ID = v.ENC_ID
  	WHERE e.ENCTYPE = 'AV'
  		AND ht IS NOT NULL
  		AND v.measure_date BETWEEN '1/1/2017'
  			AND '12/31/2017'
  	) height ON height.ENC_ID = e.ENC_ID
  	AND height.PERSON_ID = e.PERSON_ID
  JOIN (
  	SELECT e.ENC_ID
  		,e.PERSON_ID
  		,e.ENCTYPE
  		,v.measure_date
  		,wt
  	FROM @SCHEMA.ENCOUNTERS e
  	JOIN @SCHEMA.VITAL_SIGNS v ON e.ENC_ID = v.ENC_ID
  	WHERE e.ENCTYPE = 'AV'
  		AND wt IS NOT NULL
  		AND v.measure_date BETWEEN '1/1/2017'
  			AND '12/31/2017'
  	) weight ON weight.ENC_ID = height.ENC_ID
  	AND weight.PERSON_ID = height.PERSON_ID
  WHERE e.ENCTYPE = 'AV'
  	AND ht IS NOT NULL
  	AND wt IS NOT NULL;