Report returns two assets per asset instead of one

[ldockery]

The report I want is to return one asset per line, and to include both its bios age and its warranty age in that line. I have accomplished this by modifying the bios age report and adding warranty age, but now I am getting duplicate asset lines. How would I modify this to return only one asset per line?

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
Convert(nVARCHAR(10),tblBIOS.ReleaseDate,101) As [BIOS Date],
Cast(Round(DateDiff(day, tblBIOS.ReleaseDate, GetDate()) / 365.00,
2) As Numeric(8,2)) As [Years Old],
Cast(Round(DateDiff(day, tblWarrantyDetails.WarrantyStartDate, GetDate()) /
365.00, 2) As Numeric(8,2)) As [Warranty Years Old],
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblWarrantyDetails.WarrantyStartDate
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
Inner Join tblWarranty On tblAssets.AssetID = tblWarranty.AssetId
Inner Join tblWarrantyDetails On tblWarranty.WarrantyId =
tblWarrantyDetails.WarrantyId
Where tblState.Statename = 'Active'
Order By [Warranty Years Old] Desc

[RCorbeil]

I got wondering about the "why" of it. Based on what I'm seeing in my inventory, the doubled output is a result of multiple warranty details per asset.

e.g. an HP asset has two warranty details:

• Wty: HP HW Maintenance Onsite Support
  
• Wty: HP Support for Initial Setup

a Dell asset has two warranty details:

• Onsite Service After Remote Diagnosis ...
  
• Dell Digital Delivery

a Dell asset with three warranty details, each with start and end dates different from the other two:

• Onsite Service After Remote Diagnosis ...
  
• Dell Digital Delivery
  
• Onsite Service After Remote Diagnosis ...

Unfortunately, I don't think the "Select Distinct" suggestion is going to do the trick.

Since you appear to be looking for the earliest warranty start date for each asset, you might try something like this:

Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  Convert(nVARCHAR(10),tblBIOS.ReleaseDate,101) As [BIOS Date],
  Cast(Round(DateDiff(day, tblBIOS.ReleaseDate, GetDate()) / 365.00, 2) As Numeric(8,2)) As [Years Old],
  Cast(Round(DateDiff(day, w.WarrantyStartDate, GetDate()) / 365.00, 2) As Numeric(8,2)) As [Warranty Years Old],
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  w.WarrantyStartDate
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
  Inner Join tblWarranty On tblAssets.AssetID = tblWarranty.AssetId
  Left Join ( SELECT
                WarrantyID,
                Min(WarrantyStartDate) AS WarrantyStartDate
              FROM
                tblWarrantyDetails
              GROUP BY
                WarrantyId) AS w ON w.WarrantyID = tblWarranty.WarrantyId
Where
  tblState.Statename = 'Active'
Order By
  [Warranty Years Old] Desc

[KrisNelson]

I can't tell you where it's coming up with unique values to create the double lines (maybe if I look into it more I could find it), but I can tell you if you make this change it will work.

Select Top 1000000 tblAssets.AssetID

to

Select Distinct Top 1000000 tblAssets.AssetID

-Kris