Articles
Assets checked out to a booking
/* From within RentalPoint, right click on your booking and print-->packing list */ /* OR change the value of @Booking_no below and execute this query */ You can execute this query using Excel Query Builder in RentalPoint..... DECLARE...
Assets NOT Scanned in Stocktake Since Date
This query can be run to find all the assets that have not be scanned in 12 months or 24 months in a stocktake, so we know the missing items. You'll need to change the EntryDateTime to get the records within your specified timeframe. You can ex...
Assets NOT Scanned Out Since Date
In search of unused assets? This query will show assets that have not be scanned on to or off a booking, thus haven’t been used in RentalPoint. You can execute this query using Excel Query Builder in RentalPoint... select A1.* fro...
Assets out on given date
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 ...
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 neve...
Assets NOT Returned
Assets NOT Returned Rentalpointv10.
Lists all asset Tracked items with WH in date that has passed (i.e. NOT returned)
Assets Checked Out on What Booking?
Shows what booking an asset is checked out on.. You can execute this query using Excel Query Builder in RentalPoint... select booking_no_v32, product_code_v42, trans_qty, qtycheckedout, qtyreturned from tblitemtran where qtychecked...
Rental Items not returned between date range
/* All gear that didn't make it back from a show within date range, include warehouse status of the booking */ DECLARE @StartDate AS VARCHAR(20) DECLARE @EndDate AS VARCHAR(20) SET @StartDate = '2024-03-30' SET @EndDate = '2024-07-01' sele...
All Assets Outs to Maintenance
Shows a list of all assets scanned out to maintenance You can execute this query using Excel Query Builder in RentalPoint... SELECT IM.groupFld [Group],
IM.category [Category],
A.PRODUCT_COde [Product],
A.ASSET...
Asset Movement Including Last Stock Take
You can execute this query using Excel Query Builder in RentalPoint... SELECT A.asset_code [Barcode],
A.product_code [Product],
A.description,
A.Location,
A.stock_number [Stock Number],
A.serial_...
Asset Movements Including Number of Times Rented
Shows movement of all assets in the database You can execute this query using Excel Query Builder in RentalPoint... SELECT IM.groupFld [Group], IM.category [Category], A.PRODUCT_COde [Product], A.ASSET_CODE [Barcode], A.SERIAL_NO [Ser...
Assets Purchased Between Date Range
SELECT IM.groupfld AS [Product Group], IM.category AS [Product Category], A.product_code AS [Product Code], IM.descriptionv6 AS [Product Description], A.asset_code AS [Bar Code Number], A.descr...
Assets Entered Since
Shows a list of all assets entered since a specific purchase date. Change the purchase date below for specific results. select A.ASSET_CODE as [Barcode], A.DESCRIPTION as [Asset Description], A.modelNumber as [Asset Model #], ...
Asset Listing
Lists Assets in inventory with some rate and product information You can execute this query using Excel Query Builder in RentalPoint... SELECT A.description AS [Asset Description], A...
Assets Overdue for Return
select B.booking_no 'Booking #', CASE WHEN B.Booking_type_v32 = 0 THEN 'Rental' WHEN B.booking_type_v32 = 1 THEN 'Sub Hire' WHEN B.booking_type_v32 = 2 THEN 'Quote' ELSE '' END AS Type, ...
Booking Grid View
This is a very complex query so it will be difficult for most users to modify. however if changes need to be made please contact support. This will export the same data that is available via the booking grid. You can execute this query using ...
Bookings Entered Today
Shows New Bookings Entered TODAY SELECT B.booking_no AS Code, B.organizationv6 AS Company, B.ddate AS [Out Date], D.div_name AS Division, CASE ...
Bookings Entered Within Last 7 Days
Shows Bookings entered IN the LAST 7 days
SELECT B.booking_no AS Code,
B.OrganizationV6 AS Company,
B.dDate AS [Out Date],
D.div_name AS Division,
CASE
WHEN B.BookingProgressStatus = 0 THEN 'Quote'
...
Bookings between date range with Invoice and Payment Details
Shows Bookings within @StartDate and @Enddate for @CustCode. You will need to change these values in the query for specific results. DECLARE @StartDate AS VARCHAR(20) DECLARE @EndDate AS VARCHAR(20) DECLARE @CustCode AS VARCHAR(8) SET @StartDate ...
Bookings with Invoice and Payment Details in the last 30 days
*/ Bookings Invoiced within the last 30 days - Payment details included */ SELECT v.booking_no AS [Booking #], v.showname AS [Showname], v.organizationv6 AS...
Bookings with Invoice and Itemized Payment Details
/* Shows Bookings with invoice or payment recorded against them within the last 30 days Change the value of @DaySpan to change the range */ DECLARE @DaySpan INT ; SET @DaySpan = 30; SELECT v.booking_no ...
Bookings with Estimated Shortages
/* This query shows all bookings after July 1st 2017 and their estimated cross rental amount where a shortage exists*/ select I.booking_no_v32 as [Booking No], B.Showname as [Showname], Format(b.hire_price,'C') as [Total Rental], (Select ISNU...
Bookings Invoiced with Shortages
Invoiced Bookings with shortages, where warehouse out date is greater than Jan 1 2017. Change the date as needed
Booking Contacts with addresses for confirmed bookings within date rate
DECLARE @StartDate AS VARCHAR(20) DECLARE @EndDate AS VARCHAR(20) SET @StartDate = 'Apr 1 2018' SET @EndDate = 'Aug 30 2018' SELECT B.OrganizationV6 AS CompanyName, b.contact_nameV6, c.PostalAddress1, c.PostalAddress2, c.PostalAddress3, c.PostalSt...
Cancelled Bookings
SELECT B.booking_no as [Booking Number], B.organizationv6 as [Company], B.Showname as [ShowName], B.DateLastModified as [Cancellation Date], B.dDate [Warehouse Out Date], B.ShowSdate as [Show Start Date], S....
Customer Bookings with Date Range
/* This query will show a count of all given customer bookings, current and archived, that have a warehouse out date within the date range provided */ DECLARE @StartDate AS VARCHAR(20) DECLARE @EndDate AS VARCHAR(20) DECLARE @CustCode AS VARC...
CARNET Query
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], ...
Crew Usage and History
The queries below will provide an export OF your RentalPoint Labour IN Excel format.
For Labour listed IN unarchived bookings
/*This query will provide a summary OF crew requirements*/
SELECT IM.descriptionV6 [Designation],
T....
Cross Rentals & Transfers Assigned to Booking
Change the booking number to the one you are working on. This query will show all sub rentals, transfers, PO's assigned to your booking DECLARE @MYBOOKING VARCHAR(13) = 'CATHAY00003' DECLARE @Items TABLE ( [id] DECIMAL(10...
Customers List
This will provide the mailing address for active customers, this could be used for creating mailing lists. SELECT contactV6 AS Contact,
OrganisationV6 AS Company,
PostalAddress1 AS [Address 1],
PostalAddress2 AS [Address...
Customers By Division
SELECT c.OrganisationV6 AS Company,
d.div_name AS Division,
c.contactV6 AS Contact,
CASE
WHEN c.stop_credit = 0 THEN 'Allow Credit'
WHEN c.stop_credit = 1 THEN 'STOP CREDIT - DO NOT TRADE'
...
Customer Details with Last Booking Entered
SELECT BK.CUST as [Customer Code], c.OrganisationV6 as [Organization], c.contactv6 as [Contact], c.phone1countrycode as [Phone1 Country], CONCAT(c.Phone1AreaCode,' ', c.Phone1Digits) as [Phone1], ...
Customers including Credit Info
SELECT c.OrganisationV6 AS Company,
c.contactV6 AS Contact,
CASE
WHEN c.stop_credit = 0 THEN 'Allow Credit'
WHEN c.stop_credit = 1 THEN 'STOP CREDIT - DO NOT TRADE'
WHEN c.stop_credit = 2 THEN 'STO...
Add default Multi Stage Terms for all Customers that don't have default terms
/* Set up default 50/50 payterm stages for all customers that are not already set up for multi stage invoicing Change the Stage and StageName values as needed Contact support@rentp.com to modify this query */ DECLARE mycursor CURSOR fast_forward...
Customers with Invoices Within Date Range
--Lists Customers with invoices between the date range specified--- --Change the dates below for the dates you want to run the query -- DECLARE @StartDate AS VARCHAR (20) DECLARE @EndDate AS VARCHAR (20) SET @StartDate = ' Jan 1 2019 ' ...
Customer with Notes (using SQL function)
Knowledge of SQL Server Management Studio is needed to create the function below in the database, please contact support@rentp.com for assistance if needed Execute the function below in SQL Server Management Studio using your database name U...
Contacts List
SELECT T.OrganisationV6 AS [COMPANY],
c.ContactName AS [CONTACT NAME],
c.FirstName AS [FIRSTNAME],
c.MidName AS [MIDDLE NAME],
c.Surname AS [SURNAME],
c.Adr1 AS [ADDRESS 1],
c.Adr2 AS [ADDRESS 1],
...
Contacts List - All Contacts Added in the Last 30 Days
SELECT c.ContactName AS [Contact Name], c.firstname as [First Name], c.surname as [Surname], T.OrganisationV6 AS [Company], c.position as [Position], c.Email AS [E-mail], ...
Contact Distribution List
/*Export a distribution list for all contacts in the database with an email address and send emails box checked*/ SELECT [CallListID], [ContactID], [Completed], [ActivityID], C.firstname, C.surname, C.AskFor, c....
Contact Duplicates
SELECT contactname,
count(1) AS cnt
FROM tblcontact
GROUP BY Contactname
HAVING count(1) >1;
Cross Rental Purchase Orders within date range
List all Cross Rental PO's for a given date range
Excel Query Builder
Full details on how to use Excel Query Builder in RentalPoint in order to query your database for specific information and output the data to Excel. ...
Future Package Use
This query is useful if you need to determine how many times packages are being rented within a certain period of time. This is considered a more advanced query as you will need to adjust the date and or product code as you needs change. *Note i...
Freight Reports
You can execute any of the queries below using Excel Query Builder in RentalPoint..... Remember to change the date values (highlighted in red below) as needed. Freight Report for Date Range DECLARE @fromdate DATETIME DECLARE @todate DATE...
Rental Inventory Export
The query below will provide an export of all Rental Equipment. Each line is a separate field with the first 3 in Bold being mandatory for importing back into the system. Please contact support@rentp.com before attempting an import for the first ...
Rental Products and Assets
SELECT G.group_code AS [Product Group], G.group_descv6 AS [Group Description], IM.category AS [Product Category], C.cat_descv6 ...
Sales Inventory Export
The query below will provide an export of all Sales Equipment. Each line is a separate field with the first 3 in Bold being mandatory for importing back into the system. Please contact support@rentp.com before attempting an import for the fir...
Inventory Report for Disposed/Maintenance Items
You can execute this query using Excel Query Builder in RentalPoint..... SELECT A.product_code AS [Product Code], I.descriptionv6 AS [Product Description], A.asset_code AS [Bar Code], LO.locn_name AS [Locatio...
Inventory Report of Assets
Shows a list of all assets in inventory, their current status and if they're out on a booking.
Inventory Report of Assets by Purchase Date
/* Report of all assets in the system ordered by Purchase Date (newest to oldest) */ SELECT I.groupfld AS [Group], g.group_descv6 AS [Group Desc], i.category AS [Category...
Inventory Report of Non Tracked Gear
Shows a list of all non asset tracked gear in your system.
Inventory Report of Products and their Components etc.
The accessories and components are stored in a table called tblBill and link back to the main inventory master table tnlInvmas. The easiest way of creating this report will be by an Excel ODC query as per the instructions on the knowledge base at ...
Inventory Sales for date range
/* Lists all items sold on confirmed bookings between start and end date Int1 offers ability to show a shorter date range for comparison sales */ DECLARE @StartDate AS VARCHAR(20) DECLARE @EndDate AS VARCHAR(20) DECLARE @Int1Start AS VARCH...
Invoices Outstanding
/* Shows all invoices with outstanding balance based on closing date with an outstanding amount greater than InvValue */ DECLARE @ClosingDate AS VARCHAR (20) DECLARE @InvValue FLOAT SET @ClosingDate = '2024-2-1' SET @InvValue = 1 SELECT B.b...
Labour Designation Report
Provide a list of all Labour Designations set up in the RentalPoint Database SELECT G.Group_code AS [Product Group], G.group_descV6 AS [Group Description], IM.category AS [Product Category], C.cat_descV6 AS [Ca...
Technicians Attached to a Labour Designation
The technicians attached to a designation are stored in tblbill: TblInvmas.product_code = tblBill.parent_code (for the designation) tblBill.product_code = tblinvmas.product_code (for the technician) tblbill.contactid links the tech in the contac...
Package Revenue
This is an advance query as you will need to change date range as needed. This query will allow you to get the revenue that packages generated as well as determine how many times a package has been rented within a particular time period. Date r...
Products Due To Go Out Today OR In The Future
This query will return all products due to go out today or in the future for the AUDIO group. Change the AUDIO group below to whatever group you need OR remove the last AND clause to get all groups SELECT i.booking_id [Booking],
i.produc...
Products used on bookings that are no longer in inventory
select * from tblitemtran where ISNULL(product_code_v42 , '') '' AND ISNULL(product_code_v42 , '') NOT IN ( select ISNULL(product_code, '') from tblinvmas)
Products not used on Bookings
Execute these queries using Excel Query Builder /* Products not used on any booking*/ select m.product_code as [Inventory Code], m.descriptionV6 as [Description], isnull(i.product_code_v42,'') as [Item Code], isnull...
Purchase Order Grid
List items on the Purchase Order Grid based on the selection criteria in the 'Where' clause bolded below SELECT P.Location, P.ID, P.PVendorCode, ISNULL(B.RDate, 'Jan 1 1980') AS rdate, (CASE P.CrossRental WHE...
Purchase Orders for the last 90 Days
Purchase Orders entered over the last 90 days
Products in Inventory ordered by date they were added
SELECT CONVERT(VARCHAR, I.EntryDate, 101) AS [Entry Date], CASE WHEN LastUpdate = CAST('Jan 1 1980' AS DateTime) THEN '' WHEN LastUpdate = CAST('Dec 30 1899' AS DateTime) THEN '' ELSE ...
Products in Trash used on Bookings
select I.booking_no_v32 as [Booking #], I.FirstDate as [Out Date], I.RetnDate as [In Date], I.product_code_v42 as [Product], m.descriptionV6 as [Description] from tblitemtran I left outer join tblinvmas m on m.pr...
Stock Take - Non Barcoded Items
/*All NON Barcode tracked Rental Items in Inventory Master with a count of whats out on a booking */ Select m.product_code AS [PRODUCT CODE], m.descriptionV6 AS [DESCRIPTION], ('$' + convert(VARCHAR,convert(money,m.cost_price),...
Transfers Checked out today and balance Pending checkout
DECLARE @Today DATETIME set @Today = CONVERT(datetime,Convert(Char(10), GetDate(),20),20) select I.booking_no_v32 as [Booking Number] , I.product_code_v42 AS [Product Code], I.trans_qty AS [Qty Ordered], ...
Transfers
/* All transfers and bookings they are assigned to*/ Select T.booking_no [Code], isnull(B.OrganizationV6,'') [Company], DATENAME(WEEKDAY,T.ddate) [Out], convert(varchar,T.ddate,105) [Date], dbo.MakeTime(T.de...
Update Product Replacement Value
Full details on how to update product replacement value for all products in your database ...
Import Product Weight and/or Rate Information
Import changes to existing inventory with a smaller selection of data (for example rate and weight) First export product information (copy/paste the query below into Export Query Builder ) SELECT G.group_code AS [Product Group], G.g...
Vendor List
This query will create a list of active vendors SELECT VendorContact,
VendorName AS Company,
Vadr1 AS [Address 1],
Vadr2 AS [Address 2],
Vadr3 AS [City/Town],
Vpostcode AS [Postal Code],
Country,
...
Vendors With a PO
SELECT v.VendorName AS Company,
Vemail AS [Email],
v.CellAreaCode AS [Cell Area Code],
v.CellDigits [Cell],
p.AreaCode AS [Phone1 Area Code],
p.Digits AS [Phone1],
p2.AreaCode AS [Phone2 Area Code],
...
Venues
Used to export all Venue information to Excel via the Excel Query builder. The first line will provide the Venue name and address information, if you don't need any of the other information you can remove it from the query, each line in italicizes...
Active Customers with Notes
/* Shows all active customers with notes */ SELECT CASE WHEN N.line_no = 1 THEN n.customer_code ELSE '' END AS [COMPANY CODE], CASE WHEN N.line_no = 1 THEN C.OrganisationV6 ELSE '' END AS [COMPANY NAME], N.line_no AS LINE, ...