I have these two tables

daily_info

change_values

From these two tables, I want to create a new table that will look like this

history_table

So far I could only join these two tables and try to use the case-when clause, I know I need to use lag but I am not sure how

My code

Select id, 
    date,
    (Case When change_col = 'place' Then new_value End) As place,
    (Case When change_col = 'name' Then new_value End) As name
From daily_info
Left Join change_values On d.id = c.id And d.date = c.date

CodePudding user response：

Join (apply) you change table based on the date, once for each change type.

declare @daily_info table ([date] date, id int, [name] varchar(32), place varchar(32));

insert into @daily_info ([date], id, [name], place)
values
('2022-04-11', 1, 'Jhon', null),
('2022-04-12', 1, 'Jhon', null),
('2022-04-13', 1, 'Jhon', null),
('2022-04-14', 1, 'Jhon', null),
('2022-04-15', 1, 'Jhon', null),
('2022-04-16', 1, 'Jhon', null);

declare @change_values table ([date] date, id int, change_col varchar(32), new_value varchar(32));

insert into @change_values ([date], id, change_col, new_value)
values
('2022-04-14', 1, 'place', 'Chicago'),
('2022-04-16', 1, 'place', 'India'),
('2022-04-16', 1, 'name',  'John,Dan');

select DI.[date], DI.id
    , coalesce(CV2.new_value, DI.[name]) [name]
    , coalesce(CV1.new_value, DI.place) place
from @daily_info DI
outer apply (
    select top 1 change_col, new_value
    from @change_values CV
    where CV.[date] <= DI.[date] and change_col = 'place'
    order by [date] desc
) CV1
outer apply (
    select top 1 change_col, new_value
    from @change_values CV
    where CV.[date] <= DI.[date] and change_col = 'name'
    order by [date] desc
) CV2;

Returns: