Difference between revisions of "Work stuff"

From Combobulate
(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...")
 
Line 1: Line 1:
−
use testdb
+
<syntaxhighlight lang="sql">
 +
use testdb
 +
go
  
−
go
+
drop table if exists dbo.emp_values;
  
−
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
  
−
create table dbo.emp_values
+
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);
  
−
    id        bigint identity,
+
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);
  
−
    name       nvarchar(255) not null,
+
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));
  
−
    value      money        not null,
+
select * from emp_values
  
−
    period int not null,
+
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;
  
−
    added_on  datetime2    not null default current_timestamp
 
  
−
)
+
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;
  
−
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
 +
WHERE a.period = 3;
  
−
truncate table emp_values;
 
  
−
insert into emp_values (name,value,period) values ('test1', 110, 1);
+
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;
−
insert into emp_values (name,value,period) values ('test1', 120, 1);
+
</syntaxhighlight>
−
 
 
−
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;
 

Revision as of 17:03, 8 September 2024

<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>

Retrieved from ‘http://172.16.78.1/index.php?title=Work_stuff&oldid=301’