Can function take subquery as a parameter?

I created a function for user to calculate median, one parameter for the function @filter is actually used to filter the result so the median is calculated on those records which meets that condition. So the function is used this way:

select fixedto_date,cbsa_name, dbo.fn_getMedian(fixedto_date,cbsa_name, 'single family') as median_price
from dbo.Realtor_Best_FirstofMonth_Listings
where cbsa = 19740
and description like '%single family%'
group by fixedto_date,cbsa_name
order by fixedto_date asc

----------function definition----------------
create FUNCTION [dbo].[fn_getMedian](@fixedto_date datetime, @cbsa_name varchar(150), @filter varchar(150))
RETURNS money
AS
BEGIN
    DECLARE @ret money
    SELECT TOP 50 PERCENT @ret= Price FROM Realtor_Best_FirstofMonth_Listings
    WHERE [description] like '%'+@filter+'%'
   and  fixedto_date = @fixedto_date and cbsa_name = @cbsa_name ORDER BY Price ASC
   
    RETURN @ret
END


but now user wants to have the flexibility of the function to operate on the table with any type of filter, like:
select fixedto_date,cbsa_name, dbo.fn_getMedian_filter(fixedto_date,cbsa_name, 'single family') as median_price
from dbo.Realtor_Best_FirstofMonth_Listings
where cbsa = 19740
and (description like '%single family%' or description like '%condo%')
group by fixedto_date,cbsa_name
order by fixedto_date asc

or the filter can be anything like: description like '%single family%' or description like '%condo%' or description like '%multi family%', etc. i find this not an easy 1, 2, 3 thing if not impossible.

I thought about using a subquery and take the subquery as a parameter, so you would use it like this:

select fixedto_date,cbsa_name,count(price) as inventory,avg(price) as avg_price, dbo.fn_getMedian(fixedto_date,cbsa_name,filtered_query) as median_price
from
(select * from
from dbo.Realtor_Best_FirstofMonth_Listings
where cbsa = 19740
and description like '%single family%' ) as filtered_query
group by fixedto_date,cbsa_name
order by fixedto_date asc

and use dynamic sql in the function to take the subquery as the table, but I doubt that would work that way.

Does anybody have better solution? Thanks in advance!Start Free Trial