Hi
My company wants to start tracking headset usage in Lansweeper. I am trying to create a report that will show all active headsets and who they are currently used by. As each headset can be returned and reissued each record can contain multiple used by relationships. In my report I only want to pull the most recent record but can't get it working. So far I have:
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetRelationTypes.Name As Type,
tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.Description,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssetUserRelations.Username,
tblAssetCustom.Serialnumber,
tblAssetUserRelations.StartDate
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetUserRelations On tblAssets.AssetID =
tblAssetUserRelations.AssetID
Inner Join tsysAssetRelationTypes On tblAssetUserRelations.Type =
tsysAssetRelationTypes.RelationTypeID
Inner Join tblADusers On tblADusers.Username = tblAssetUserRelations.Username
And tblADusers.Userdomain = tblAssetUserRelations.Userdomain
Where tsysAssetRelationTypes.Name = 'used by' And tsysAssetTypes.AssetTypename =
'Headset' And tblAssetCustom.State = 1
This give me a report of almost what I need but I only want to see records with the most recent "tblAssetUserRelations.StartDate". I have tried max(tblAssetUserRelations.StartDate) but then I get multiple errors about aggregates and grouping.
Thanks in advance for any help.
Graham