Asset Out Between Date Range

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