/* 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