Bookings Entered Within Last 7 Days
Last Modified on 18/07/2017 3:59 pm EDT
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'
WHEN B.BookingProgressStatus = 1 THEN 'Light Pencil'
WHEN B.BookingProgressStatus = 2 THEN 'Heavy Pencil'
WHEN B.BookingProgressStatus = 3 THEN 'Confirmed'
ELSE 'Cancelled'
END AS [Progress Status],
B.showName AS [Show Name],
V.VenueName AS Venue,
B.price_quoted AS Revenue,
CASE
WHEN (B.Booking_type_v32 = 6
OR B.Booking_type_v32 = 7) THEN CAST(B.Sales_discount_rate AS DECIMAL (6,2))
ELSE CAST(B.Discount_rate AS DECIMAL(6,2))
END AS [Disc %],
CASE
WHEN B.Booking_type_v32 = 6 THEN CAST(((b.sales_amount - B.sales_undisc_amount) * (b.sales_discount_rate /100)) AS DECIMAL (9,2))
ELSE CAST(((b.hire_price - B.un_disc_amount) * (b.discount_rate/100)) AS DECIMAL(9,2))
END AS [Discount Amt],
O.FirstName AS [Operator First],
O.LastName AS [Operator Last],
B.Salesperson,
B.EntryDate AS [Booking Created],
B.ConDate AS [Confirmed Date],
B.Inv_date AS [Invoiced Date],
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'
WHEN B.booking_type_v32 = 3 THEN 'Rental Quote Monthly'
WHEN B.booking_type_v32 = 4 THEN 'Rental Monthly'
WHEN B.booking_type_v32 = 5 THEN 'Sub Rental'
WHEN B.booking_type_v32 = 6 THEN 'Sale'
WHEN B.booking_type_v32 = 7 THEN 'Sales Quote'
WHEN B.booking_type_v32 = 11 THEN 'Location Transfer'
WHEN B.booking_type_v32 = 13 THEN 'Sundry Transfer'
WHEN B.booking_type_v32 = 14 THEN 'Rental 7'
WHEN B.booking_type_v32 = 15 THEN 'Rental 14'
WHEN B.booking_type_v32 = 16 THEN 'Rental 28'
WHEN B.booking_type_v32 = 17 THEN 'Rental Quarterly'
WHEN B.booking_type_v32 = 18 THEN 'Rental Quote 7'
WHEN B.booking_type_v32 = 19 THEN 'rental_quote_14'
WHEN B.booking_type_v32 = 20 THEN 'Rental Quote 28'
WHEN B.booking_type_v32 = 21 THEN 'Rental Quote Quarterly'
ELSE ''
END AS TYPE,
CASE
WHEN B.status = 0 THEN 'Booked'
WHEN B.status = 1 THEN 'Checked Out'
WHEN B.status = 2 THEN 'Returned'
ELSE ''
END AS State,
B.invoiced,
B.invoice_no
FROM dbo.tblbookings AS B
LEFT OUTER JOIN dbo.tblDivlist AS D ON B.division = D.div_number
LEFT OUTER JOIN dbo.tblOperators AS O ON B.operatorsID = O.ID
LEFT OUTER JOIN dbo.tblVenues AS V ON B.VenueID = V.ID
WHERE CONVERT(varchar(12),b.entrydate, 112) >= CONVERT(varchar(12),GETDATE()-7, 112)