/* 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 AS [Booking #],
v.showname AS [Showname],
v.organizationv6 AS
[Organization],
s.salesperson_name AS
[SalesPerson],
Cast(v.inv_date AS DATE) AS
[Invoice Date],
v.invoice_no AS [Invoice #],
Cast(v.price_quoted - v.tax1 - v.tax2 AS DECIMAL (14, 2)) AS
[Before Tax Amount],
Cast(v.tax1 AS DECIMAL(9, 2)) AS [Tax 1],
Cast(v.tax2 AS DECIMAL(9, 2)) AS [Tax 2],
Cast(v.price_quoted AS DECIMAL(14, 2)) AS
[Price Quoted],
Isnull(Cast(p.datef AS DATE), '') AS
[Payment Date],
Cast(p.amount AS DECIMAL(14, 2)) AS
[Payment Amount],
Isnull(p.comment_line, '') AS
[Payment Memo]
FROM vwbookandhist v
LEFT OUTER JOIN tblsalesper S
ON s.salesperson_code = v.salesperson
LEFT OUTER JOIN tblinvhead I
ON I.invoice_cred_no = v.invoice_no
LEFT OUTER JOIN tblpayment P
ON P.invoice_no = i.invoice_cred_no
WHERE ( ( v.inv_date >= Getdate() - @DaySpan )
OR ( p.datef >= Getdate() - @DaySpan ) )
ORDER BY v.organizationv6