Remove Asset Group Duplicates

[ssuarez]

I have a code that produces AssetName, Username, AssetGroup, NewAsset, Model IP Address, Manufacturer, Serial number, warranty expiration, and State.

Because all assets are in the asset group called, 'default group,' assets that are in other groups are showing up twice in my report.

How do I prevent lansweeper from generating a report that shows 'default group' if that asset is in another asset group already?

Below is my code:

Select Top 1000000 tblAssetCustom.AssetID,
  tblAssets.AssetName,
  tblAssets.Username,
  tblAssetGroups.AssetGroup,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  Case When tblAssetCustom.Warrantydate > DateAdd(day, -14, GetDate()) Then 'X'
    Else ' ' End As [New Asset],
  tblAssetCustom.Model,
  tblAssets.IPAddress,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Serialnumber,
  tblAssetCustom.Warrantydate As [Warranty Expiration],
  tblAssetCustom.State
From tblAssetCustom
  Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
  Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
    tblAssetGroupLink.AssetGroupID
Where tblAssetCustom.Warrantydate < GetDate() And tblAssetCustom.State = 1
Order By [Warranty Expiration] Desc,
  tblAssets.AssetName

[Hemoco · Accepted solution]

Hi,

Please use the following query:


Select Top 1000000 tblAssetCustom.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tblAssetGroups.AssetGroup,
tsysAssetTypes.AssetTypeIcon10 As icon,
Case When tblAssetCustom.Warrantydate > DateAdd(day, -14, GetDate()) Then 'X'
Else ' ' End As [New Asset],
tblAssetCustom.Model,
tblAssets.IPAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Serialnumber,
tblAssetCustom.Warrantydate As [Warranty Expiration],
tblAssetCustom.State
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Where tblAssetGroups.AssetGroup <> 'default group' And
tblAssetCustom.Warrantydate < GetDate() And tblAssetCustom.State = 1
Union
Select Top 1000000 tblAssetCustom.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tblAssetGroups.AssetGroup,
tsysAssetTypes.AssetTypeIcon10 As icon,
Case When tblAssetCustom.Warrantydate > DateAdd(day, -14, GetDate()) Then 'X'
Else ' ' End As [New Asset],
tblAssetCustom.Model,
tblAssets.IPAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Serialnumber,
tblAssetCustom.Warrantydate As [Warranty Expiration],
tblAssetCustom.State
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Where tblAssetCustom.Warrantydate < GetDate() And tblAssetCustom.State = 1 And
tblAssets.AssetID In (Select Top 1000000 tblAssets.AssetID
From tblAssetGroups Inner Join tblAssetGroupLink
On tblAssetGroups.AssetGroupID = tblAssetGroupLink.AssetGroupID Inner Join
tblAssets On tblAssets.AssetID = tblAssetGroupLink.AssetID
Where tblAssetGroups.AssetGroup = 'default group') And
tblAssets.AssetID Not In (Select Top 1000000 tblAssets.AssetID
From tblAssetGroups Inner Join tblAssetGroupLink
On tblAssetGroups.AssetGroupID = tblAssetGroupLink.AssetGroupID Inner Join
tblAssets On tblAssets.AssetID = tblAssetGroupLink.AssetID
Where tblAssetGroups.AssetGroup <> 'default group')
Order By [Warranty Expiration] Desc,
AssetName