Shows movement of all assets in the database
| You can execute this query using Excel Query Builder in RentalPoint... |  |
SELECT IM.groupFld [Group],
IM.category [Category],
A.PRODUCT_COde [Product],
A.ASSET_CODE [Barcode],
A.SERIAL_NO [Serial],
A.locn [Item Location],
A.COST,
A.EST_RESALE,
A.INSURED_VAL,
A.ServiceStatus,
A.iDisposalType,
CASE
WHEN (A.ServiceStatus = 0
AND ISNULL(A.iDisposalType,0) = 0) THEN 'Active'
WHEN A.ServiceStatus = 1 THEN 'Temporarily Out of Service'
ELSE 'Permanently Out of Service'
END [Status],
CASE
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 ''
END [Disposal Type],
(SELECT TOP 1 CASE
WHEN AT.ActInDate = CAST('Jan 1 1980' AS DateTime) THEN 'Out'
WHEN AT.ActInDate > CAST('Jan 1 1980' AS DateTime) THEN 'In'
END
FROM tblAssetran AT
WHERE AT.product_code = A.PRODUCT_COde
AND AT.stock_number = A.STOCK_NUMBER
ORDER BY ActOutDate DESC) [Last Asset Movement Type],
(SELECT TOP 1 AT.booking_no
FROM tblAssetran AT
WHERE AT.product_code = A.PRODUCT_COde
AND AT.stock_number = A.STOCK_NUMBER
ORDER BY ActOutDate DESC) [Last Asset Movement Booking],
((SELECT B.showname from tblbookings B where B.booking_no = (SELECT TOP 1 AT.booking_no
FROM tblAssetran AT
WHERE AT.product_code = A.PRODUCT_COde
AND AT.stock_number = A.STOCK_NUMBER
ORDER BY ActOutDate DESC))
UNION
(SELECT B.showname from tblHistbks B where B.booking_no = (SELECT TOP 1 AT.booking_no
FROM tblAssetran AT
WHERE AT.product_code = A.PRODUCT_COde
AND AT.stock_number = A.STOCK_NUMBER
ORDER BY ActOutDate DESC))) [Showname],
(SELECT TOP 1 CASE
WHEN AT.ActInDate = CAST('Jan 1 1980' AS DateTime) THEN 'Still Out On Booking'
ELSE Convert(varchar, AT.ActInDate,101)
END
FROM tblAssetran AT
WHERE AT.product_code = A.PRODUCT_COde
AND AT.stock_number = A.STOCK_NUMBER
ORDER BY ActOutDate DESC) [Last Asset Movement Date],
(SELECT COUNT(AT.ID)
FROM tblAssetran AT
LEFT OUTER JOIN tblbookings B ON B.booking_no = AT.booking_no
WHERE AT.product_code = A.PRODUCT_COde
AND AT.stock_number = A.STOCK_NUMBER /* AND B.booking_type_v32 in (0, 4, 6, 14, 15, 16, 17)*/ ) [Number Times Rented],
(SELECT ISNULL(SUM(ABS(DATEDIFF(DAY, AT.ActOutDate, AT.ActInDate))), 0)
FROM tblAssetran AT
LEFT OUTER JOIN tblbookings B ON B.booking_no = AT.booking_no
WHERE AT.product_code = A.PRODUCT_COde
AND AT.stock_number = A.STOCK_NUMBER
AND AT.ActInDate > AT.ActOutDate /* must be returned in order to calculate correctly */ /* AND B.booking_type_v32 in (0, 4, 6, 14, 15, 16, 17) */) [Number Days Rented]
FROM tblAsset01 A
LEFT OUTER JOIN tblInvmas IM ON IM.product_code = A.PRODUCT_COde
/*WHERE a.iDisposalType IN (2) */
/*WHERE A.Asset_Code = 'XXXXXX' */
ORDER BY IM.groupFld,
IM.category,
A.PRODUCT_COde,
A.ASSET_CODE