Getting duplicate asset names in custom report

[FayeJ]

So, trying to create a report (with specific wants from managment), it displays how I want, but I am getting duplicate assets names.  I've tried several solutions I've researched (and you can see I've even tried distinct), but still can't get rid of the duplicates.  Any help would be appreciated.

Select Distinct Top (1000000) tblAssets.AssetName,
tblAssetCustom.Model,
tsysIPLocations.IPLocation As [Location (Based on IP)],
Convert(Varchar(10),tblWarrantyDetails.WarrantyStartDate,101) As
[Warranty Start Date],
Convert(Varchar(10),tblWarrantyDetails.WarrantyEndDate,101) As
[Warranty End Date],
tblADusers.Displayname As [Last Known User],
tblProcessor.Name,
tblAssets.Memory,
Cast(Cast(tblDiskdrives.Size As BigInt) / 1024 / 1024 / 1024 As Numeric) As
[Total size (GB)]
From tblComputersystem
Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Outer Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblWarranty On tblAssets.AssetID = tblWarranty.AssetId
Inner Join tblWarrantyDetails On tblWarranty.WarrantyId =
tblWarrantyDetails.WarrantyId
Inner Join tblADusers On tblADusers.Username = tblAssets.Username
Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Where tblWarranty.ShipDate < '01/01/2019' And tblComputersystem.Domainrole < 2
And tblAssetCustom.State = 1
Group By tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Model,
tsysIPLocations.IPLocation,
Convert(Varchar(10),tblWarrantyDetails.WarrantyStartDate,101),
Convert(Varchar(10),tblWarrantyDetails.WarrantyEndDate,101),
tblADusers.Displayname,
tblProcessor.Name,
tblAssets.Memory,
Cast(Cast(tblDiskdrives.Size As BigInt) / 1024 / 1024 / 1024 As Numeric)
Order By tblAssets.AssetName

[FayeJ]

Anyway to get around this or will it just take manual manipulation of an export of the report?

[ASismey]

Hi,

Looks like its the Warranty Service values giving you duplicates , if you look at one with duplicates in the report you will probably have something like :

[ASismey]

Give this ago, I have taken the warranty Overview and amended the HDD Size column 

Select Distinct Top (1000000) tblAssets.AssetName,
  tblAssetCustom.Model,
  tsysIPLocations.IPLocation As [Location (Based on IP)],
  tblAssetCustom.PurchaseDate As [Purchase Date],
  tblAssetCustom.Warrantydate As [Warranty Expiration],
  tblADusers.Displayname As [Last Known User],
  tblProcessor.Name,
  tblAssets.Memory
From tblComputersystem
  Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Outer Join tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Inner Join tblWarranty On tblAssets.AssetID = tblWarranty.AssetId
  Inner Join tblWarrantyDetails On tblWarranty.WarrantyId =
      tblWarrantyDetails.WarrantyId
  Inner Join tblADusers On tblADusers.Username = tblAssets.Username
  Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
  Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Where tblWarranty.ShipDate < '01/01/2019' And tblComputersystem.Domainrole < 2
  And tblAssetCustom.State = 1
Group By tblAssets.AssetName,
  tblAssetCustom.Model,
  tsysIPLocations.IPLocation,
  tblAssetCustom.PurchaseDate,
  tblAssetCustom.Warrantydate,
  tblADusers.Displayname,
  tblProcessor.Name,
  tblAssets.Memory,
  Convert(Varchar(10),tblWarrantyDetails.WarrantyStartDate,101),
  Convert(Varchar(10),tblWarrantyDetails.WarrantyEndDate,101),
  tblAssets.AssetID,
  Cast(Cast(tblDiskdrives.Size As BigInt) / 1024 / 1024 / 1024 As Numeric)
Order By tblAssets.AssetName

[ASismey · Accepted solution]

Sorry this one : 

Select Distinct Top (1000000) tblAssets.AssetName,
  tblAssetCustom.Model,
  tsysIPLocations.IPLocation As [Location (Based on IP)],
  tblAssetCustom.PurchaseDate As [Purchase Date],
  tblAssetCustom.Warrantydate As [Warranty Expiration],
  tblADusers.Displayname As [Last Known User],
  tblProcessor.Name,
  tblAssets.Memory,
  Cast(Cast(tblDiskdrives.Size As BigInt) / 1024 / 1024 / 1024 As Numeric) As
  TotalSizeGB,
  tblDiskdrives.Caption
From tblComputersystem
  Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Outer Join tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Inner Join tblWarranty On tblAssets.AssetID = tblWarranty.AssetId
  Inner Join tblWarrantyDetails On tblWarranty.WarrantyId =
      tblWarrantyDetails.WarrantyId
  Inner Join tblADusers On tblADusers.Username = tblAssets.Username
  Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
  Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Where tblDiskdrives.Caption Like 'C%' And tblWarranty.ShipDate < '01/01/2019'
  And tblComputersystem.Domainrole < 2 And tblAssetCustom.State = 1
Group By tblAssets.AssetName,
  tblAssetCustom.Model,
  tsysIPLocations.IPLocation,
  tblAssetCustom.PurchaseDate,
  tblAssetCustom.Warrantydate,
  tblADusers.Displayname,
  tblProcessor.Name,
  tblAssets.Memory,
  Cast(Cast(tblDiskdrives.Size As BigInt) / 1024 / 1024 / 1024 As Numeric),
  Convert(Varchar(10),tblWarrantyDetails.WarrantyStartDate,101),
  Convert(Varchar(10),tblWarrantyDetails.WarrantyEndDate,101),
  tblAssets.AssetID,
  tblDiskdrives.Caption
Order By tblAssets.AssetName

[FayeJ]

You are awesome!  Thanks so much!

[FayeJ]

Anyway to get around this or will it just take manual manipulation of an export of the report?