Work stuff

<syntaxhighlight lang="sql"> 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; </syntaxhighlight>