Report for Hardware Maintenance

[PBjelly]

Hey everyone,
I'm quiet new to SQL and my company wants to add a maintenance report to our lansweeper.
I got it figuered out, how to make the report and also adding colors depending on how many days left until the maintenance of one device runs out (green >60 days left, orange >30 days left, red <30 days left).
Now I want to add a column where it actually tells, how many days left until next maintenance, but I'm not sure how to do so...
And I have another question: Is it possible to also add a report, when the days left is under 30 days, so it sends out a reminder?

I hope someone can help me out with this issue.
Here is, what I have got so far:

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
Convert(datetime,tblAssetCustom.Custom1,120) As [Last maintenance],
Convert(datetime,tblAssetCustom.Custom2,120) As [Maintenance expired],
tblAssetCustom.Serialnumber,
Convert(nvarchar,tblAssetCustom.PurchaseDate,103) As [Purchase date],
Convert(nvarchar,tblAssetCustom.Warrantydate,103) As [Warranty expiration],
tsysAssetTypes.AssetTypename As Type,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tblAssets.Lastseen,
Case
When tblAssetCustom.Custom2 < GetDate() + 30 Then '#ffadad'
When tblAssetCustom.Custom2 < GetDate() + 60 Then '#ffe662'
Else '#d4f4be'
End As backgroundcolor
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Where tblAssetCustom.Custom1 < GetDate() + 120 And tblAssetCustom.Custom1 >
GetDate() - 120
Order By [Maintenance expired] Desc

[PBjelly]

I've got it working for myself.

[PBjelly]

PBjelly wrote:
I've got it working for myself.

EDIT: I have to re-open this post, because there is one major issue with this report:
Because of this line: "Where tblAssetCustom.Custom1 > GetDate() - 120" nearly at the end, the report will only show up hardware where the maintenance date isn't older than 120 days.

This is a realy aggressive limit, as there is a maintenance schedule every 2 or 3 years. So hardware, 120 days past the "GetDate()" will no longer show up in the list. Does anyone has an idea how to change this?
I can't get past the 120 days, as this will result in an conversion error.

[PBjelly · Accepted solution]

PBjelly wrote:

PBjelly wrote:
I've got it working for myself.

EDIT: I have to re-open this post, because there is one major issue with this report:
Because of this line: "Where tblAssetCustom.Custom1 > GetDate() - 120" nearly at the end, the report will only show up hardware where the maintenance date isn't older than 120 days.

This is a realy aggressive limit, as there is a maintenance schedule every 2 or 3 years. So hardware, 120 days past the "GetDate()" will no longer show up in the list. Does anyone has an idea how to change this?
I can't get past the 120 days, as this will result in an conversion error.

It was just a little adjustment and the problem was solved.
For everyone who's interested in such kind of report. Here is the final code:

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssetCustom.Serialnumber,
  Convert(datetime,tblAssetCustom.Custom2) As [Last Maintenance],
  Convert(datetime,tblAssetCustom.Custom1) As [Maintenance expired],
  Case
    When GetDate() < tblAssetCustom.Custom1 Then Cast(DateDiff(DAY, GetDate(),
      tblAssetCustom.Custom1) As nvarchar) + ' days'
    When GetDate() >= 0 Then 'expired'
  End As [Days left],
  tsysAssetTypes.AssetTypename As Type,
  tblAssets.IPAddress,
  tblAssets.Description,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Location,
  tsysIPLocations.IPLocation,
  tblAssets.Lastseen,
  Case
    When tblAssetCustom.Custom1 < GetDate() + 30 Then '#ffadad'
    When tblAssetCustom.Custom1 < GetDate() + 60 Then '#ffe662'
    Else '#d4f4be'
  End As backgroundcolor
From tblAssetCustom
  Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Where tblAssetCustom.Custom1 < GetDate() + 1000000 And tblAssetCustom.Custom1 >
  GetDate() - 120 And tblAssetCustom.State = 1
Order By [Maintenance expired]