Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎04-04-2018 08:26 PM
From one of the built-in reports, I created a simple report that gives me basic info about a filtered set of computers from our Lansweeper DB. I'd like to add a column that shows the current age of each computer in years (to one decimal place). I want to calculate the age based on the stored value for the purchase date of each system (tblAssetCustom.PurchaseDate). Can someone assist me? I'm not a SQL wiz. Here's my code so far:
Select Distinct Top 1000000 tsysOS.Image As Icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Serialnumber As [Service Tag],
tblAssetCustom.Model As Model,
tblAssets.Processor As CPU,
Cast(tblAssets.Memory / 1024 As Numeric) As [RAM (GB)],
tblAssetCustom.PurchaseDate As [Ship Date]
From tblAssets
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblAssets.AssetName Like '%LABN110%'
Order By tblAssets.AssetName
Thanks!
Select Distinct Top 1000000 tsysOS.Image As Icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Serialnumber As [Service Tag],
tblAssetCustom.Model As Model,
tblAssets.Processor As CPU,
Cast(tblAssets.Memory / 1024 As Numeric) As [RAM (GB)],
tblAssetCustom.PurchaseDate As [Ship Date]
From tblAssets
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblAssets.AssetName Like '%LABN110%'
Order By tblAssets.AssetName
Thanks!
Solved! Go to Solution.
Labels:
- Labels:
-
Report Center
1 ACCEPTED SOLUTION
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎04-05-2018 05:06 PM
Try this:
Breakdown:
Days between purchase date and now:
DateDiff(dd, tblAssetCustom.PurchaseDate, GetDate())
(Doing a DateDiff for years would truncate the result.)
Convert that to years:
/ 365.2425
(If you're not anal enough to care about accounting for leap centuries, you could stick with 365.25. )
Format it as numeric, rounded to one decimal place:
Cast(... As numeric(8,1))
Cast(DateDiff(dd, tblAssetCustom.PurchaseDate, GetDate()) / 365.2425 As numeric(8,1)) As Age
Breakdown:
Days between purchase date and now:
DateDiff(dd, tblAssetCustom.PurchaseDate, GetDate())
(Doing a DateDiff for years would truncate the result.)
Convert that to years:
/ 365.2425
(If you're not anal enough to care about accounting for leap centuries, you could stick with 365.25. )
Format it as numeric, rounded to one decimal place:
Cast(... As numeric(8,1))
11 REPLIES 11
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎04-05-2018 07:37 PM
wdillner wrote:
That's perfect! Exactly what I was looking for. Thank you!
I appreciate that you included the breakdown. That increases my knowledge!
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎04-05-2018 05:06 PM
Try this:
Breakdown:
Days between purchase date and now:
DateDiff(dd, tblAssetCustom.PurchaseDate, GetDate())
(Doing a DateDiff for years would truncate the result.)
Convert that to years:
/ 365.2425
(If you're not anal enough to care about accounting for leap centuries, you could stick with 365.25. )
Format it as numeric, rounded to one decimal place:
Cast(... As numeric(8,1))
Cast(DateDiff(dd, tblAssetCustom.PurchaseDate, GetDate()) / 365.2425 As numeric(8,1)) As Age
Breakdown:
Days between purchase date and now:
DateDiff(dd, tblAssetCustom.PurchaseDate, GetDate())
(Doing a DateDiff for years would truncate the result.)
Convert that to years:
/ 365.2425
(If you're not anal enough to care about accounting for leap centuries, you could stick with 365.25. )
Format it as numeric, rounded to one decimal place:
Cast(... As numeric(8,1))