Duplicate Assets

[JZastow]

I have about 900 Assets on site and am trying to create or modify the Warranty report to show the users full name not the login ID soon as i add tblADusers and check Displayname my Asset count jumps from 900 to 624078

Select Top (1000000) tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblWarrantyDetails.WarrantyStartDate,
tblWarrantyDetails.WarrantyEndDate,
Case
When GetDate() > tblWarrantyDetails.WarrantyEndDate Then 'yes'
Else 'no'
End As Expired,
tsysAssetTypes.AssetTypename As Type,
tblAssetCustom.SerialNumberScanned
From tblWarranty
Inner Join tblWarrantyDetails On tblWarranty.WarrantyId =
tblWarrantyDetails.WarrantyId
Inner Join tblAssets On tblWarranty.AssetId = tblAssets.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblADusers On tblADusers.Displayname = tblAssets.AssetID
Order By tblAssets.AssetName,
tblWarrantyDetails.WarrantyEndDate Desc

[JZastow]

both fix's sort of work but now i am missing the Display name from AD and instead of getting 1 asset per page i still get duplicates but 2 is easer to work with then 100's of the same asset 

[Mister_Nobody · Accepted solution]

1. You can add 

tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.Name,
tblADusers.Displayname,

2. There are no duplicates on the your screenshot - every asset has 2 sequental warranties. 

[JZastow]

FYI i talked to a SQL program about the duplicates entrees  had me and Min to 2 lines 

Select Distinct Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
Min(tblWarrantyDetails.WarrantyEndDate) As Min_Date,
tsysAssetTypes.AssetTypename As Type,
tblAssetCustom.SerialNumberScanned,
tblADusers.Displayname
From tblWarranty
Inner Join tblWarrantyDetails On tblWarranty.WarrantyId =
tblWarrantyDetails.WarrantyId
Inner Join tblAssets On tblWarranty.AssetId = tblAssets.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblADusers On tblADusers.username = tblAssets.username And
tblADusers.userdomain = tblAssets.userdomain
Group By tsysAssetTypes.AssetTypeIcon10,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysAssetTypes.AssetTypename,
tblAssetCustom.SerialNumberScanned,
tblADusers.Displayname
Order By tblAssets.AssetName,
Min_Date Desc

 

[JZastow]

That sucks they have no way to remove duplicates entrees, it funny that only some of the assets do that and not others 

[Mister_Nobody]

I think you have logical error in query

I fix one  Inner Join tblADusers On tblADusers.username = tblAssets.username And
tblADusers.userdomain = tblAssets.userdomain

Select Top (1000000) tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblWarrantyDetails.WarrantyStartDate,
  tblWarrantyDetails.WarrantyEndDate,
  Case
    When GetDate() > tblWarrantyDetails.WarrantyEndDate Then 'yes'
    Else 'no'
  End As Expired,
  tsysAssetTypes.AssetTypename As Type,
  tblAssetCustom.SerialNumberScanned
From tblWarranty
  Inner Join tblWarrantyDetails On tblWarranty.WarrantyId =
      tblWarrantyDetails.WarrantyId
  Inner Join tblAssets On tblWarranty.AssetId = tblAssets.AssetID
  Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblADusers On tblADusers.username = tblAssets.username And
      tblADusers.userdomain = tblAssets.userdomain
Order By tblAssets.AssetName,
  tblWarrantyDetails.WarrantyEndDate Desc

[brandon_jones]

Try replacing you first line with this: Select Distinct Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon. That should take care of the duplicate issue.