/*Invoiced Bookings with shortages, where warehouse out date is greater than Jan 1 2017. Change the date as needed */
select b.booking_no as [Booking No],
B.Showname as [Showname],
B.ddate as [Out Date],
B.Salesperson as [SalesPerson],
D.Div_name as [Division],
B.invoice_no as [Invoice #],
B.OrganizationV6 as [Customer],
O.Loginname as [Operator],
Format(b.hire_price,'C') as [Total Rental],
(Select ISNULL(FORMAT(sum(x.Price/x.trans_qty * x.sub_hire_qtyv61),'C'),0) from tblitemtran x where x.booking_no_v32 = B.booking_no and x.sub_hire_qtyV61 > 0) as [Shortages],
FORMAT(b.hire_price - (Select ISNULL(sum(x.Price/x.trans_qty * x.sub_hire_qtyv61),0) from tblitemtran x where x.booking_no_v32 = B.booking_no and x.sub_hire_qtyV61 > 0),'C') as [Rental LESS Shortages]
from tblBookings B
left outer join tblOperators O on B.OperatorsID = O.ID
LEFT OUTER JOIN tblDivlist D on D.div_number = B.division
Where b.ddate >= '2017-01-01' and b.invoiced = 'Y' and b.booking_type_v32 in (0,2,6,7,14,15,16,17,18,19,20,21)
and b.booking_no in (select distinct booking_no_v32 from tblItemtran where sub_hire_qtyV61 > 0)
Order by B.booking_no,b.Salesperson