CREATE VIEW [dbo].[vw_faAssetsList]
AS 
     SELECT at.ASSETGROUP,
            at.ASSETID,
            at.NAME,
            at.QUANTITY,
            at.UNITCOST,
            at.REFERENCE,
            at.LOCATION,
            ab.BOOKID,
            ab.[STATUS],
            ab.ACQUISITIONDATE,
            ab.ACQUISITIONPRICE,
            ab.ACQUISITIONMETHOD,
            abt.DEPRECIATIONPROFILE,
            'DEPRECIATIONALTPROFILE' = CAST('' AS VARCHAR(20)),
            ab.DEPRECIATIONCONVENTION,
            ab.DEPRECIATION,
            ab.LASTDEPRECIATIONDATE,
            ab.SERVICELIFE,
            ab.[LIFETIME],
            ab.LIFETIMEREST,
            ab.USEDFROMDATE,
            'bonus' = CAST('' AS VARCHAR(20)),
            abm.BOOKTYPE
     FROM   ASSETTABLE at
     INNER JOIN ASSETBOOK ab
     ON     ab.partition = at.partition
            AND ab.DATAAREAID = at.DATAAREAID
            AND ab.ASSETID = at.ASSETID
     INNER JOIN ASSETBOOKTABLE abt
     ON     abt.partition = at.partition
            AND abt.DATAAREAID = at.DATAAREAID
            AND abt.BOOKID = ab.BOOKID
     INNER JOIN ASSETBOOKMERGE abm
     ON     abm.partition = at.partition
            AND abm.DATAAREAID = at.DATAAREAID
            AND abm.ASSETID = at.ASSETID
            AND abm.ASSETGROUP = ab.ASSETGROUP
            AND abm.BOOKID = ab.BOOKID
     UNION ALL
     SELECT at.ASSETGROUP,
            at.ASSETID,
            at.NAME,
            at.QUANTITY,
            at.UNITCOST,
            at.REFERENCE,
            at.LOCATION,
            adb.DEPRECIATIONBOOKID,
            adb.ASSETSTATUS,
            adb.ACQUISITIONDATE,
            adb.ACQUISITIONPRICE,
            adb.ACQUISITIONMETHOD,
            adb.DEPRECIATIONPROFILE,
            adb.DEPRECIATIONALTPROFILE,
            adb.DEPRECIATIONCONVENTION,
            adb.DEPRECIATION,
            adb.LASTDEPRECIATIONDATE,
            adb.SERVICELIFE,
            adb.[LIFETIME],
            adb.LIFETIMEREST,
            adb.USEDFROMDATE,
            adbb.BONUSID,
            abm.BOOKTYPE
     FROM   assettable at
     INNER JOIN ASSETDEPBOOK adb
     ON     adb.partition = at.partition
            AND adb.DATAAREAID = at.DATAAREAID
            AND adb.ASSETID = at.ASSETID
     INNER JOIN ASSETBOOKMERGE abm
     ON     abm.partition = at.partition
            AND abm.DATAAREAID = at.DATAAREAID
            AND abm.ASSETID = at.ASSETID
            AND abm.ASSETGROUP = adb.ASSETGROUPID
            AND abm.BOOKID = adb.DEPRECIATIONBOOKID
     LEFT JOIN ASSETDEPBOOKBONUS adbb
     ON     adbb.ASSETID = at.ASSETID
            AND adbb.depreciationbookid = adb.depreciationbookid
            AND adbb.DATAAREAID = at.DATAAREAID
            AND adbb.Partition = at.Partition

GO