Asset user relationship history report

[Reen87]

Hello all. I have a small report problem. I have one report that maps all the curent active user/asset relationships.

but I also need this report totake older relationships. (aka. if a user has 1 laptop, and 1 desktop. this report only shows the one with the latest date).

anybody that can help me with this? I am not used too sql.

Select Top 1000000 tblAssetUserRelations.StartDate,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Description,
tblAssetUserRelations.Comments,
tblADusers.Lastname,
tblADusers.Firstname,
tblAssets.Username
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetRelations
On tblAssets.AssetID = tblAssetRelations.ParentAssetID
Inner Join tblAssetUserRelations On tblAssets.AssetID =
tblAssetUserRelations.AssetID
Inner Join tblADusers On tblADusers.Username = tblAssetUserRelations.Username
And tblADusers.Userdomain = tblAssetUserRelations.Userdomain
Where tblAssetCustom.State = 1
Group By tblAssetUserRelations.StartDate,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Description,
tblAssetUserRelations.Comments,
tblADusers.Lastname,
tblADusers.Firstname,
tblAssets.Username
Order By tblAssetUserRelations.StartDate Desc,
tblADusers.Lastname

[Daniel_B · Accepted solution]

In your report you had joined tblAssetRelations as well. That might have prevented some assets to be displayed. Asset-user-relations are stored in tblAssetUserRelations only. Please find a modified report below.

Select Top 1000000 tblAssetUserRelations.StartDate,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Description,
  tsysAssetRelationTypes.Name As Relation,
  tblAssetUserRelations.Comments,
  tblADusers.Lastname,
  tblADusers.Firstname,
  tblAssets.Username
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 tblADusers On tblADusers.Username = tblAssetUserRelations.Username
    And tblADusers.Userdomain = tblAssetUserRelations.Userdomain
  Inner Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
    tblAssetUserRelations.Type
Where tblAssetCustom.State = 1
Order By tblAssetUserRelations.StartDate Desc,
  tblADusers.Lastname