Work stuff

use testdb
go

drop table if exists dbo.emp_values;
drop view if exists dbo.current_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);
/* WAITFOR DELAY '00:00:01'; */
insert into emp_values (name,value,period) values ('test1', 120, 1);
/* WAITFOR DELAY '00:00:01'; */
insert into emp_values (name,value,period) values ('test2', 1111, 1);
/* WAITFOR DELAY '00:00:01'; */
insert into emp_values (name,value,period) values ('test4', 387.4, 1);
/* WAITFOR DELAY '00:00:01'; */

insert into emp_values (name,value,period) values ('test1', 10, 2);
/* WAITFOR DELAY '00:00:01'; */
insert into emp_values (name,value,period) values ('test1', 20, 2);
/* WAITFOR DELAY '00:00:01'; */
insert into emp_values (name,value,period) values ('test2', 1, 2);
/* WAITFOR DELAY '00:00:01'; */
insert into emp_values (name,value,period) values ('test4', 87.4, 2);
/* WAITFOR DELAY '00:00:01'; */

insert into emp_values (name,value,period) values ('test1', 180, 3);
/* WAITFOR DELAY '00:00:01'; */
insert into emp_values (name,value,period) values ('test1', 2880, 3);
/* WAITFOR DELAY '00:00:01'; */
insert into emp_values (name,value,period) values ('test2', 81, 3);
/* WAITFOR DELAY '00:00:01'; */
insert into emp_values (name,value,period) values ('test4', 88887.4, 3);
/* WAITFOR DELAY '00:00:01'; */

insert into emp_values (name,value,period) values ('test4', 88787.4, 1);
/* WAITFOR DELAY '00:00:01'; */
insert into emp_values (name,value,period) values ('test4', 8678787.4, 2);
/* WAITFOR DELAY '00:00:01'; */
insert into emp_values (name,value,period) values ('test2', 8, 2);
/* WAITFOR DELAY '00:00:01'; */
insert into emp_values (name,value,period,added_on) values ('test4', 867842435287.4, 2,DATEADD(day,1,CURRENT_TIMESTAMP));

go
create view current_emp_values as
    SELECT a.* FROM emp_values a JOIN (SELECT name,max(id) as max_id,period FROM emp_values b GROUP BY name,period) b ON a.name = b.name AND a.id = b.max_id;

go

select * from current_emp_values;
go

select * from emp_values WHERE period = 2 order by name,added_on
go

SELECT a.* FROM emp_values a JOIN (SELECT name,max(id) as max_id,period FROM emp_values b GROUP BY name,period) b ON a.name = b.name AND a.id = b.max_id
WHERE a.period = 2;
go;

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

SELECT a.* FROM emp_values a JOIN (SELECT name,max(id) as max_id,period FROM emp_values b GROUP BY name,period) b ON a.name = b.name AND a.id = b.max_id
WHERE a.period = 3;
go;

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