SQL Query to get the value of a product given a date

Question

I have a table which gives the rate of a product on a particular date, #tableA.

create table #tableA
(
    Id int not null,
    ValueDate date,
    Price decimal(9,2)
)

insert into #tableA (Id, ValueDate, Price)
values 
    (1, '2020-08-01', 100),
    (1, '2020-08-05', 110),
    (1, '2020-08-07', 50)

My other table has the id and the date the product is active.

create table #tableB
(
    Id int not null,
    Dates date
)

insert into #tableB (Id, Dates)
values
    (1, '2020-08-01'),
    (1, '2020-08-02'),
    (1, '2020-08-03'),
    (1, '2020-08-04'),
    (1, '2020-08-05'),
    (1, '2020-08-06'),
    (1, '2020-08-07'),
    (1, '2020-08-04')

I cannot find an efficient query where my resulting table gives the rate of the product on a given date.

I am expecting this result.

Id  Dates         ValueDate    Price 
-------------------------------------
1, '2020-08-01', '2020-08-01', 100
1, '2020-08-02', '2020-08-01', 100
1, '2020-08-03', '2020-08-01', 100
1, '2020-08-04', '2020-08-01', 100
1, '2020-08-05', '2020-08-05', 110
1, '2020-08-06', '2020-08-05', 110
1, '2020-08-07', '2020-08-07', 50

Answer 1

Something like this:

SELECT DISTINCT B.[id]
               ,B.[Dates]
               ,DS.*
FROM #tableB B
CROSS APPLY
(
    SELECT TOP 1 *
    FROM #tableA A
    WHERE B.[Id] = A.[Id]
        AND B.[Dates] >= A.[ValueDate]
        AND A.[Price] IS NOT NULL
    ORDER BY A.[ValueDate] DESC
) DS;

or this:

WITH DataSource AS
(
    SELECT DISTINCT B.[ID]
                   ,B.[Dates]        
                   ,A.[ValueDate]
                   ,A.[Price]
                   ,SUM(IIF(A.[ID] IS NOT NULL, 1, 0)) OVER (ORDER BY B.[Dates]) AS [GroupID]
    FROM #tableB B
    LEFT JOIN #tableA A
        ON B.[Id] = A.[Id]
        AND B.[Dates] = A.[ValueDate]
        AND A.[Price] IS NOT NULL
)
SELECT [ID]
      ,[Dates]
      ,MAX([ValueDate]) OVER (PARTITION BY [GroupID]) AS [ValueDate]
      ,MAX([Price]) OVER (PARTITION BY [GroupID]) AS [Price]
FROM DataSource;