Report to group workstations by purchase year

[galesj]

Hello,

I'm trying to create a Chart: report that will group our workstation purchases by purchase year. So far I've come up with this, but the counts are too high. I believe it includes assets besides workstations. What's the easiest way to limit it just to workstations?

Select Top 1000000 Case
When tblAssetCustom.PurchaseDate >= Cast('01-01-2018' As DATETIME)
Then '1. Purchased in 2018'
When tblAssetCustom.PurchaseDate >= Cast('01-01-2017' As DATETIME)
And tblAssetCustom.PurchaseDate <= Cast('12-31-2017' As DATETIME) Then
'2. Purchased in 2017'
When tblAssetCustom.PurchaseDate >= Cast('01-01-2016' As DATETIME)
And tblAssetCustom.PurchaseDate <= Cast('12-31-2016' As DATETIME) Then
'3. Purchased in 2016' Else '4. Purchased in 2015 or before'
End As DateRange,
count(*) As NumberOfAssets
From tblAssetCustom
Group By Case
When tblAssetCustom.PurchaseDate >= Cast('01-01-2018' As DATETIME)
Then '1. Purchased in 2018'
When tblAssetCustom.PurchaseDate >= Cast('01-01-2017' As DATETIME)
And tblAssetCustom.PurchaseDate <= Cast('12-31-2017' As DATETIME) Then
'2. Purchased in 2017'
When tblAssetCustom.PurchaseDate >= Cast('01-01-2016' As DATETIME)
And tblAssetCustom.PurchaseDate <= Cast('12-31-2016' As DATETIME) Then
'3. Purchased in 2016' Else '4. Purchased in 2015 or before' End

Thanks!

[RCorbeil · Accepted solution]

Give this a try.

SELECT Top 1000000
  Year(tblAssetCustom.PurchaseDate) AS PurchaseYear,
  Count(*) AS NumberOfAssets
FROM
  tblAssets
  INNER JOIN tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  INNER JOIN tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
WHERE
  tblAssetCustom.State = 1               -- Active
  AND tblAssets.AssetType = -1           -- Windows
  AND tblComputersystem.Domainrole < 2   -- workstation
GROUP BY
  Year(tblAssetCustom.PurchaseDate)

If you need to lump anything older than 2016, it should be straightforward enough to replace the Year() with a CASE to create the time blocks you want.

[galesj]

Thanks, this is perfect; much better than my approach. Cheers!

[Esben_D]

Use the following where condition:

Where tblComputersystem.Domainrole < 2

Domainrole property is the easiest way to only select Workstations or Servers.

Value Meaning:
0 Standalone Workstation
1 Member Workstation
2 Standalone Server
3 Member Server
4 Backup Domain Controller
5 Primary Domain Controller

[galesj]

This is a handy tip, thank you.