SQL Server Left Join and replace NULL value in VIEW with previous value-CodePudding

I'm using SQL Server and I have two tables:

Table A

DATE         CUR     VALUE
----------------------------
2022-01-01   USD    18.253   
2022-01-02   EUR    20.21
2022-01-02   USD    19.23
2022-02-02   TRY    16.12
2022-02-10   TRY    15.12
2022-02-11   USD    13.321

Table B

DATE         CUR     AMOUNT
----------------------------
2022-01-03   USD    100   
2022-01-06   EUR    125   
2022-02-10   TRY    110
2022-02-12   USD    112

I try to create a view using LEFT JOIN to get this result :

DATE         CUR    AMOUNT  VALUE 
-----------------------------------
2022-01-03   USD     100    19.23
2022-01-06   EUR     125    20.21
2022-02-10   TRY     110    15.12
2022-02-12   USD     112    13.321

The value column in view is from Table A and if is Null it should be the previous value according to previous DATE and CUR columns.

This is my query:

SELECT B.DATE, B.CUR, B.AMOUNT, A.VALUE 
FROM TABLE_A B
LEFT JOIN TABLE_A A ON A.DATE = B.DATE AND A.CUR = B.CUR

But I got these results :

DATE         CUR    AMOUNT  VALUE 
-----------------------------------
2022-01-03   USD     100    NULL
2022-01-06   EUR     125    NULL
2022-02-10   TRY     110    15.12
2022-02-12   USD     112    NULL

CodePudding user response：

Your current query adds some confusion because you're desired results are from Table_B not Table_A and your Amount and Value columns are in the opposite tables.

Ignoring that however and just looking at your sample data and desired results, you can retrieve the VALUE from Table_A using a correlated subquery:

select [date], cur, amount, (
  select top(1) [value] 
    from Table_A a 
    where a.cur = b.cur and a.[date] <= b.[date] 
    order by [date] desc
) [Value]
from Table_B b;