Work stuff

use testdb
go

drop table if exists dbo.emp_values;

create table dbo.emp_values
(
    id         bigint identity,
    name       nvarchar(255) not null,
    value      money         not null,
    period int not null,
    added_on   datetime2     not null default current_timestamp
)
go

truncate table emp_values;
insert into emp_values (name,value,period) values ('test1', 110, 1);
insert into emp_values (name,value,period) values ('test1', 120, 1);
insert into emp_values (name,value,period) values ('test2', 1111, 1);
insert into emp_values (name,value,period) values ('test4', 387.4, 1);

insert into emp_values (name,value,period) values ('test1', 10, 2);
insert into emp_values (name,value,period) values ('test1', 20, 2);
insert into emp_values (name,value,period) values ('test2', 1, 2);
insert into emp_values (name,value,period) values ('test4', 87.4, 2);

insert into emp_values (name,value,period) values ('test1', 180, 3);
insert into emp_values (name,value,period) values ('test1', 2880, 3);
insert into emp_values (name,value,period) values ('test2', 81, 3);
insert into emp_values (name,value,period) values ('test4', 88887.4, 3);

insert into emp_values (name,value,period) values ('test4', 88787.4, 1);
insert into emp_values (name,value,period) values ('test4', 8678787.4, 2);
insert into emp_values (name,value,period,added_on) values ('test4', 867842435287.4, 2,DATEADD(day,1,CURRENT_TIMESTAMP));

select * from emp_values

SELECT a.* FROM emp_values a JOIN (SELECT name,max(added_on)AS added_on,period FROM emp_values b GROUP BY name,period) b ON a.name = b.name AND a.added_on = b.added_on AND a.period=b.period
WHERE a.period = 2;


SELECT a.* FROM emp_values a JOIN (SELECT name,max(added_on)AS added_on,period FROM emp_values b WHERE b.added_on <= CURRENT_TIMESTAMP GROUP BY name,period) b ON a.name = b.name AND a.added_on = b.added_on AND a.period=b.period
WHERE a.period = 2;

SELECT a.* FROM emp_values a JOIN (SELECT name,max(added_on)AS added_on,period FROM emp_values b GROUP BY name,period) b ON a.name = b.name AND a.added_on = b.added_on AND a.period=b.period
WHERE a.period = 3;


SELECT a.* FROM emp_values a JOIN (SELECT name,max(added_on)AS added_on,period FROM emp_values b WHERE b.added_on <= CURRENT_TIMESTAMP GROUP BY name,period) b ON a.name = b.name AND a.added_on = b.added_on AND a.period=b.period
WHERE a.period = 3;