Device Warranty Report Help

[sktf]

Hey everyone,

Requesting some help with a report which can display the counts of our device warranties in a summary:

Out of Warranty | Count Amount

1 Year Warranty Remaining | Count Amount

2 Year Warranty Remaining | Count Amount

3 Year Warranty Remaining | Count Amount

Appreciate some help as the intention is for this to be converted into a chart for a pie chart widget.

Thanks in advance 🙂

[sktf]

Hi ASismey,

Thank you for your response, that code is perfect, i managed to get the data i needed to prior to your response using the query below, really appreciate your response its much appreciated.

Select Distinct Top (1000000) Year(tblAssetCustom.Warrantydate) As
  WarrantyExpiry,
  Count(tblAssets.AssetID) As Total
From tblAssetCustom
  Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Outer Join tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
Where tblAssetCustom.Warrantydate > GetDate() And tblComputersystem.Domainrole <
  2 And tblAssetCustom.State = 1
Group By Year(tblAssetCustom.Warrantydate)
Order By WarrantyExpiry

[ASismey · Accepted solution]

Hi,

This should get you started :

Select Top (1000000) Case
    When DatePart(yyyy, tblAssetCustom.Warrantydate) Is Not Null Then
      Concat(DatePart(yyyy, tblAssetCustom.Warrantydate) - DatePart(yyyy,
      GetDate()), ' :Years Warranty Remaining')
  End As Title,
  Count(tblAssets.AssetName) As [Count Of Assets]
From tblAssetCustom
  Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Outer Join tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
Group By Case
    When DatePart(yyyy, tblAssetCustom.Warrantydate) Is Not Null Then
      Concat(DatePart(yyyy, tblAssetCustom.Warrantydate) - DatePart(yyyy,
      GetDate()), ' :Years Warranty Remaining')
  End
Order By [Count Of Assets]