Work stuff - Revision history

Ian at 18:09, 8 September 2024

2024-09-08T18:09:16Z

Ian at 17:04, 8 September 2024

2024-09-08T17:04:08Z

Ian at 17:03, 8 September 2024

2024-09-08T17:03:04Z

Ian: Created page with " use testdb go drop table if exists dbo.emp_values; create table dbo.emp_values ( id bigint identity, name nvarchar(255) not null, valu..."

2024-09-08T16:59:42Z

Created page with " use testdb go drop table if exists dbo.emp_values; create table dbo.emp_values ( id bigint identity, name nvarchar(255) not null, valu..."

New page

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;

@@ Line 1: / Line 1: @@
-<syntaxhighlight lang="sql">
+<pre>
 use testdb
 go
@@ Line 50: / Line 50: @@
 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>
+</pre>

@@ Line 4: / Line 4: @@
 drop table if exists dbo.emp_values;
 create table dbo.emp_values
@@ Line 17: / Line 18: @@
 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
+go
-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
+select * from emp_values WHERE period = 2 order by name,added_on
 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
+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;
+WHERE a.period = 3;
-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
+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;
 </pre>