Getting numerator with subqueries

Analytics
Fan

Getting numerator with subqueries

I am trying to get my numerator with the following query:

		SELECT
--			Region,
(
(
        ( SELECT
			MAX(TimeToFibrinolysis)
		  FROM 
		( SELECT 
			TOP 50 PERCENT TimeToFibrinolysis
			FROM TmpOP01											AS b
			WHERE TimeToFibrinolysis IS NOT NULL
			AND a.MeasureMonth =  b.MeasureMonth 
			AND a.Measureyear = b.Measureyear
        	ORDER BY TimeToFibrinolysis )				AS BottomHalf )
	+
(		SELECT
			MIN(TimeToFibrinolysis)
		FROM 
		( SELECT 
			TOP 50 PERCENT TimeToFibrinolysis
			FROM TmpOP01											AS c
			WHERE TimeToFibrinolysis IS NOT NULL
			AND a.MeasureMonth =  c.MeasureMonth 
			AND a.Measureyear = c.Measureyear
        	ORDER BY TimeToFibrinolysis DESC )			AS TopHalf )
 	)
    /2 )															AS n,  --Numerator	
 	1 AS Denominator,
 	( MeasureYear - 1900 ) * 10000 + MeasureMonth * 100 + 1 (DATE) AS MeasureDate,
	MeasureMonth,
 	MeasureYear
 	FROM TmpOP01													AS a
 	WHERE 
	MeasureDate >= '2017-06-01'	--:pStrDate	
	GROUP BY  MeasureMonth, MeasureYear	;

It doesent seenm to work in TD as it does in T-SQL.  Could someone help me with this?

 

1 REPLY
Highlighted
Junior Contributor

Re: Getting numerator with subqueries

No need for complicates Correlated Subqueries, seems like you simply want the MEDIAN(TimeToFibrinolysis).