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 = 'Jan 1 2016'
SET @EndDate = 'Aug 14 2016'
SET @CustCode = 'CANTOR'
SELECT B.Booking_no AS [Code],
B.Organizationv6 AS [Organisation],
B.contact_nameV6,
V.VenueName AS [Venue Name],
B.dDate AS [Warehouse Out Date],
I.Invoice_cred_no AS [Invoice #],
I.InvDate AS [Invoice Date],
(SELECT TOP 1 S.InvStageName
FROM tblTermstages S
WHERE (T.ID = S.PayTermID)
AND (S.StageNo = I.StageNo)) AS [Invoice Stage Name],
CASE
WHEN I.Inv_cred_note = 0 THEN CAST((I.Invoice_amount) AS DECIMAL(9,2))
ELSE 0
END AS [Invoice Amount],
CASE
WHEN I.Inv_cred_note = 1 THEN CAST((I.credit_amount * -1) AS DECIMAL(9,2))
ELSE 0
END AS [Credit Amount],
(SELECT TOP 1 P.DateF
FROM tblPayment P
WHERE P.invoice_no = I.invoice_cred_no
ORDER BY DateF DESC) AS [Payment Date],
CASE
WHEN I.Inv_cred_note = 0 THEN CAST(
(SELECT SUM(P.amount)
FROM tblPayment P
WHERE P.invoice_no = I.invoice_cred_no) AS DECIMAL(9,2))
ELSE 0
END AS [Payment Amount],
CAST ((B.Price_Quoted) AS DECIMAL(9,2)) AS [Job Value],
B.last_operators AS [Booking Last Modified By],
B.conDate AS [Confirm Date],
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 [Status],
C.Address_l1V6 AS [Physical Address],
C.Address_l2V6 AS [Physical Adress Cont.],
C.Address_l3V6 [City],
C.StreetState AS [State],
C.post_code AS [Zip Code],
C.Phone1CountryCode,
C.Phone1AreaCode,
C.Phone1Digits AS [Phone Number],
C.emailaddress AS [Email],
C.webAddress AS [Website]
FROM tblbookings B
LEFT OUTER JOIN tblInvhead I ON I.Booking_no = B.Booking_no
LEFT OUTER JOIN tblvenues V ON V.id = B.VenueID
LEFT OUTER JOIN tblPayTerms T ON B.booking_no = T.Booking_no
LEFT OUTER JOIN tblCust C ON C.ID = B.CustID
WHERE B.dDate >= getdate() -- AND @CustCode = c.Customer_code /* Bookings with warehouse out date greater than today and customer code matches */
-- where B.dDate >= getdate() /* Bookings with warehouse out date greater than today */
/* If using this code it will show confirmed RENTAL bookings between the dates specified , remember to comment out the where statement above*/ -- WHERE B.BookingProgressStatus = 3
-- AND B.Booking_type_v32 = 0
-- AND (B.dDate BETWEEN CAST(@StartDate AS DATETIME) AND CAST(@EndDate AS DATETIME))
-- AND @CustCode = c.Customer_code
ORDER BY B.booking_no,
I.Invoice_cred_no