Desktops over 5 years old report

[LiquidKaoz]

Trying to get desktops over 5 years old based off of the purchase date. Here's what I have so far... I'm new to SQL, so please forgive and inefficiencies. It doesn't seem to grab everything...am I missing something?


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer,
tblAssetCustom.Serialnumber,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate
From tblAssets
Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblSystemEnclosure
On tblAssets.AssetID = tblSystemEnclosure.AssetID
Where (tblAssetCustom.PurchaseDate < GetDate() - 1825 And
tblSystemEnclosure.ChassisTypes = 3) Or
(tblAssetCustom.PurchaseDate < GetDate() - 1825 And
tblSystemEnclosure.ChassisTypes = 4) Or
(tblAssetCustom.PurchaseDate < GetDate() - 1825 And
tblSystemEnclosure.ChassisTypes = 6) Or
(tblAssetCustom.PurchaseDate < GetDate() - 1825 And
tblSystemEnclosure.ChassisTypes = 7) Or
(tblAssetCustom.PurchaseDate < GetDate() - 1825 And
tblSystemEnclosure.ChassisTypes = 13) Or
(tblAssetCustom.PurchaseDate < GetDate() - 1825 And
tblSystemEnclosure.ChassisTypes = 15) Or
(tblAssetCustom.PurchaseDate < GetDate() - 1825 And
tblSystemEnclosure.ChassisTypes = 16)
Order By tblAssetCustom.PurchaseDate

[LiquidKaoz]

It's still showing some laptops, but not a whole ton that I can't parse through. Thanks so much for that!

[Daniel_B · Accepted solution]

If you want to be sure that all computers are being picked up, it might be more safe to list all computers which are not servers, have no entry in tblPortableBattery and which are no virtual machines. The rest should be all desktops. Give the following report a try:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssetCustom.Model,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Serialnumber,
  tblAssetCustom.PurchaseDate,
  tblAssetCustom.Warrantydate
From tblAssets
  Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblSystemEnclosure
    On tblAssets.AssetID = tblSystemEnclosure.AssetID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
Where tblAssetCustom.Model Not Like '%virtual%' And
  tblAssetCustom.Serialnumber Not Like '%virtual%' And
  (tblAssetCustom.PurchaseDate < GetDate() - 365 Or
    Coalesce(tblAssetCustom.PurchaseDate, '1900-01-01') = '1900-01-01') And
  tblComputersystem.Domainrole < 2 And Coalesce(tblPortableBattery.AssetID, 0) =
  0
Order By tblAssetCustom.PurchaseDate