SELECT DISTINCT dbo.tb_files.file_pk              AS 'File Number',
       dbo.tb_files.file_subject                  AS 'File Subject',
       dbo.tb_compartment.comp_cab_fk             AS 'Storage Number',
       dbo.tb_compartment.comp_part               AS 'Compartment Number',
       dbo.tb_departments.dept_code               AS 'Department',
       dbo.tb_location.locationno                 AS 'Location',
       dbo.tb_users.usr_fullname                  AS 'Created by',
       dbo.tb_files.file_datecreated              AS 'Date Created',
       dbo.tb_states.states_desc                  AS 'State Description',
       (SELECT MAX(Dateadd(MONTH, f2.file_retentiondate, r2.rec_record_date)) AS
               Expr1
        FROM   dbo.tb_records AS r2
               INNER JOIN dbo.tb_files AS f2
                       ON f2.file_pk = r2.rec_filepart_fk
        WHERE  ( f2.file_pk = tb_files.file_pk )) AS 'ExpiryDate'
FROM   dbo.tb_states
       INNER JOIN dbo.tb_statehistory
               ON dbo.tb_states.states_pk = dbo.tb_statehistory.statehist_states_fk
       INNER JOIN dbo.tb_files
               ON dbo.tb_files.file_pk = dbo.tb_statehistory.statehist_file_fk
       INNER JOIN dbo.tb_fileparts
               ON dbo.tb_files.file_pk = dbo.tb_fileparts.fp_file_fk
       INNER JOIN dbo.tb_records
               ON dbo.tb_fileparts.fp_pk = dbo.tb_records.rec_filepart_fk
       INNER JOIN dbo.tb_users
               ON dbo.tb_files.file_createdby_usr_fk = dbo.tb_users.usr_pk
       INNER JOIN dbo.tb_compartment
               ON dbo.tb_files.file_comp_fk = dbo.tb_compartment.comp_pk
       INNER JOIN dbo.tb_cabinet
               ON dbo.tb_compartment.comp_cab_fk = dbo.tb_cabinet.cab_pk
       INNER JOIN dbo.tb_location
               ON dbo.tb_cabinet.cab_location_fk = dbo.tb_location.location_pk
       INNER JOIN dbo.tb_departments
               ON dbo.tb_cabinet.cab_dept_fk = dbo.tb_departments.dept_pk
WHERE  ( dbo.tb_files.file_active = 1 )
       AND ( dbo.tb_records.rec_active = 1 )
       AND ( dbo.tb_statehistory.statehist_states_fk != 5 )
GROUP  BY dbo.tb_states.states_pk,
          dbo.tb_states.states_desc,
          dbo.tb_statehistory.statehist_states_fk,
          dbo.tb_files.file_pk,
          dbo.tb_fileparts.fp_pk,
          dbo.tb_files.file_subject,
          dbo.tb_files.file_retentiondate,
          dbo.tb_compartment.comp_cab_fk,
          dbo.tb_compartment.comp_part,
          dbo.tb_departments.dept_code,
          dbo.tb_location.locationno,
          dbo.tb_users.usr_fullname,
          dbo.tb_files.file_datecreated
HAVING ( (SELECT MAX(Dateadd(MONTH, f2.file_retentiondate, r2.rec_record_date))
                 AS
                 Expr1
          FROM   dbo.tb_records AS r2
                 INNER JOIN dbo.tb_files AS f2
                         ON f2.file_pk = r2.rec_filepart_fk
          WHERE  ( f2.file_pk = tb_files.file_pk )) <= Getdate() )
```ORDER BY tb_files.file_pk ASC