CODEWITHSUNDEEP
sqlsql-serverdatetimeleft-joingaps-and-islands
Peter S
Peter S

Reputation: 575

Replace zero values with last available value in join

SQL-FIDDLE Link

I'm using SQL Server.

Edit: Used a wrong SQL Fiddle before - updated to correct one

The join statement:

select t1.A_NR, t1.V_DATE, t1.AMOUNT T1_AMOUNT, t2.AMOUNT T2_AMOUNT
from Table_1 t1 
left join Table_2 t2 on t1.A_NR = t2.A_NR and t1.V_DATE = t2.V_DATE

brings me this table with null values in the T2_Amount row.

+------+----------------------+-----------+-----------+
| A_NR |        V_DATE        | T1_AMOUNT | T2_AMOUNT |
+------+----------------------+-----------+-----------+
|    1 | 2020-01-01T00:00:00Z |       100 | 100       |
|    1 | 2020-01-02T00:00:00Z |       101 | (null)    |
|    1 | 2020-01-03T00:00:00Z |       102 | (null)    |
|    2 | 2020-01-01T00:00:00Z |       200 | 200       |
|    2 | 2020-01-02T00:00:00Z |       201 | (null)    |
|    2 | 2020-01-03T00:00:00Z |       202 | (null)    |
+------+----------------------+-----------+-----------+

I want to replace these values with the last available values from Table_2 like this:

+------+----------------------+-----------+-----------+
| A_NR |        V_DATE        | T1_AMOUNT | T2_AMOUNT |
+------+----------------------+-----------+-----------+
|    1 | 2020-01-01T00:00:00Z |       100 |       100 |
|    1 | 2020-01-02T00:00:00Z |       101 |       100 | -->  value from 01.01.2020
|    1 | 2020-01-03T00:00:00Z |       102 |       100 | -->  value from 01.01.2020
|    2 | 2020-01-01T00:00:00Z |       200 |       200 |
|    2 | 2020-01-02T00:00:00Z |       201 |       200 | -->  value from 01.01.2020
|    2 | 2020-01-03T00:00:00Z |       202 |       200 | -->  value from 01.01.2020
+------+----------------------+-----------+-----------+

Upvotes: 1

Views: 159

Answers (1)

GMB
GMB

Reputation: 222432

One option uses a correlated subquery, or a lateral join:

select t1.a_nr, t1.v_date, t1.amount as t1_amount, t2.* 
from table_1 t1 
outer apply (
    select top (1) t2.amount as t2_amount
    from table_2 t2 
    where t2.a_nr = t1.a_nr and t2.v_date <= t1.v_date
    order by t2.v_date desc
) t2

An alternative is to use some gaps-and-island technique: we can put unmatched records in groups along with the latest matched record with a window count, then use a window max to recover the value we want:

select a_nr, v_date, amount as t1_amount, 
    max(t2_amount) over(partition by a_nr, grp)
from (
    select t1.*, t2.amount as t2_amount,
        count(t2.amount) over(partition by t1.a_nr order by t1.v_date) as grp
    from table_1 t1 
    left join table_2 t2 on t2.a_nr = t1.a_nr and t2.v_date = t1.v_date
) t

Upvotes: 2

Related Questions