Modifying Purchase Date / Yearly Refresh Report

[mhammond]

Using resources provided here, a couple years ago I was able to create a report that would assist in a 5 year refresh program based off of Purchase Date (+5 years: so if this is 2021, and asset purchased in 2016 or earlier will ONLY show in the list). The report is/was immaculate. I recently moved to a NEW company and am trying to modify that same report to base everything off of Warranty Date.

Where I've moved to, they only use Lenovo and are phasing out their Dells and HPs (ironically the manufacturer's that correctly report purchase dates!). The new refresh plan, given the age of the hardware here, is to base everything off of +2 years from the Warranty End date. That is the only thing reporting properly. Then, once everything is base-lined, we can start reducing our needs for extended warranties, stick to the 3 year and reduce the refresh down to +1 beyond warranty date.

I've included the original code that worked for Purchase Date, then following included the edited code to adjust for Warranty Date. For some reason, everything beyond the current year (i.e. 2021) is showing up; where I want to only have assets where their warranty year +2 = 2021 or older (2020, 2019, 2018, etc.)

I've gone over the code 100s of times and can't figure out what I'm missing.

Apologies for the sloppy code!

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssetCustom.PurchaseDate As [Purchase Date],
Year(tblAssetCustom.PurchaseDate) As [Purchase Year],
Year(tblAssetCustom.PurchaseDate) + 5 As [Q Refresh],
tblAssetCustom.Model,
tblAssets.Memory,
tblAssets.Description,
Case
When tblAssetCustom.Manufacturer Like 'Microsoft%' And
tblAssetCustom.Model Like 'Surface%' Then 'Tablet'
When tblPortableBattery.Name Is Not Null Then 'Laptop'
Else TsysChassisTypes.ChassisName
End As Chassis,
tblAssets.AssetName,
tsysIPLocations.IPLocation,
tblAssetCustom.Warrantydate As [Warranty Expiration],
tblAssetCustom.Manufacturer
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
Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
Left Join (Select Distinct tblSystemEnclosure.AssetID,
tblSystemEnclosure.ChassisTypes
From tblSystemEnclosure
Where tblSystemEnclosure.ChassisTypes <> 12) As Enclosure On
Enclosure.AssetID = tblAssets.AssetID
Left Join TsysChassisTypes On Enclosure.ChassisTypes =
TsysChassisTypes.Chassistype
Where ((Year(tblAssetCustom.PurchaseDate) + 5) <= Year(GetDate()) Or
tblAssetCustom.PurchaseDate Is Null) And tsysAssetTypes.AssetTypename In
('Windows') And tblAssetCustom.State = 1 And tblAssets.Assettype <> 66
Except
Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssetCustom.PurchaseDate As [Purchase Date],
Year(tblAssetCustom.PurchaseDate) As [Purchase Year],
Year(tblAssetCustom.PurchaseDate) + 5 As [Q Refresh],
tblAssetCustom.Model,
tblAssets.Memory,
tblAssets.Description,
Case
When tblAssetCustom.Manufacturer Like 'Microsoft%' And
tblAssetCustom.Model Like 'Surface%' Then 'Tablet'
When tblPortableBattery.Name Is Not Null Then 'Laptop'
Else TsysChassisTypes.ChassisName
End As Chassis,
tblAssets.AssetName,
tsysIPLocations.IPLocation,
tblAssetCustom.Warrantydate As [Warranty Expiration],
tblAssetCustom.Manufacturer
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
Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
Left Join (Select Distinct tblSystemEnclosure.AssetID,
tblSystemEnclosure.ChassisTypes
From tblSystemEnclosure
Where tblSystemEnclosure.ChassisTypes <> 12) As Enclosure On
Enclosure.AssetID = tblAssets.AssetID
Left Join TsysChassisTypes On Enclosure.ChassisTypes =
TsysChassisTypes.Chassistype
Where tblAssetCustom.Custom19 = 'Yes' And ((Year(tblAssetCustom.PurchaseDate) +
5) <= Year(GetDate()) Or tblAssetCustom.PurchaseDate Is Null) And
tsysAssetTypes.AssetTypename In ('Windows') And tblAssetCustom.State = 1 And
tblAssets.Assettype <> 66
Order By [Q Refresh] Desc

----------------------
And here is the modified code:

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssetCustom.PurchaseDate As [Purchase Date],
Year(tblAssetCustom.Warrantydate) As [EOW Year],
Year(tblAssetCustom.Warrantydate) + 2 As [AMI Refresh],
tblAssetCustom.Model,
tblAssets.Memory,
tblAssets.Description,
Case
When tblAssetCustom.Manufacturer Like 'Microsoft%' And
tblAssetCustom.Model Like 'Surface%' Then 'Tablet'
When tblPortableBattery.Name Is Not Null Then 'Laptop'
Else TsysChassisTypes.ChassisName
End As Chassis,
tblAssets.AssetName,
tsysIPLocations.IPLocation,
tblAssetCustom.Warrantydate As [Warranty Expiration],
tblAssetCustom.Manufacturer
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
Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
Left Join (Select Distinct tblSystemEnclosure.AssetID,
tblSystemEnclosure.ChassisTypes
From tblSystemEnclosure
Where tblSystemEnclosure.ChassisTypes <> 12) As Enclosure On
Enclosure.AssetID = tblAssets.AssetID
Left Join TsysChassisTypes On Enclosure.ChassisTypes =
TsysChassisTypes.Chassistype
Where ((Year(tblAssetCustom.Warrantydate) + 2) <= Year(GetDate()) Or
tblAssetCustom.PurchaseDate Is Null) And tsysAssetTypes.AssetTypename In
('Windows') And tblAssetCustom.State = 1 And tblAssets.Assettype <> 66
Except
Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssetCustom.PurchaseDate As [Purchase Date],
Year(tblAssetCustom.Warrantydate) As [EOW Year],
Year(tblAssetCustom.Warrantydate) + 2 As [AMI Refresh],
tblAssetCustom.Model,
tblAssets.Memory,
tblAssets.Description,
Case
When tblAssetCustom.Manufacturer Like 'Microsoft%' And
tblAssetCustom.Model Like 'Surface%' Then 'Tablet'
When tblPortableBattery.Name Is Not Null Then 'Laptop'
Else TsysChassisTypes.ChassisName
End As Chassis,
tblAssets.AssetName,
tsysIPLocations.IPLocation,
tblAssetCustom.Warrantydate As [Warranty Expiration],
tblAssetCustom.Manufacturer
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
Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
Left Join (Select Distinct tblSystemEnclosure.AssetID,
tblSystemEnclosure.ChassisTypes
From tblSystemEnclosure
Where tblSystemEnclosure.ChassisTypes <> 12) As Enclosure On
Enclosure.AssetID = tblAssets.AssetID
Left Join TsysChassisTypes On Enclosure.ChassisTypes =
TsysChassisTypes.Chassistype
Where tblAssetCustom.Custom10 = 'Yes' And ((Year(tblAssetCustom.Warrantydate) +
2) <= Year(GetDate()) Or tblAssetCustom.PurchaseDate Is Null) And
tsysAssetTypes.AssetTypename In ('Windows') And tblAssetCustom.State = 1 And
tblAssets.Assettype <> 66
Order By [AMI Refresh] Desc

[mhammond]

So I was able to remove the exception code from the report that 'ignored' anything "Microsoft", "Surface" and "Tablet".

The report count did not change.

Upon further review, I found that our Surfaces report neither Purchase Date, nor Warranty Expiration Date - both are empty/null.

So, outside of manually tracking down the documentation per purchased unit and inputting it into the LS asset for Surfaces, there's nothing I can do to get them to show up for any date-related fields.

All that's left is to try and get the OS name/field to show up on the report. I manually linked tsysOS to tblAssets. I feel I have tried every combination of linking OScode of tblAssets to either OScode or OSname of tsysOS. I keep getting this error:

"All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists."

Here is the "alleged" modified code:

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssetCustom.PurchaseDate As [Purchase Date],
Year(tblAssetCustom.Warrantydate) As [EOW Year],
Year(tblAssetCustom.Warrantydate) + 2 As [AMI Refresh],
tblAssetCustom.Model,
tblAssets.Memory,
tblAssets.Description,
tblAssets.AssetName,
tsysIPLocations.IPLocation,
tblAssetCustom.Warrantydate As [Warranty Expiration],
tblAssetCustom.Manufacturer,
tblAssets.OScode,
tsysOS.OSname As OS
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
Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
Left Join (Select Distinct tblSystemEnclosure.AssetID,
tblSystemEnclosure.ChassisTypes
From tblSystemEnclosure
Where tblSystemEnclosure.ChassisTypes <> 12) As Enclosure On
Enclosure.AssetID = tblAssets.AssetID
Left Join TsysChassisTypes On Enclosure.ChassisTypes =
TsysChassisTypes.Chassistype
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode And tblAssets.OScode =
tsysOS.OSname
Where (Year(tblAssetCustom.Warrantydate) + 2) <= Year(GetDate()) And
tsysAssetTypes.AssetTypename In ('Windows') And tblAssetCustom.State = 1 And
tblAssets.Assettype <> 66
Except
Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssetCustom.PurchaseDate As [Purchase Date],
Year(tblAssetCustom.Warrantydate) As [EOW Year],
Year(tblAssetCustom.Warrantydate) + 2 As [AMI Refresh],
tblAssetCustom.Model,
tblAssets.Memory,
tblAssets.Description,
tblAssets.AssetName,
tsysIPLocations.IPLocation,
tblAssetCustom.Warrantydate As [Warranty Expiration],
tblAssetCustom.Manufacturer,
tblAssets.OScode,
tsysOS.OSname As OS
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
Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
Left Join (Select Distinct tblSystemEnclosure.AssetID,
tblSystemEnclosure.ChassisTypes
From tblSystemEnclosure
Where tblSystemEnclosure.ChassisTypes <> 12) As Enclosure On
Enclosure.AssetID = tblAssets.AssetID
Left Join TsysChassisTypes On Enclosure.ChassisTypes =
TsysChassisTypes.Chassistype
Where tblAssetCustom.Custom10 = 'Yes' And ((Year(tblAssetCustom.Warrantydate) +
2) <= Year(GetDate()) Or tblAssetCustom.PurchaseDate Is Null) And
tsysAssetTypes.AssetTypename In ('Windows') And tblAssetCustom.State = 1 And
tblAssets.Assettype <> 66
Order By [AMI Refresh]

[mhammond]

So I've gotten a bit closer, but now I need to add in the OS (like add column tsysOS.OSname As OS) and remove the code the ignores "Surface", "Microsoft" and "Tablet" since our environment relies heavily on Surface Tablets for a lot of our manufacturing and distribution.

I think the fault lies in my innerjoin statements, but I can't be sure. I can't simply can't add the tsysOS.OSname As OS line as I get a "multi-part" SQL error. I don't know which code to strip for the "Surface" exclusions either.

The original coding was built around Purchase Date. However Lenovo does not report that information correctly like HP and Dell and I had to modify based on Warranty Expiration Date (year).

Below is the updated code that utilizes Warranty Date.

Any help is appreciated.

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssetCustom.PurchaseDate As [Purchase Date],
Year(tblAssetCustom.Warrantydate) As [EOW Year],
Year(tblAssetCustom.Warrantydate) + 2 As [AMI Refresh],
tblAssetCustom.Model,
tblAssets.Memory,
tblAssets.Description,
Case
When tblAssetCustom.Manufacturer Like 'Microsoft%' And
tblAssetCustom.Model Like 'Surface%' Then 'Tablet'
When tblPortableBattery.Name Is Not Null Then 'Laptop'
Else TsysChassisTypes.ChassisName
End As Chassis,
tblAssets.AssetName,
tsysIPLocations.IPLocation,
tblAssetCustom.Warrantydate As [Warranty Expiration],
tblAssetCustom.Manufacturer
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
Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
Left Join (Select Distinct tblSystemEnclosure.AssetID,
tblSystemEnclosure.ChassisTypes
From tblSystemEnclosure
Where tblSystemEnclosure.ChassisTypes <> 12) As Enclosure On
Enclosure.AssetID = tblAssets.AssetID
Left Join TsysChassisTypes On Enclosure.ChassisTypes =
TsysChassisTypes.Chassistype,
tsysOS
Where (Year(tblAssetCustom.Warrantydate) + 2) <= Year(GetDate()) And
tsysAssetTypes.AssetTypename In ('Windows') And tblAssetCustom.State = 1 And
tblAssets.Assettype <> 66
Except
Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssetCustom.PurchaseDate As [Purchase Date],
Year(tblAssetCustom.Warrantydate) As [EOW Year],
Year(tblAssetCustom.Warrantydate) + 2 As [AMI Refresh],
tblAssetCustom.Model,
tblAssets.Memory,
tblAssets.Description,
Case
When tblAssetCustom.Manufacturer Like 'Microsoft%' And
tblAssetCustom.Model Like 'Surface%' Then 'Tablet'
When tblPortableBattery.Name Is Not Null Then 'Laptop'
Else TsysChassisTypes.ChassisName
End As Chassis,
tblAssets.AssetName,
tsysIPLocations.IPLocation,
tblAssetCustom.Warrantydate As [Warranty Expiration],
tblAssetCustom.Manufacturer
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
Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
Left Join (Select Distinct tblSystemEnclosure.AssetID,
tblSystemEnclosure.ChassisTypes
From tblSystemEnclosure
Where tblSystemEnclosure.ChassisTypes <> 12) As Enclosure On
Enclosure.AssetID = tblAssets.AssetID
Left Join TsysChassisTypes On Enclosure.ChassisTypes =
TsysChassisTypes.Chassistype
Where tblAssetCustom.Custom10 = 'Yes' And ((Year(tblAssetCustom.Warrantydate) +
2) <= Year(GetDate()) Or tblAssetCustom.PurchaseDate Is Null) And
tsysAssetTypes.AssetTypename In ('Windows') And tblAssetCustom.State = 1 And
tblAssets.Assettype <> 66
Order By [AMI Refresh]