Editing report to show first and last occurrence of Event ID

[Henry2020]

This is what I have so far, it lists the last occurrence of event ID 14 when found on a computer. I'm trying to tweak it so it also lists the first time it occurred as well.


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tblAssetCustom.Model,
tblBIOS.SMBIOSBIOSVersion As BIOS,
tblBIOS.ReleaseDate As [Bios Date],
tblBIOS.Lastchanged [Bios last changed],
tblAssets.Lastseen,
tblAssets.Lasttried,
Event14.LatestTimeGenerated
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join (Select tblNtlog.AssetID,
Max(tblNtlog.TimeGenerated) As LatestTimeGenerated
From tblNtlog
Inner Join tblNtlogSource On tblNtlogSource.SourcenameID =
tblNtlog.SourcenameID
Where tblNtlog.Eventcode = '14' And tblNtlogSource.Sourcename = 'TPM'
Group By tblNtlog.AssetID) As Event14 On Event14.AssetID =
tblAssets.AssetID
Where Event14.LatestTimeGenerated > GetDate() - 60 And tblAssetCustom.State = 1

[RCorbeil]

Couldn't you just duplicate your SELECT Max() join, making the copy SELECT Min(), and add the timestamp from that to your output?