I need a report for warranty start date (accurate purchase date) but it in more usable format

[Tholmes]

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

[Tholmes]

Amazing, thank you so much
Got it all now
Regards
Tim

[RCorbeil]

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]

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]

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]

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.