/* This query will show a count of all given customer bookings, current and archived, that have a warehouse out date within the date range provided */
DECLARE @StartDate AS VARCHAR(20)
DECLARE @EndDate AS VARCHAR(20)
DECLARE @CustCode AS VARCHAR(8)
SET @StartDate = '2019-07-01'
SET @EndDate = '2019-07-31'
SET @CustCode = 'WATBUS'
SELECT c.customer_code AS [Customer Code],
v.organizationv6 AS [Company Name],
CASE
WHEN V.bookingprogressstatus = 0 THEN 'Quote'
WHEN V.bookingprogressstatus = 1 THEN 'Light Pencil'
WHEN V.bookingprogressstatus = 2 THEN 'Heavy Pencil'
WHEN V.bookingprogressstatus = 3 THEN 'Confirmed'
ELSE 'Cancelled'
END AS [Status],
Count(v.booking_no) AS [# Bookings]
FROM vwbookandhist v
LEFT OUTER JOIN tblcust c
ON c.id = v.custid
WHERE ( v.ddate >= @StartDate
AND v.ddate <= @EndDate )
AND c.customer_code = @CustCode
GROUP BY c.customer_code,
v.organizationv6,
v.bookingprogressstatus