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;