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 M365 Manager Plus?
        New to M365 Manager Plus?
          New to RecoveryManager Plus?
          New to RecoveryManager Plus?
            New to Exchange Reporter Plus?
            New to Exchange Reporter Plus?
              New to SharePoint Manager Plus?
              New to SharePoint Manager Plus?
                  See all integrations
                  New to ADManager Plus?

                    New to ADSelfService Plus?

                    Start your free trial

                      • Related Articles

                      • 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 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 show resolved by value (MSSQL & PGSQL)

                        Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: SELECT wo.WORKORDERID AS "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", LONGTODATE(wos.assignedtime) "Assigned Time", LONGTODATE(wo.RESOLVEDTIME) AS "Resolved Time", ...

                      • 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 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 ...

                        Related Products