Assets NOT Returned Rentalpointv10
Lists all asset Tracked items with WH in date that has passed (i.e. NOT returned)
| You can execute this query using Excel Query Builder in RentalPoint... |  |
SELECT I.category [Category] ,
OA.Product_code [Product Code],
A.ASSET_CODE [Asset Barcode],
OA.Booking_no [Booking Number],
B.showName [Showname],
b.rDate [WH In Date]
FROM tblOutAssets OA LEFT OUTER JOIN tblBookings B ON B.booking_no = OA.Booking_no
LEFT OUTER JOIN tblInvmas I ON I.product_code = OA.Product_code
LEFT OUTER JOIN tblAsset01 A ON A.PRODUCT_COde = OA.Product_code AND A.STOCK_NUMBER = OA.Stock_number
LEFT OUTER JOIN tblitemtran F ON B.booking_no = F.booking_no_v32
WHERE B.rDate < GETDATE() AND OA.Stock_number <> 65535
ORDER BY I.category, B.rDate
/* Alternative query for Rentalpointv10 */
SELECT I.category [Category] ,
OA.Product_code [Product Code],
A.ASSET_CODE [Asset Barcode],
A.DESCRIPTION [Asset Description],
OA.Booking_no [Booking Number],
B.showName [Showname],
b.dDate [WH Out Date],
b.rDate [WH In Date]
FROM tblOutAssets OA LEFT OUTER JOIN tblBookings B ON B.booking_no = OA.Booking_no
LEFT OUTER JOIN tblInvmas I ON I.product_code = OA.Product_code
LEFT OUTER JOIN tblAsset01 A ON A.PRODUCT_COde = OA.Product_code AND A.STOCK_NUMBER = OA.Stock_number
LEFT OUTER JOIN tblitemtran F ON B.booking_no = F.booking_no_v32
WHERE B.rDate < GETDATE() AND OA.Stock_number <> 65535
ORDER BY I.category, B.rDate
/* Use this query for Rentalpointv11 */
SELECT I.category [Category],
OA.product_code [Product Code],
OA.qtycheckedout [Qty Out],
OA.qtyreturned [Qty Ret],
A.asset_code [Asset Barcode],
A.description [Asset Description],
OA.booking_no [Booking Number],
B.showname [Showname],
b.ddate [WH Out Date],
b.rdate [WH In Date]
FROM tblassetran OA
LEFT OUTER JOIN tblbookings B
ON B.booking_no = OA.booking_no
LEFT OUTER JOIN tblinvmas I
ON I.product_code = OA.product_code
LEFT OUTER JOIN tblasset01 A
ON A.product_code = OA.product_code
AND A.stock_number = OA.stock_number
LEFT OUTER JOIN tblitemtran T
ON T.booking_no_v32 = OA.booking_no
AND T.subrentallinkid = OA.itemtranid
WHERE B.rdate < Getdate()
AND OA.stock_number <> 65535
AND ( OA.qtycheckedout > OA.qtyreturned )
AND T.trans_type_v41 <> 6
ORDER BY I.category,
B.rdate