cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
ldockery
Engaged Sweeper II
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
2 REPLIES 2
RCorbeil
Honored Sweeper II
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
Champion Sweeper
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