Report: Computers Over 5 Years Old

[lance321]

I am trying to work on a report to show computers greater than 5 years old and am having problems getting it to work correctly.
Thank you for any assistance.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssetCustom1.Model,
  tblADComputers.Description As [A.D. Description],
  tblADusers.Displayname,
  tblAssetCustom.PurchaseDate
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Inner Join tblAssetCustom tblAssetCustom1 On tblAssets.AssetID =
    tblAssetCustom1.AssetID
  Inner Join tblADusers On tblADusers.Username = tblAssets.Username
Where tblAssetCustom.PurchaseDate > DateAdd(YEAR, -5, GetDate()) And
  tblAssetCustom.State = 1

[borgoncete88]

Hi!! That query works fine, but in my situation I only get the branded computers like HP, DELL, Toshiba, etc. For custom computers without brand doesn't show any result. Is there any other filed on the query I can use like bios or motherboard to have a similar results for all the computers?? Thank you in advance.

[lance321]

I should have looked closer. It was easy to change it.

Where 60 < DateDiff(MONTH, tblAssetCustom.PurchaseDate, GetDate())

Thanks again for your help!

[lance321]

Thank you for your help. This is definitely closer that I was, it is showing assets in 2009 and back but not taking the month or day into account. I have assets from 04/2010 for example that are older than 5 years from today's date but not showing on the report. Is there a way to account for this?

[Daniel_B · Accepted solution]

The DateDiff function may work better than DateAdd. Please find a modified report below:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssetCustom.Model,
  tblADComputers.Description As [A.D. Description],
  tblADusers.Displayname,
  tblAssetCustom.PurchaseDate
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Left Join tblADusers On tblADusers.Username = tblAssets.Username And
    tblAssets.Userdomain = tblADusers.Userdomain
Where 5 < DateDiff(YEAR, tblAssetCustom.PurchaseDate, GetDate()) And
  tblAssetCustom.State = 1