cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
ProfessorNerdly
Engaged Sweeper II
I have a report that I made to identify workstations that are reaching the 5 year old mark (our refresh window), but I am struggling to modify the report to allow me to forecast for future years. As it stands right now, in order to see next year's I needed to make the purchase date query for devices that are 4 years old or older.

I would like to modify the SQL query to show me purchase dates between 1/1/20xx to 12/31/20xx. So I can just print or export that report and give it to management or send it to my vendor for pricing thus allowing us to budget for the department more effectively.

I have attached the Query I am currently using below.


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssetCustom.PurchaseDate
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tsysAssetTypes.AssetTypename <> 'Printer' And
tblAssetCustom.PurchaseDate < GetDate() - 1460 And tblAssetCustom.State = 1
Order By tblAssetCustom.PurchaseDate Desc
1 ACCEPTED SOLUTION
MikeMc
Champion Sweeper II
This should be a good starting point. You can change the dates on lines 13 and 14.
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssetCustom.PurchaseDate
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tsysAssetTypes.AssetTypename <> 'Printer' And
tblAssetCustom.PurchaseDate >= Cast('01-01-2011' As DATETIME) And
tblAssetCustom.PurchaseDate =< Cast('12-31-2011' As DATETIME) And
tblAssetCustom.State = 1
Order By tblAssetCustom.PurchaseDate Desc

View solution in original post

2 REPLIES 2
ProfessorNerdly
Engaged Sweeper II
PERFECT! Exactly what I needed. Just one small issue, line 14 has the = and < transposed. Easy catch by Report Ediitor!

Thanks.
MikeMc
Champion Sweeper II
This should be a good starting point. You can change the dates on lines 13 and 14.
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssetCustom.PurchaseDate
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tsysAssetTypes.AssetTypename <> 'Printer' And
tblAssetCustom.PurchaseDate >= Cast('01-01-2011' As DATETIME) And
tblAssetCustom.PurchaseDate =< Cast('12-31-2011' As DATETIME) And
tblAssetCustom.State = 1
Order By tblAssetCustom.PurchaseDate Desc