Query on current asset value (MSSQL & PGSQL)

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 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 ...
                    • Query to show total time spent of a technician for the current month-PGSQL

                      Working on Build's: 14500 Query show total time spent by technician for the current month regardless of the ticket created date based on worklogs added PGSQL: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", cd.CATEGORYNAME AS ...