I am having a tough time trying to manipulate the reports of Asset Out of Warranty and Asset Out of Warranty in 60 Days. We want to not only keep the manufacturer's report of when that warranty expires, but we also have set a standard where 5 years after the Purchase Date is when we will perform refreshes for that year; we are trying to create a "Refresh Report" for our systems based on that Purchase Date.
So, for example: a Dell Latitude notebook has a manufacturer's warranty expiring March 20th, 2020, as it was purchased March 21st, 2017. However, we've set a standard that we will still support the hardware until 5 years after purchase date, allowing for a standard refresh schedule. So, for us, when that machine actually needs replaced (barring any issues with broken screens, bad motherboard, etc.), the notebook would be replaced in 2022.
I'm at a loss as to where to make the calculation on the PurchaseDate field. I feel it's something REALLY simple and I'm over-complicating it somehow.
Below is the code for the Asset Out of Warranty report (I didn't include the '60 day' code, as it appears the same, minus the extra line for a 60 day timeline):
Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Serialnumber,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Expiration],
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tsysAssetTypes.AssetTypename As Type,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Where tblAssetCustom.Warrantydate < GetDate() And tblAssetCustom.State = 1 And
tblAssets.Assettype <> 66
Order By [Warranty Expiration] Desc