
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎09-11-2018 10:55 PM
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
Labels:
- Labels:
-
Report Center
2 REPLIES 2
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎09-13-2018 07:54 PM
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:
Since you appear to be looking for the earliest warranty start date for each asset, you might try something like this:
e.g. an HP asset has two warranty details:
- Wty: HP HW Maintenance Onsite Support
- Wty: HP Support for Initial Setup
- Onsite Service After Remote Diagnosis ...
- Dell Digital Delivery
- Onsite Service After Remote Diagnosis ...
- Dell Digital Delivery
- Onsite Service After Remote Diagnosis ...
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

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎09-11-2018 11:41 PM
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
Select Top 1000000 tblAssets.AssetID
to
Select Distinct Top 1000000 tblAssets.AssetID
-Kris
