cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
PBjelly
Engaged Sweeper II
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
1 ACCEPTED SOLUTION
PBjelly
Engaged Sweeper II
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]

View solution in original post

3 REPLIES 3
PBjelly
Engaged Sweeper II
I've got it working for myself.
PBjelly
Engaged Sweeper II
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
Engaged Sweeper II
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]