The query you have will give you assets out between the selected date range
Things to Note:
- ActOutDate is the date the item was checked out
- ActInDate is the date the item was checked in
- The 1980 showing in ActInDate would mean the item was never checked back in. This could be a long term hire/an asset that was never returned or returned but not checked back in.
- A.DisDate is the disposal date of the asset, the queries below will exclude assets disposed of
Cut and paste the information below this line into your Excel Query Builder. Change the value of @SingleDate to whatever you want it to be and then copy everything from below this line into your query window..
| You can execute this query using Excel Query Builder in RentalPoint... |  |
DECLARE @FromDate AS DATETIME
DECLARE @ToDate AS DATETIME
SET @FromDate = '20201201'
SET @ToDate = '20210131'
SELECT A.product_code [Product Code],
A.asset_code [Barcode],
A.description [Asset Description],
AT.booking_no [Out on booking],
B.showname [Showname],
B.organizationv6 [Customer],
convert(varchar,b.ddate,101) [Booking Out Date],
convert(varchar,b.rdate,101) [Booking In Date],
convert(varchar,AT.ActOutDate,101) [Asset Out Date],
CASE
WHEN AT.actindate = CAST('Jan 1 1980' AS datetime) THEN 'Still Out'
WHEN AT.ActInDate > CAST('Jan 1 1980' AS DateTime) THEN convert(varchar,AT.ActInDate,101)
END AS [Asset Return Date]
FROM tblassetran AT
LEFT OUTER JOIN vwbookandhist B
ON B.booking_no = AT.booking_no
LEFT OUTER JOIN tblcust C
ON B.custcode = C.customer_code
INNER JOIN tblasset01 A
ON A.product_code = AT.product_code
AND A.stock_number = AT.stock_number
WHERE ( ( AT.actoutdate BETWEEN @FromDate AND @ToDate )
OR ( AT.actindate BETWEEN @FromDate AND @ToDate ) )
ORDER BY At.actoutdate,
at.actindate