I need a suggestion for how to create a SQL instead a function for, Sql Server

Language: Layout:

create table a (
    globalid    varchar(36)  not null primary key
  , date        datetime2(2) not null
  , description varchar(128) not null
);
create table b (
    rel_globalid varchar(36)  not null
  , globalid     varchar(36)  not null
  , filename     varchar(128) not null
  , date         datetime2(2) not null
);
insert into a (globalid, date, description) values
  ('aa','2016-12-06','Description 01')
, ('ab','2016-12-07','Description 02')
, ('ac','2016-12-07','Description 03');

insert into b (globalid, rel_globalid, filename, date) values
 ('ba','aa','file02.jpg','2016-12-06T05:13:25')
,('bb','aa','file01.jpg','2016-12-06T03:45:12')
,('bc','ab','file03.jpg','2016-12-07T08:54:31')
,('bd','ac','file05.jpg','2016-12-07T11:39:19')
,('be','ac','file04.jpg','2016-12-07T10:48:13')
,('bf','ac','file06.jpg','2016-12-07T15:08:17');

with cte as (
  select
    globalid
  , rel_globalid
  , filename
  , date
  , rn_a = row_number() over (partition by rel_globalid order by date asc)
  , rn_d = row_number() over (partition by rel_globalid order by date desc)
  from b)
, x as (
  select
    globalid
  , rel_globalid
  , filename
  , date
  , rn_a
  , rn_d
  , seq = row_number() over (order by globalid, date asc)
  from cte
  where rn_a = 1 
    or  rn_d = 1
)

select 
      a.globalid
    , first_file     = ba.globalid
    , last_file      = bd.globalid
    , first_file_num = ba.seq
    , last_file_num  = bd.seq
    from a
      left join x as ba on a.globalid = ba.rel_globalid and ba.rn_a = 1
      left join x as bd on a.globalid = bd.rel_globalid and bd.rn_d = 1 and bd.rn_a !=1
    order by a.globalid;

View schema
Execution time: 0,03 sec, rows selected: 3, rows affected: 9, absolute service time: 0,19 sec