SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE #ServerAudit (
    ServerName varchar(100) NOT NULL,
    SQLVersion varchar(50) NOT NULL,
    SQLPatchLevel varchar(20) NOT NULL,
    OS varchar(50) NOT NULL,
    OSBuild varchar(20) NOT NULL,
    Written date NOT NULL,
    BuildDate datetime NOT NULL,
    Active BIT  NOT NULL
    );
INSERT INTO #ServerAudit VALUES
    ('SrvA',' MSSQL 2017',' SP1','Windows 10 Pro','19042.1237','2021-09-26','2021-09-26 01:11', '0'),
    ('SrvA',' MSSQL 2017',' SP4','Windows 10 Pro','19042.1237','2021-10-04','2021-10-04 02:22','1'),
    ('SrvB',' MSSQL 2017',' SP1','Windows 10 Pro','19042.1237','2021-09-27','2021-09-27 03:33','0'),
    ('SrvB',' MSSQL 2017',' SP4','Windows 10 Pro','19111.1234','2021-10-05','2021-10-05 04:44','1'),
    ('SrvC',' MSSQL 2017',' SP4','Windows 10 Pro','19111.1234','2021-10-05','2021-10-05 05:55','1'),
    ('SrvD',' MSSQL 2017',' SP4','Windows 10 Pro','19111.1234','2021-10-05','2021-10-06 06:06','0')

6 rows affected

-- Works
SELECT CA2.*
FROM #ServerAudit SA1
CROSS APPLY (
    SELECT 'The ' + ServerName + ' old server was deleted on ' + CONVERT(varchar(30), SA1.BuildDate, 120) AS Message
    FROM   #ServerAudit
    GROUP BY ServerName
    HAVING MAX(CAST(Active AS TINYINT)) = 0
) AS CA2
WHERE SA1.Active = 1


Message


The SrvD old server was deleted on 2021-10-04 02:22:00

The SrvD old server was deleted on 2021-10-05 04:44:00

The SrvD old server was deleted on 2021-10-05 05:55:00


-- Fails due to use of aggregate on SA1.BuildDate
SELECT CA2.*
FROM #ServerAudit SA1
CROSS APPLY (
    SELECT 'The ' + ServerName + ' old server was deleted on ' + CONVERT(varchar(30), MAX(SA1.BuildDate), 120) AS Message
    FROM   #ServerAudit
    GROUP BY ServerName
    HAVING MAX(CAST(Active AS TINYINT)) = 0
) AS CA2
WHERE SA1.Active = 1
Msg 4101 Level 15 State 1 Line 5
Aggregates on the right side of an APPLY cannot reference columns from the left side.