This is a simple tool but still handy.

## You are here

# Fixed Assets Balance by Asset

## Fixed Assets Balance by Asset

Submitted by Nathan Clouse on 10/25/16.

Fixed Assets Balance by Asset to today. This can be modified to get a balance at a point in time by replacing getDate() with a particular date.

CREATE VIEW [dbo].[vw_faAssetBalances] AS select at.ASSETGROUP, at.ASSETID, at.NAME, ab.BOOKID, 'bookType' = 'Value Model', ab.STATUS, 'aquisition' = (select isnull(SUM(AMOUNTMST),0) from ASSETTRANS atrans where atrans.partition = at.partition and atrans.dataareaid = at.DATAAREAID and atrans.ASSETID = at.ASSETID and atrans.TRANSTYPE in (1,2,27) --acquisition and atrans.TRANSDATE <= getdate() and atrans.DATAAREAID = at.DATAAREAID), 'bonus' = (select isnull(SUM(AMOUNTMST),0) from ASSETTRANS atrans where atrans.partition = at.partition and atrans.dataareaid = at.DATAAREAID and atrans.ASSETID = at.ASSETID and atrans.TRANSTYPE = 32 --bonus and atrans.TRANSDATE <= getdate() and atrans.DATAAREAID = at.DATAAREAID), 'depreciation' = (select isnull(SUM(AMOUNTMST),0) from ASSETTRANS atrans where atrans.partition = at.partition and atrans.dataareaid = at.DATAAREAID and atrans.ASSETID = at.ASSETID and atrans.TRANSTYPE in (3,4) --depreciation and atrans.TRANSDATE <= getdate() and atrans.DATAAREAID = at.DATAAREAID), 'profitLoss' = (select isnull(SUM(AMOUNTMST),0) from ASSETTRANS atrans where atrans.partition = at.partition and atrans.dataareaid = at.DATAAREAID and atrans.ASSETID = at.ASSETID and atrans.TRANSTYPE = 11 --profitLoss and atrans.TRANSDATE <= getdate() and atrans.DATAAREAID = at.DATAAREAID), 'issue' = (select isnull(SUM(AMOUNTMST),0) from ASSETTRANS atrans where atrans.partition = at.partition and atrans.dataareaid = at.DATAAREAID and atrans.ASSETID = at.ASSETID and atrans.TRANSTYPE in (8,9) --issue and atrans.TRANSDATE <= getdate() and atrans.DATAAREAID = at.DATAAREAID), 'netValue' = (select isnull(SUM(AMOUNTMST),0) from ASSETTRANS atrans where atrans.partition = at.partition and atrans.dataareaid = at.DATAAREAID and atrans.ASSETID = at.ASSETID and atrans.TRANSDATE <= getdate() and atrans.DATAAREAID = at.DATAAREAID) from ASSETTABLE at inner join ASSETBOOK ab on ab.partition = at.partition and ab.DATAAREAID = at.DATAAREAID and ab.ASSETID = at.ASSETID union all select at.ASSETGROUP, at.ASSETID, at.NAME, adb.DEPRECIATIONBOOKID, 'bookType' = 'Depreciation Book', adb.ASSETSTATUS, 'aquisition' = (select isnull(SUM(AMOUNTMST),0) from ASSETDEPBOOKTRANS adbtrans where adbtrans.partition = at.partition and adbtrans.DATAAREAID = at.DATAAREAID and adbtrans.ASSETID = at.ASSETID and adbtrans.TRANSTYPE in (1,2,27) --acquisition and adbtrans.DEPRECIATIONBOOKID = adb.DEPRECIATIONBOOKID and adbTrans.TRANSDATE <= getdate() ), 'bonus' = (select isnull(SUM(AMOUNTMST),0) from ASSETDEPBOOKTRANS adbtrans where adbtrans.partition = at.partition and adbtrans.DATAAREAID = at.DATAAREAID and adbtrans.ASSETID = at.ASSETID and adbtrans.TRANSTYPE = 32 --bonus and adbtrans.DEPRECIATIONBOOKID = adb.DEPRECIATIONBOOKID and adbTrans.TRANSDATE <= getdate()), 'depreciation' = (select isnull(SUM(AMOUNTMST),0) from ASSETDEPBOOKTRANS adbtrans where adbtrans.partition = at.partition and adbtrans.DATAAREAID = at.DATAAREAID and adbtrans.ASSETID = at.ASSETID and adbtrans.TRANSTYPE in (3,4) --depreciation and adbtrans.DEPRECIATIONBOOKID = adb.DEPRECIATIONBOOKID and adbTrans.TRANSDATE <= getdate()), 'profitLoss' = (select isnull(SUM(AMOUNTMST),0) from ASSETDEPBOOKTRANS adbtrans where adbtrans.partition = at.partition and adbtrans.DATAAREAID = at.DATAAREAID and adbtrans.ASSETID = at.ASSETID and adbtrans.TRANSTYPE = 11 --profitLoss and adbtrans.DEPRECIATIONBOOKID = adb.DEPRECIATIONBOOKID and adbTrans.TRANSDATE <= getdate()), 'issue' = (select isnull(SUM(AMOUNTMST),0) from ASSETDEPBOOKTRANS adbtrans where adbtrans.partition = at.partition and adbtrans.DATAAREAID = at.DATAAREAID and adbtrans.ASSETID = at.ASSETID and adbtrans.TRANSTYPE in (8,9) --issue and adbtrans.DEPRECIATIONBOOKID = adb.DEPRECIATIONBOOKID and adbTrans.TRANSDATE <= getdate()), 'netValue' = (select isnull(SUM(AMOUNTMST),0) from ASSETDEPBOOKTRANS adbtrans where adbtrans.partition = at.partition and adbtrans.DATAAREAID = at.DATAAREAID and adbtrans.ASSETID = at.ASSETID and adbtrans.DEPRECIATIONBOOKID = adb.DEPRECIATIONBOOKID and adbTrans.TRANSDATE <= getdate()) from ASSETTABLE at inner join ASSETDEPBOOK adb on adb.partition = at.partition and adb.dataareaid = at.dataareaid and adb.ASSETID = at.ASSETID GO