Shows CARNET details for all items that were checked out after a given date and have not been returned yet.
SELECT A.BOOKING_NO AS [Booking],
M.groupFld AS [Group],
M.category AS [Category],
A.DESCRIPTION AS [Description],
M.retail_price AS [Retail Price],
A.SERIAL_NO AS [Serial],
M.CountryOfOrigin AS [Country of Origin],
A.ASSET_CODE AS [Barcode],
M.zColor AS [Manufacturer],
ISNULL(AT.checkoutNo, 1) AS [Session Number],
(AT.qtycheckedOut - At.qtyreturned) AS [Qty],
AT.ActOutDate AS [Out Date],
AT.act_time_out_h AS [Out Hour],
AT.act_time_out_m AS [Out Minute],
M.zModelNo AS [Model],
AT.OperatorID AS [Operator ID]
FROM tblAssetran AT
INNER JOIN tblAsset01 A ON A.PRODUCT_COde = AT.product_code
AND A.STOCK_NUMBER = AT.stock_number
INNER JOIN tblInvmas M ON M.product_code = AT.product_code
WHERE (LEN(A.BOOKING_NO) > 0)
AND (AT.ActOutDate > '2017/01/01')
AND (AT.qtycheckedOut - At.qtyreturned) > 0
ORDER BY [Booking],
[Session Number],
[Barcode],
[Out Date]