Make a view of a difference table

Lets imagine two tables - T_Name, T_nameAttributes


Name NameID
Sara 2
Matthew 3


NameID AttributeID Value
2 3 Red Apple
2 5 $20

Originally retrieving data from them looks like this

select * 
from T_name 
left join T_nameAttributes 
    on T_name.NameID = T_nameAttributes.NameID 
    and AttributeID = 3 and nameID = 2

Sara, 3, Red Apple

Everyday those values saved into the same table schemas with an additional 'Date' column and all fine here, I can easily retrieve value on a requested date

The problem here - I want to save DB space and to store only changed values, so my log tables now look like that: (this part is already done)


Date Name NameID
2023-01-01 Sara 2
2023-10-05 Sarah 2 // name changed


Date AttributeID NameID Value
2023-01-01 3 2 Red Apple
2023-10-04 3 2 Green Apple // value changed
2023-01-01 5 2 $20
2023-02-12 5 2 $0
2023-10-09 5 2 $70

The question: Both tables can change its values at the random days, I want to retrieve a joined tables with a rows based on an each day in a month

Select date, name, value 
from T_NameLog 
left join T_nameAttributesLog <...> 
where date between '2023-10-02' and '2023-10-05' 
and AttributeID = 3 and nameID = 2

expected results:

date name attribute
2023-10-02 Sara Red Apple
2023-10-03 Sara Red Apple
2023-10-04 Sara Green Apple
2023-10-05 Sarah Green Apple


  • First generate a row per date. Then use CROSS APPLY with a TOP clause to get the most up-to-date rows of the tables. Instead of looking up only one name and one attribute, I have written my query so it can find all names and attributes. This is done with TOP (1) WITH TIES in combination with an ORDER BY RANK().

    If you want one name only or one attribute only, just comment out the ID conditions in the WHERE clauses. (But in that case it would suffice to have TOP (1) without the WITH TIES clause and a mere ORDER BY [Date] DESC.)

      days as 
        select cast('2023-10-02' as date) as dt,
               cast('2023-10-05' as date) as last_dt
        union all
        select dateadd(day, 1, dt),
        from days
        where dt < last_dt
    select days.dt, n.name, na.value
    from days
    cross apply
      select top (1) with ties
      from t_namelog nl
      where nl.[Date] <= days.dt
      -- and nl.nameid = 2
      order by rank() over (partition by nl.nameid order by nl.[Date] desc)
    ) n
    cross apply
      select top (1) with ties
      from t_nameattributeslog nal
      where nal.nameid = n.nameid
      and nal.[Date] <= days.dt
      -- and nal.attributeid = 3
      order by rank() over (partition by nal.attributeid order by nal.[Date] desc)
    ) na
    order by days.dt, n.nameid, na.attributeid;

    Demo: https://dbfiddle.uk/WyhGYzqy