Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎05-11-2020 09:40 AM
Hi,
I'm trying to write a report which displays all assets with a custom-field set to "yes" but also display the latest comments, my report is either showing only records with comments (latest) or multiples of the same asset with all the comments, is this possible ?
Thanks
Andy
Code so far:
I'm trying to write a report which displays all assets with a custom-field set to "yes" but also display the latest comments, my report is either showing only records with comments (latest) or multiples of the same asset with all the comments, is this possible ?
Thanks
Andy
Code so far:
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,
tblAssetCustom.Custom1 As Custom11
From tblAssets
Left Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Full Join tblAssetComments On tblAssets.AssetID = tblAssetComments.AssetID
Right Join (Select tblAssetComments.AssetID,
Max(tblAssetComments.Added) As Max
From tblAssetComments
Group By tblAssetComments.AssetID) SubQuery On SubQuery.Max =
tblAssetComments.Added And SubQuery.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1 And tblAssetCustom.Custom1 = 'yes'
Order By tblAssetComments.Added
Labels:
- Labels:
-
Report Center
0 REPLIES 0