/*shows a list of all assets in inventory, their current status and if they're out on a booking. The report is sorted by group so you can easily separate it out into different reports as you see fit. This prevents you from having to run the query many times with different group codes.*/
| You can execute this query using Excel Query Builder in RentalPoint..... |  |
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.purdate AS [Purchase Date],
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],
A.disdate 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],
Isnull(M.datef, '') AS [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 G.seqno,
I.seq_no,
a.stock_number