Query on current asset value (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306)
PGSQL:
SELECT productType.COMPONENTTYPENAME AS "Product Type", resource.RESOURCENAME AS "Asset Name1", resource.RESOURCENAME AS "Asset Name", product.COMPONENTNAME AS "Product",
LONGTODATE(resource.ACQUISITIONDATE) AS "Acquisition Date", aaov.NAME AS "Site", resToCost.PURCHASECOST AS "Purchase Cost",
resToCost.TOTALCOST AS "Total Cost", resToCost.CURRENTCOST AS "Current Book Value", state.DISPLAYSTATE AS "Asset State",
to_char(to_timestamp(to_char((dep.MONTH+1), '999'), 'MM'), 'Mon') "Months", dep.YEAR "Year", dep.bookvalue "Book Value on Depreciation" FROM Resources resource
LEFT JOIN ComponentDefinition product ON resource.COMPONENTID=product.COMPONENTID
LEFT JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID
LEFT JOIN ResourceState state ON resource.RESOURCESTATEID=state.RESOURCESTATEID
LEFT JOIN ResourceLocation resLocation ON resource.RESOURCEID=resLocation.RESOURCEID
LEFT JOIN SiteDefinition siteDef ON resLocation.SITEID=siteDef.SITEID
LEFT JOIN SDOrganization aaov ON siteDef.SITEID=aaov.ORG_ID
LEFT JOIN ResourceToCost resToCost ON resource.RESOURCEID=resToCost.RESOURCEID
LEFT JOIN ResourceDepreciationValue dep on resource.RESOURCEID = dep.RESOURCEID
and ( (dep.YEAR = date_part('year', timestamp '2018-02-28') and dep.MONTH >= (date_part('month', timestamp '2018-02-28'))-1) OR dep.YEAR > date_part('year', timestamp '2018-02-28'))
and ( (dep.YEAR = date_part('year', timestamp '2019-02-28') and dep.MONTH <= (date_part('month', timestamp '2021-02-28'))-1) OR dep.YEAR < date_part('year', timestamp '2021-03-28')) ORDER BY 2, dep.DEPRECIATIONVALUEID
MSSQL:
SELECT productType.COMPONENTTYPENAME AS "Product Type", resource.RESOURCENAME AS "Asset Name1", resource.RESOURCENAME AS "Asset Name", product.COMPONENTNAME AS "Product",
LONGTODATE(resource.ACQUISITIONDATE) AS "Acquisition Date", aaov.NAME AS "Site", resToCost.PURCHASECOST AS "Purchase Cost",
resToCost.TOTALCOST AS "Total Cost", resToCost.CURRENTCOST AS "Current Book Value", state.DISPLAYSTATE AS "Asset State" FROM Resources resource
LEFT JOIN ComponentDefinition product ON resource.COMPONENTID=product.COMPONENTID
LEFT JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID
LEFT JOIN ResourceState state ON resource.RESOURCESTATEID=state.RESOURCESTATEID
LEFT JOIN ResourceLocation resLocation ON resource.RESOURCEID=resLocation.RESOURCEID
LEFT JOIN SiteDefinition siteDef ON resLocation.SITEID=siteDef.SITEID
LEFT JOIN SDOrganization aaov ON siteDef.SITEID=aaov.ORG_ID
LEFT JOIN ResourceToCost resToCost ON resource.RESOURCEID=resToCost.RESOURCEID
ORDER BY 2
New to ADSelfService Plus?
Related Articles
Query to show Asset state history ( MSSQL)
Tested in MSSQL build (14306) SELECT res.resourcename 'Asset Name', res.assettag 'Asset Tag',res.serialno 'Asset Serial No.', rs2.statedesc 'Previous State',rs1.statedesc 'Current State',DATEADD(s,DATEDIFF(s,GETUTCDATE() ,getdate()) + ...Query to get Asset contract and its details (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) SELECT mcdt.Contractid "URL Contract ID", mcdt.customcontractid "Custom contractid in details page", mcdt.CONTRACTNAME "Contract Name", aao.NAME "Maintenance Vendor Name", LONGTODATE(mcdt.FROMDATE) ...Query report to get the successfully scanned assets (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Query report to get the successfully scanned assets with audit status, state , Serial no and site. SELECT max(resource.resourcename) "Asset Name", max(resource.SERIALNO) AS "Org Serial Number", ...Query to show Average response time for Category (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL: SELECT accountdefinition.org_name "Account",cd.categoryname "Category", TO_CHAR(((avg(wo.respondedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg Response Time" FROM ...Query to show asset additional attributes. (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Go to Reports-New Query Report and execute this query. SELECT ci.CINAME AS "CI Name", citype.TYPENAME AS "CI Type", ci.DESCRIPTION AS "Description",aao.NAME AS "Vendor Name",state.DISPLAYSTATE AS "Asset ...