cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
mrdisco
Engaged Sweeper II
I have a report that I'm using to list devices in a certain Asset Group. I'm wanting to highlight devices that are older than 5 years to help managers visually see what needs budgeted to be replaced.


In the report below, I'm using a string I found to take the Purchase Date, add 5 years, and then display that as a number.
Cast(DateDiff(dd, tblAssetCustom.PurchaseDate, GetDate()) / 365.2425 As numeric(8,1)) As Age

I would love for that number to be displayed in red, if the date is older than today.




I've also seen this line used:
Case When tblAssetCustom.Warrantydate < GetDate() Then 'red' End As foregroundcolor,


If I could take the tablAssetCustom.PurchaseDate + 5 years, IF older than today, make it red. That also works. Thanks in advance!



Select Top 1000000 tblAssets.AssetName,
tblADComputers.Description,
tsysAssetTypes.AssetTypename,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.Username As [Last Login],
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
Cast(DateDiff(dd, tblAssetCustom.PurchaseDate, GetDate()) / 365.2425 As numeric(8,1)) As Age
From tblAssets
Inner Join tblADComputers On tblADComputers.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssetCustom.State = 1 And tblAssetGroups.AssetGroup Like 'DEPARTMENT'
Order By tsysAssetTypes.AssetTypename,
tblAssets.AssetName
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
Add one more column that conditionally defines the foreground colour, e.g.
  CASE
WHEN tblAssetCustom.PurchaseDate IS NULL THEN '#ff00ff' -- no purchase date: magic pink
WHEN DateAdd(dd, 5*365.2425, tblAssetCustom.PurchaseDate) < GetDate() THEN '#ff0000' -- more than 5yo: red
END AS foregroundcolor

View solution in original post

6 REPLIES 6
rader
Champion Sweeper III
I found that thread about 1/2hr of posting the request. I think what was screwing up my logic was the missing , in the previous lines before the CASE command. SQL's not my lingo, but I'm adapting my programming logic to it's nuances. I was able to get some basic colors figured out thanks to your code and the other thread.

Now I'm figuring out how to do math in SQL statements. Then I'll add colors to the results to make the results needed stand out better.

Thank you.
RCorbeil
Honored Sweeper II
You can do the same thing with both foreground (text) and background colours. See this thread.
mrdisco
Engaged Sweeper II
Oh, man. That's wonderful. Thank you so much!
rader
Champion Sweeper III
Josh R Smith wrote:
Oh, man. That's wonderful. Thank you so much!


Would you mind posting the complete code here. I'm trying to get the color to work so I can use the structure in another report, and I'm having a heck of a time getting it to work. I've tried adding the snippet code from RC62N to your report, but all I get is Invalid "Select" statement. Unexpected token "Case" at line xx, pos x.

Thanks.
mrdisco
Engaged Sweeper II
rader wrote:
Josh R Smith wrote:
Oh, man. That's wonderful. Thank you so much!


Would you mind posting the complete code here. I'm trying to get the color to work so I can use the structure in another report, and I'm having a heck of a time getting it to work. I've tried adding the snippet code from RC62N to your report, but all I get is Invalid "Select" statement. Unexpected token "Case" at line xx, pos x.

Thanks.


I hope this helps!


Select Top 10000000 tblAssets.AssetName,
tblADComputers.Description,
tsysAssetTypes.AssetTypename,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.Username As [Last Login],
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
Cast(DateDiff(dd, tblAssetCustom.PurchaseDate, GetDate()) / 365.2425
As numeric(8,1)) As Age,
Case
When tblAssetCustom.PurchaseDate Is Null Then '#ff00ff'
When DateAdd(dd, 5 * 365.2425, tblAssetCustom.PurchaseDate) < GetDate() Then
'#ff0000'
End As foregroundcolor
From tblAssets
Inner Join tblADComputers On tblADComputers.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssetCustom.State = 1 And tblAssetGroups.AssetGroup Like 'HCA School'
Order By tsysAssetTypes.AssetTypename,
tblAssets.AssetName
RCorbeil
Honored Sweeper II
Add one more column that conditionally defines the foreground colour, e.g.
  CASE
WHEN tblAssetCustom.PurchaseDate IS NULL THEN '#ff00ff' -- no purchase date: magic pink
WHEN DateAdd(dd, 5*365.2425, tblAssetCustom.PurchaseDate) < GetDate() THEN '#ff0000' -- more than 5yo: red
END AS foregroundcolor