cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
gritchie
Engaged Sweeper
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
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
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

View solution in original post

1 REPLY 1
Daniel_B
Lansweeper Alumni
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

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now