Inventory Report of Assets by Purchase Date

/* Report of all assets in the system ordered by Purchase Date (newest to oldest) */

SELECT I.groupfld                       AS [Group],
       g.group_descv6                   AS [Group Desc],
       i.category                       AS [Category],
       c.cat_descv6                     AS [Category Desc],
       A.product_code                   AS [Product Code],
       I.descriptionv6                  AS [Product Description],
       A.asset_code                     AS [Bar Code],
       A.description                    AS [Asset Description],
       LO.locn_name                     AS [Location],
       A.stock_number                   AS [Stock Number],
       A.serial_no                      AS [Serial Number],
       A.modelnumber                    AS [Asset Model Number],
       A.booking_no                     AS [OUT on Booking],
       CASE
         WHEN A.idisposaltype = 0 THEN
           CASE
             WHEN A.servicestatus <> 0 THEN 'Inactive'
             ELSE 'Active'
           END
         WHEN A.idisposaltype = 1 THEN 'Sold'
         WHEN A.idisposaltype = 2 THEN 'Lost/Missing'
         WHEN A.idisposaltype = 3 THEN 'Stolen'
         WHEN A.idisposaltype = 4 THEN 'Written Off'
         ELSE '**E**'
       END                              AS [Disposal Type],
       Format(A.purdate, 'yyyy-MMM-dd') AS [Purchase Date],
       CASE
         WHEN a.disdate = '1980-01-01' THEN ''
         ELSE Format(A.disdate, 'yyyy-MMM-dd')
       END                              AS [Disposal Date],
       CASE
         WHEN A.servicestatus = 1 THEN 'Out for Maintenance'
         WHEN A.servicestatus = 2 THEN 'Permanently Out of Service'
         WHEN A.servicestatus = 0 THEN
           CASE
             WHEN A.idisposaltype <> 0 THEN 'Inactive'
             ELSE 'In Service'
           END
         ELSE '** E **'
       END                              AS [Service Status],
       CASE
         WHEN Isnull(M.datef, '') = '1900-01-01' THEN ''
         ELSE Format(M.datef, 'yyyy-MMM-dd')
       END                              AS [Service Entry Date],
       Isnull(MN1.textline, '')         AS [Faults],
       Isnull(MN2.textline, '')         AS [Repair Details],
       Isnull(M.labour, '')             AS [Labor Charge],
       Isnull(M.material, '')           AS [Material Charge],
       Isnull(M.reference, '')          AS [Maintenance Reference],
       CASE
         WHEN M.assetstatus = 0 THEN 'Damaged'
         WHEN M.assetstatus = 1 THEN 'Faulty'
         WHEN M.assetstatus = '2' THEN 'Test Required'
         ELSE ''
       END                              [Maintenance Status]
FROM   tblasset01 AS A
       LEFT OUTER JOIN tblinvmas AS I
                    ON I.product_code = A.product_code
       LEFT OUTER JOIN tblcategory AS c
                    ON c.category_code = i.category
       LEFT OUTER JOIN tbllocnlist AS LO
                    ON LO.locn_number = A.locn
       LEFT OUTER JOIN tblgroup AS G
                    ON G.group_code = I.groupfld
       LEFT OUTER JOIN tblmaint M
                    ON A.product_code = M.product_code
                       AND A.stock_number = M.stock_number
       LEFT OUTER JOIN tblmaintenancenotes MN1
                    ON MN1.maintenanceid = M.id
                       AND MN1.notetype = 0
                       AND MN1.linenumber = 0
       LEFT OUTER JOIN tblmaintenancenotes MN2
                    ON MN2.maintenanceid = M.id
                       AND MN2.notetype = 1
                       AND MN2.linenumber = 0
ORDER  BY a.purdate DESC