We started tracking the history of who has had equipment. We want two reports-- one to show all available equipment and one to show who equipment is assigned to. I wrote something that worked until the equipment was reassigned to someone else.
If a monitor was assigned to Joe and then Adam, it should not appear on this report, but it does since there is an end date for Joe.
Our easiest solution is just don't track the history and delete the relationship when it goes to a new person, but we rather not do that.
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
DatePart(yyyy, tblAssetCustom.Custom5) As Year,
tblAssetCustom.Comments As Usage,
tblAssetCustom.Custom2 As Size,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblAssetCustom.PurchaseDate,
tblState.Statename,
tblAssetUserRelations.Comments
From tblAssets
Left Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tblAssetUserRelations On
tblAssets.AssetID = tblAssetUserRelations.AssetID
Left Join tblState On tblState.State = tblAssetCustom.State
Left Outer Join tblADusers On
tblADusers.Userdomain = tblAssetUserRelations.Userdomain And
tblADusers.Username = tblAssetUserRelations.Username
Where (tblAssetUserRelations.StartDate Is Null And tblAssets.Assettype = 208) Or
(Not tblAssetUserRelations.StartDate Is Null And tblAssets.Assettype = 208 And
Not tblAssetUserRelations.EndDate Is Null)
Order By Year Desc,
tblAssets.AssetName
