Generating expiring assets in quarterly basis

[dijs06]

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. 🙂

[dijs06]

Thank you so much!!

[Daniel_B]

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]

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 · Accepted solution]

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