Get last comment from asset

[Danielbo]

Hello,

When we set asset state to Repair we also add a comment about what the issue was. I have made a report wich finds the assets with the correct asset ID and then shows the comments sorted by asset ID since i didnt mange to sort on date. But how do i make it only show the last comment ? Maybe also hide the commentID if thats possible ?

My current report :

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssetCustom.State,
  tblAssetCustom.Warrantydate,
  tblAssetComments.Comment,
  tblAssetComments.Added,
  tblAssetComments.AddedBy,
  tblAssetComments.CommentID
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblAssetComments On tblAssets.AssetID = tblAssetComments.AssetID
Where tblAssetCustom.State = 13
Order By tblAssetComments.CommentID Desc

EDIT : is it also possible to send a alert if there havent been a comment the last 14 days ? Eventually a modified report.

[Susan_A · Accepted solution]

The report below lists "repair" assets whose last comment is from more than 14 days ago and the details of that last comment. To remove the date filter, clear the Criteria column of the tblAssetComments.Added expression. You can have the results of any report in the Reports tab sent via email on a scheduled basis by following the instructions in this knowledge base article.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssetCustom.State,
  tblAssetCustom.Warrantydate,
  tblAssetComments.Comment,
  tblAssetComments.AddedBy,
  tblAssetComments.Added
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblAssetComments On tblAssets.AssetID = tblAssetComments.AssetID
  Inner Join (Select tblAssetComments.AssetID,
    Max(tblAssetComments.Added) As Max
  From tblAssetComments
  Group By tblAssetComments.AssetID) SubQuery On SubQuery.AssetID =
    tblAssetComments.AssetID And SubQuery.Max = tblAssetComments.Added
Where tblAssetCustom.State = 13 And tblAssetComments.Added < GetDate() - 14
Order By tblAssetComments.Added