/* 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 ISNULL(FORMAT(sum(x.Price/x.trans_qty * x.sub_hire_qtyv61),'C'),0) from tblitemtran x where x.booking_no_v32 = i.booking_no_v32 and x.sub_hire_qtyV61 > 0) as [Shortages]
from tblItemtran I
left outer join tblbookings B on B.booking_no = i.booking_no_v32
Where b.ddate >= '2017-07-01'
Group by B.hire_price,I.booking_no_v32, B.Showname
Order by I.booking_no_v32