The query you have will give you assets out on a given date - even if they were checked back in after that date Technically they were OUT on the given date, it will also show items that were never returned.
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 @SingleDate AS DATETIME
SET @SingleDate = GETDATE()
SELECT A.product_code [Product Code],
A.asset_code [Barcode],
A.DESCRIPTION [Asset Description],
AT.booking_no [Out on booking],
AT.ActOutDate [Out Date],
AT.ActInDate [In Date],
A.COST [Cost]
FROM tblassetran AT INNER JOIN tblasset01 A ON A.product_code = AT.product_code and A.stock_number = AT.stock_number
WHERE ((AT.ActOutDate > CAST('Jan 1 1980' AS DateTime)) and At.ActOutDate <= @SingleDate)
AND ((AT.ActInDate = CAST('Jan 1 1980' AS DateTime)) OR (AT.ActInDate > @SingleDate))
AND (ISNULL(a.DisDate,'1980-01-01') = '1980-01-01') /*assets are not sold*/
ORDER BY A.product_code, A.asset_code, AT.booking_no
OR...
SET @SingleDate = '20210101'
DECLARE @SingleDate AS DATETIME
SET @SingleDate = '20210101'
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 > Cast('Jan 1 1980' AS DATETIME) )
AND At.actoutdate <= @SingleDate )
AND ( ( AT.actindate = Cast('Jan 1 1980' AS DATETIME) )
OR ( AT.actindate > @SingleDate ) )
AND ( Isnull(a.disdate, '1980-01-01') = '1980-01-01' )
/*assets are not sold*/
ORDER BY A.product_code,
A.asset_code,
AT.booking_no