Lets imagine two tables - T_Name, T_nameAttributes
T_name
| Name | NameID |
|---|---|
| Sara | 2 |
| Matthew | 3 |
T_nameAttributes
| 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)
T_NameLog
| Date | Name | NameID |
|---|---|---|
| 2023-01-01 | Sara | 2 |
| 2023-10-05 | Sarah | 2 // name changed |
T_nameAttributesLog
| 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.)
with
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),
last_dt
from days
where dt < last_dt
)
select days.dt, n.name, na.value
from days
cross apply
(
select top (1) with ties
nl.name,
nl.nameid
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
nal.value,
nal.attributeid
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;