Compare table column with a range of values and take the position of that value from another table

I got stuck with this problem, please help if possible.

Suppose we have two tables, Table A and Table B. Table A looks like this:

ReportDate  val1
----------------
2021-08-16  0.1
2021-08-15  0.1
2021-08-14  0.2
2021-08-13  0.2
2021-08-12  0.1

And Table B looks like this:

ReportDate  val2
----------------
2021-08-16  1.4
2021-08-16  4.1
2021-08-16  2.1
2021-08-16  1.9
2021-08-16  5.1
2021-08-16  2.4
2021-08-16  1.6
2021-08-16  4.6
2021-08-16  3.1
2021-08-16  0.9
2021-08-16  20.6
2021-08-16  0.4
2021-08-16  2.6
2021-08-16  0.1
2021-08-15  1.4
2021-08-15  20.9
2021-08-15  5.9
2021-08-15  0.1
2021-08-15  0.9
2021-08-15  0.4 ...

In table B we have around 23 values for each date, now we need to match val1 of table A to val2 of tableB for each date and return its position in the row for example 0.1 on 2021-08-16 in Table A should look for a match in table B val2 and should return its position in the row (14 in case of 0.1)

I tried multiple ways (including row_number(), etc) but none of them is working. Many thanks in advance. I need an output like this

ReportDate  val1  Match
------------------------
2021-08-16  0.1  14
2021-08-15  0.1   4 and so on..

I am trying to use row_number so, that I can create a row number on TableB and then use Case statement with Table A to get the row_number which matches with val1, I tried similar queries like below but none of them are working the sequence of the rows get changed whenever I use ranking functions, as I need the sequence of the rows exactly as shown for the table B otherwise the position of the values will change

SELECT *
FROM (SELECT *, row_number() OVER (Partition by rnk, reportdate ORDER BY ReportDate)rnk2 FROM (SELECT *, row_number() OVER (PARTITION BY ReportDate, val2 ORDER BY ReportDate DESC) rnk
FROM (SELECT ReportDate,  val2 
FROM tableB) a
ORDER BY ReportDate DESC) b
) c
ORDER BY ReportDate DESC, rnk2

Answer

You can use simple left join and row_number()

Create table #temp
(
ReportDate date,  val1 decimal(6,1)
)

Create table #temp2
(
ReportDate date,  val2 decimal(6,1)
)

insert into #temp
values
('2021-08-16',  0.1)
,('2021-08-15',  0.1)
,('2021-08-14',  0.2)
,('2021-08-13',  0.2)
,('2021-08-12',  0.1)

insert into #temp2
values
('2021-08-16',  1.4 )
,('2021-08-16',  4.1 )
,('2021-08-16',  2.1 )
,('2021-08-16',  1.9 )
,('2021-08-16',  5.1 )
,('2021-08-16',  2.4 )
,('2021-08-16',  1.6 )
,('2021-08-16',  4.6 )
,('2021-08-16',  3.1 )
,('2021-08-16',  0.9 )
,('2021-08-16',  20.6)
,('2021-08-16',  0.4 )
,('2021-08-16',  2.6 )
,('2021-08-16',  0.1 )
,('2021-08-15',  1.4 )
,('2021-08-15',  20.9)
,('2021-08-15',  5.9 )
,('2021-08-15',  0.1 )
,('2021-08-15',  0.9 )
,('2021-08-15',  0.4 )

select t1.ReportDate,t1.val1,rownum as [Match]  
from #temp as t1
left join  (
select *,ROW_NUMBER() over (partition by ReportDate order by (select null)) as rownum from #temp2
) as t2
on t1.ReportDate = t2.ReportDate and t1.val1 = t2.val2
where rownum is not null

drop table #temp
drop table #temp2