Reporting Group

Can someone help me with this default report called "Assets by Owner's with Details".  My boss would like this report to not show "retired" assets. 

declare @AssetDeptOwner uniqueidentifier
declare @AssetUserOwner uniqueidentifier
declare @UserDept uniqueidentifier
set @AssetDeptOwner = '1466e770-4413-4517-a89d-6599b8a7f144'
set @AssetUserOwner = 'ed35a8d1-bf60-4771-9dde-092c146c485a'
set @UserDept = 'd9656701-b8ee-48c1-a4e3-5741ca18471d'
DECLARE @v1_TrusteeScope nvarchar(max)
declare @Dept uniqueidentifier
SET @v1_TrusteeScope = '%TrusteeScope%'
set @Dept = '%Dept%'
DECLARE @Culture nvarchar(10)
SET @Culture = '%_culture%'
select @Culture = ltrim(rtrim(Culture)) from fnGetBaseCultures(@Culture) where Priority = 3
declare @active uniqueidentifier
set @active = '0A0203A5-D2B6-49F1-A53B-5EC31A89437C'
DECLARE @activestr nvarchar(200)
select @activestr = Name from vResourceName where ResourceGuid = @active and Culture = @Culture
if(@activestr is null or @activestr = '')
begin
 select @activestr = Name from vResourceName where ResourceGuid = @active and Culture = ''
end
select distinct
 _ResourceGuid _ItemGuid,
 vU.[Name] [Owner],
 isnull(vUD.[Name], vD.[Name]) [Department],
 vi.[Name],
 scAssetType.[String] as [Asset Type],
 isnull(scAssetStatus.[String], @activestr) as [Status],
 [Manufacturer],
 [Model],
 [Serial Number],
 [System Number],
 [Barcode],
 [Last Barcode Scan]
from
 vAsset va
 join vRM_Asset_Item vi on va._ResourceGuid = vi.Guid
 outer apply
(
 select top 1 String from StringCache
 where BaseGuid = va._ResourceTypeGuid and Culture = @Culture and StringRef = 'item.name'
) scAssetType
outer apply
(
 select top 1 String from StringCache
 where BaseGuid = va._AssetStatusGuid and Culture = @Culture and StringRef = 'item.name'
) scAssetStatus
 left join ResourceAssociation ownDept on va._ResourceGuid = ownDept.ParentResourceGuid and ownDept.ResourceAssociationTypeGuid = @AssetDeptOwner
 left join ResourceAssociation ownUser on va._ResourceGuid = ownUser.ParentResourceGuid and ownUser.ResourceAssociationTypeGuid = @AssetUserOwner
 left join ResourceAssociation usrDept on ownUser.ChildResourceGuid = usrDept.ParentResourceGuid and usrDept.ResourceAssociationTypeGuid = @UserDept
 left join vRM_Department_Item vUD on usrDept.ChildResourceGuid = vUD.Guid
 left join vRM_User_Item vU on ownUser.ChildResourceGuid = vU.Guid
 left join vRM_Department_Item vD on ownDept.ChildResourceGuid = vD.Guid
where
 vi.Guid in (select ResourceGuid from ScopeMembership where ScopeCollectionGuid in (select ScopeCollectionGuid from dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope)))
 and ((usrDept.ChildResourceGuid = @Dept) or (ownDept.ChildResourceGuid = @Dept) or (@Dept = '00000000-0000-0000-0000-000000000000'))
order by
 [Name],
 Owner,
 Department
        

You can see the Asset Statuses in vRM_Fixed_Asset_Status_Resource_Type_Item

You can use the Resource Association of 'Asset's Status' [3028166f-c0d6-41d8-9cb7-f64852e0fd01] to show this.

SELECT  
  vai.Name AS [AssetName],  
  fasri.Name AS [Status] 
FROM ResourceAssociation ra 
  INNER JOIN vRM_Asset_Item vai ON ra.[ParentResourceGuid] = vai.[Guid]  
  INNER JOIN vRM_Fixed_Asset_Status_Resource_Type_Item fasri 
  ON ra.[ChildResourceGuid] = fasri.[Guid] AND (ra.[ResourceAssociationTypeGuid] = '3028166f-c0d6-41d8-9cb7-f64852e0fd01')  

You could then filter on the Statuses you want to see.

As a side note the Asset status may not exist for all assets and would be considered Active.  You may need a left join to ensure all assets are returned.