Warranty end date

[esr]

On each asset page is, of course, the warranty end date.

Our Dell PC's have 3 or 4 parts to the warranty, each with it's own distinct end date.

Which field do you call for the single entry on the asset page so we can duplicate that single piece of data in a report, versus having each asset listed in the report 3 or 4 times with each distinct end date?

Our goal is simply to add the warranty end date to a report which already includes other details like purchase date etc. When I add it now I get each asset listed multiple times for each end date, rather than just once to match what's on the asset page.

Thanks!

[esr]

Perfect-

tblAssetCustom.Warrantydate was exactly what was needed in this case. When combined with a quick bit of formatting to drop the time from the result and we have the data Management was interested in adding to our reports-

Convert(nvarchar(10),tblAssetCustom.Warrantydate,101) As [Warranty End Date]

Thanks so much!

[Daniel_B · Accepted solution]

You could simply add tblAssetCustom.Warrantydate to your report which lists the last warranty end date. All full warranty products will be considered and the latest date will be written into tblAssetCustom.Warrantydate. So you can remove table tblWarrantyDetails from your report. Alternatively report on the warranty product with the latest end date in a report like to following example:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tWarrantyDetails.WarrantyEndDate,
  tWarrantyDetails.ServiceType
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join (Select tblWarranty.AssetId,
    Max(tblWarrantyDetails.WarrantyEndDate) As [max warranty end date]
  From tblWarranty
    Inner Join tblWarrantyDetails On tblWarranty.WarrantyId =
      tblWarrantyDetails.WarrantyId
  Group By tblWarranty.AssetId) tWarrantyEnd On tWarrantyEnd.AssetId =
    tblAssets.AssetID
  Inner Join (Select tblWarrantyDetails.ServiceType,
    tblWarranty.AssetId,
    tblWarrantyDetails.WarrantyEndDate
  From tblWarrantyDetails
    Inner Join tblWarranty On tblWarranty.WarrantyId =
      tblWarrantyDetails.WarrantyId) tWarrantyDetails On tblAssets.AssetID =
    tWarrantyDetails.AssetId And tWarrantyEnd.[max warranty end date] =
    tWarrantyDetails.WarrantyEndDate
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName