SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select *
into a
from (--mock of table
        select 1 as val union all
        select 3 as val union all
        select 4 as val union all
        select 1 as val union all
        select 3 as val union all
        select 4 as val union all
        select 4 as val union all
        select 10 as val
    ) a
8 rows affected

select s.value as param, a.val, a.cnt
from string_split('2,9,10,4', ',') s outer apply
     (select top (1) a.val, count(*) as cnt
      from a
      group by a.val
      having a.val < s.value
      order by a.val desc
     ) a;


param
val
cnt


2
1
2

9
4
3

10
4
3

4
3
2


with av as (
      select a.val, count(*) as cnt
      from a
      group by a.val
      union all
      select s.value, null as cnt
      from string_split('2,9,10,4', ',') s
     )
select val, a_val, a_cnt
from (select av.*,
             max(case when cnt is not null then val end) over (order by val, (case when cnt is null then 1 else 2 end)) as a_val,
             max(case when cnt is not null then cnt end) over (order by val, (case when cnt is null then 1 else 2 end)) as a_cnt
      from av
     ) av
where cnt is null;


val
a_val
a_cnt


2
1
2

4
3
2

9
4
3

10
4
3


Warning: Null value is eliminated by an aggregate or other SET operation.