cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Tholmes
Engaged Sweeper III
I found this report which is how I want it to look.
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
Order By [Q Refresh]

I want the report to show the warranty start date, which is an accurate age for our environment, and also what it is, laptop, notebook,portable or surface, in the above format.
I found this report but this shows everything, I don't need the C,NBD ONSITE etc, I just need to see the equipment on its own.

Select Top 1000000 tsysAssetTypes.AssetTypeIcon16 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblWarranty.PurchaseCountry,
tblWarranty.ShipDate,
tblWarrantyDetails.WarrantyStartDate,
tblWarrantyDetails.WarrantyEndDate,
tblWarrantyDetails.ServiceType,
Case
When GetDate() > tblWarrantyDetails.WarrantyEndDate Then 'yes'
Else 'no'
End As Expired,
tblWarranty.Error,
tblAssets.Domain,
tsysAssetTypes.AssetTypename As Type,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tblAssets.Firstseen,
tblAssets.Lastseen
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
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Where Not (tblWarranty.LastWarrantySuccess Is Null)
Order By tblAssets.AssetName,
tblWarrantyDetails.WarrantyEndDate Desc

Above is the report that has the useful warranty data that I need. (just start date tho)
I am so sorry for the long copy and paste of the code, but I need the best of these two reports.
So basically, I want a report that shows just laptops, portable devices and tablets, warranty start date.
Thanks in advance and feel free to berate me for the copy and pasting of so much code.
Tim
5 REPLIES 5
Tholmes
Engaged Sweeper III
Amazing, thank you so much
Got it all now
Regards
Tim
RCorbeil
Honored Sweeper II
The code you offered up as a starting point included two requirements:
  • List machines more than 5y old
  • List machines with no recorded purchase date, as age can't be determined
This can be found at the start of the WHERE clause:
  ((Year(tblAssetCustom.PurchaseDate) + 5) <= Year(GetDate())
Or tblAssetCustom.PurchaseDate Is Null)

If you're looking for machines with warranty expiry in 2024, odds are they're less than 5y old, thus they're being filtered out. If you want to list everything, remove the code above from your query.
RCorbeil
Honored Sweeper II
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,
w.WarrantyStartDate,
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
Left 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
((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]
Tholmes
Engaged Sweeper III
Thanks so much for the code, I have been off sick, sorry for the late reply.
It displays everything up until 2018, is there anyway to update this for the latest, as we have laptops with warranties until 2024.
Thanks again
Tim
RCorbeil
Honored Sweeper II
The issue of multiple warranty details and only wanting the earliest record showed up a couple of years ago. You should be able to use the same approach for your report.