→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
galesj
Engaged Sweeper II
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!
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
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.

View solution in original post

4 REPLIES 4
RCorbeil
Honored Sweeper II
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
Engaged Sweeper II
Thanks, this is perfect; much better than my approach. Cheers!
Esben_D
Lansweeper Employee
Lansweeper Employee
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
Engaged Sweeper II
This is a handy tip, thank you.