Report to select only most recent User Relation

[gritchie]

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

[Daniel_B · Accepted solution]

You could accomplish this by adding a subquery which lists the most recent asset user relation and then make a join to tblAssetUserRelations again on the AssetID and StartDate field:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  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 (Select Max(tblAssetUserRelations.StartDate) As StartDate,
    tblAssetUserRelations.AssetID,
    tblAssetUserRelations.Type
  From tblAssetUserRelations
  Group By tblAssetUserRelations.AssetID,
    tblAssetUserRelations.Type) tAssetUser On tblAssets.AssetID =
    tAssetUser.AssetID
  Inner Join tblAssetUserRelations On tAssetUser.AssetID =
    tblAssetUserRelations.AssetID And tAssetUser.StartDate =
    tblAssetUserRelations.StartDate
  Inner Join tsysAssetRelationTypes
    On tAssetUser.Type = tsysAssetRelationTypes.RelationTypeID
  Inner Join tblADusers On tblADusers.Username = tblAssetUserRelations.Username
    And tblADusers.Userdomain = tblAssetUserRelations.Userdomain
Where tsysAssetTypes.AssetTypename = 'Headset' And tsysAssetRelationTypes.Name =
  'used by' And tblAssetCustom.State = 1