-- if accidentally left on from previous runs
SET STATISTICS XML OFF;
SET STATISTICS IO,TIME OFF;
-- which version?
select @@VERSION;
-- drop if they exist
drop view lAN;
drop view vAt2;
drop table At2;
drop table AN;
-- create the tables
create table AN (
id int not null primary key
);
create table At2 (
id int not null foreign key references AN(id),
stamp datetime not null,
primary key (
id,
stamp
)
);
go
-- create a view
create view vAt2
as
select
id,
stamp,
ROW_NUMBER() over (
partition by
id
order by
stamp desc
) as v
from
At2
go
create view lAN
as
with a2 as (
select
*
from
vAt2
where
v = 1
)
select
an.id as AN_id,
(select id from a2 where a2.id = an.id) as AT2_id,
(select stamp from a2 where a2.id = an.id) as AT2_stamp,
(select stamp from a2 where a2.id = an.id) as AT2_stamp_again
from
AN an;
go
-- populate the tables
declare @numberOfRows int = 1000;
with rowGen(rowNum) as (
select
1
union all
select
rowNum + 1
from
rowGen
where
rowNum < @numberOfRows
)
insert into AN (id)
select rowNum from rowGen
option (maxrecursion 0);
declare @now datetime = getdate();
declare @then datetime = dateadd(day, -333, @now);
insert into At2 (id, stamp) select id, @now from AN;
insert into At2 (id, stamp) select id, @then from AN;
go
SET STATISTICS XML ON;
SET STATISTICS IO,TIME ON;
-------------------------------------------------------------
select
avg(cast(AT2_id as bigint)),
min(AT2_stamp),
min(AT2_stamp_again)
from
lAN
where
AT2_id is not null;
-------------------------------------------------------------
SET STATISTICS XML OFF;
SET STATISTICS IO,TIME OFF;