ā07-12-2023 04:06 PM
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
Solved! Go to Solution.
ā07-14-2023 05:07 AM
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.
ā07-13-2023 07:45 PM - edited ā07-13-2023 09:23 PM
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
ā07-14-2023 05:07 AM
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.
ā07-18-2023 12:51 AM
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
ā
ā07-15-2023 12:26 AM
That sucks they have no way to remove duplicates entrees, it funny that only some of the assets do that and not others
ā07-13-2023 10:13 AM
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
ā07-12-2023 10:23 PM
Try replacing you first line with this: Select Distinct Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon. That should take care of the duplicate issue.
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now