cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
dijs06
Engaged Sweeper II
Hi,

Is there's a way to generate asset reports in a way that it will show separate reports of assets that are expiring in a quarterly basis?

For example:
1ST QUARTER REPORT - assets that are expiring for the month of January to March
2ND QUARTER REPORT - assets that are expiring for the month of aPRIL to June
3RD QUARTER REPORT - assets that are expiring for the month of July to September
4TH QUARTER REPORT - assets that are expiring for the month of October to December

With this i can create a dashboard in Lansweeper which will show me this report so that it will be easier to determine how many expired machines or asset is set to be replaced in a quarterly basis.

Hope anyone can help me with this. 🙂
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
The following report lists all assets in active state sorted on their warranty expiration date together with the quarter of the expiration date. You could add a Data Report widget to your Dashboard and use this report in order to have an overview.

Select Top 1000000 Cast(DatePart(Year, tblAssetCustom.Warrantydate) As nvarchar)
+ ' ' + 'Q' + Cast(DatePart(Quarter, tblAssetCustom.Warrantydate) As nvarchar)
As [warranty expiration],
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssetCustom.State = 1
Order By tblAssetCustom.Warrantydate

View solution in original post

4 REPLIES 4
dijs06
Engaged Sweeper II
Thank you so much!!
Daniel_B
Lansweeper Alumni
You can do this with a CASE statement.

Select Top 1000000 Case
When DatePart(Month, tblAssetCustom.Warrantydate) >= 10 Then
Cast(DatePart(Year, tblAssetCustom.Warrantydate) + 1 As nvarchar) + ' Q1'
When DatePart(Month, tblAssetCustom.Warrantydate) <
4 Then Cast(DatePart(Year, tblAssetCustom.Warrantydate) As nvarchar) + ' Q2'
When DatePart(Month, tblAssetCustom.Warrantydate) >= 4 And
DatePart(Month, tblAssetCustom.Warrantydate) < 7 Then Cast(DatePart(Year,
tblAssetCustom.Warrantydate) As nvarchar) + ' Q3'
When DatePart(Month, tblAssetCustom.Warrantydate) >= 7 And
DatePart(Month, tblAssetCustom.Warrantydate) < 10 Then Cast(DatePart(Year,
tblAssetCustom.Warrantydate) As nvarchar) + ' Q4'
End As [warranty expiration],
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssetCustom.State = 1
Order By tblAssetCustom.Warrantydate
dijs06
Engaged Sweeper II
Thanks Daniel! Really helps me..:)

I Just have another question though! Is there's a way for me to change or modify the quarterly report in the sense that the month range is different or does not follow the normal range for Q1, Q2, Q3 and Q4.


For example:


1ST QUARTER REPORT - assets that are expiring for the month of October to December
2ND QUARTER REPORT - assets that are expiring for the month of January to March
3RD QUARTER REPORT - assets that are expiring for the month of APRIL to June
4TH QUARTER REPORT - assets that are expiring for the month of July to September


Daniel_B
Lansweeper Alumni
The following report lists all assets in active state sorted on their warranty expiration date together with the quarter of the expiration date. You could add a Data Report widget to your Dashboard and use this report in order to have an overview.

Select Top 1000000 Cast(DatePart(Year, tblAssetCustom.Warrantydate) As nvarchar)
+ ' ' + 'Q' + Cast(DatePart(Quarter, tblAssetCustom.Warrantydate) As nvarchar)
As [warranty expiration],
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssetCustom.State = 1
Order By tblAssetCustom.Warrantydate

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now