ALTER PROCEDURE [dbo].[ASP_Weekly_Lift_Report]
@Voyage_Start Varchar(3) = Null,
@Voyage_End Varchar(3) = Null,
@Sales_Rep Varchar(25) = Null,
@Service Varchar(4) = Null

AS
SET @Voyage_Start = IsNull(@Voyage_Start, '%')
SET @Voyage_End = IsNull(@Voyage_End, '%')
SET @Sales_Rep = IsNull(@Sales_Rep, '%')
SET @Service = IsNull(@Service, '%')

-- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT dbo.tbl_Lift_Report_PayorList.[Rep Name], dbo.tbl_Lift_Report_PayorList.Parent, dbo.tbl_Lift_Report_PayorList.Business_Type, dbo.tbl_Lift_Report_PayorList.PayorID, dbo.tbl_Lift_Report_PayorList.Payor, dbo.tbl_Lift_Report_PayorList.[Payor Name - ID], dbo.tbl_Lift_Report_PayorList.[Booking Payor], dbo.tbl_Lift_Report_PayorList.ShipperID, dbo.tbl_Lift_Report_PayorList.Shipper, dbo.tbl_Lift_Report_PayorList.ConsigneeID, dbo.tbl_Lift_Report_PayorList.Consignee, dbo.tbl_Lift_Report_PayorList.[Service], dbo.tbl_Lift_Report_PayorList.[Svc Desc], dbo.tbl_Lift_Report_PayorList.Vessel, dbo.tbl_Lift_Report_PayorList.Voyage, dbo.tbl_Lift_Report_PayorList.[Voyage No], dbo.tbl_Lift_Report_PayorList.[Cargo Type], dbo.tbl_Lift_Report_PayorList.Booking, dbo.tbl_Lift_Report_PayorList.[Profit Year], dbo.tbl_Lift_Report_PayorList.[Quarter No], dbo.tbl_Lift_Report_PayorList.Quarter, dbo.tbl_Lift_Report_PayorList.[Qtr-Year], dbo.tbl_Lift_Report_PayorList.Month, dbo.tbl_Lift_Report_PayorList.[Month No], dbo.tbl_Lift_Report_PayorList.[Month-Year], dbo.tbl_Lift_Report_PayorList.[Week No], dbo.tbl_Lift_Report_PayorList.[Week-Year], dbo.tbl_CRM_Lift_Report1.[Vehicle FEUs] AS [CY Veh FEUs Wk], dbo.tbl_CRM_Lift_Report1.[Vehicle Count] AS [CY Veh Count Wk], dbo.tbl_CRM_Lift_Report1.[Trailer FEUs] AS [CT Trlr FEUs Wk], dbo.tbl_CRM_Lift_Report1.[Total_FEU/Count] AS [CY Total FEU/Count Wk], dbo.tbl_CRM_Lift_Report1.[Booking Count] AS [CY Booking Count Wk], dbo.tbl_CRM_Lift_Report1.[Gross Less Fuel] AS [CY Gross less Fuel Wk], tbl_CRM_Lift_Report1_3.[Total_FEU/Count] AS [PY1 Total_FEU/Count Wk], tbl_CRM_Lift_Report1_3.[Gross Less Fuel] AS [PY1 Gross Less Fuel Wk], tbl_CRM_Lift_Report1_1.[Vehicle FEUs] AS [PY1 Veh FEUs], tbl_CRM_Lift_Report1_1.[Vehicle Count] AS [PY1 Veh Count], tbl_CRM_Lift_Report1_1.[Trailer FEUs] AS [PY1 Trailer FEUs], tbl_CRM_Lift_Report1_1.[Total_FEU/Count] AS [PY1 Total_FEU/Count], tbl_CRM_Lift_Report1_1.[Booking Count] AS [PY1 Booking Count], tbl_CRM_Lift_Report1_1.[Gross Less Fuel] AS [PY1 Gross Less Fuel], tbl_CRM_Lift_Report1_2.[Vehicle FEUs] AS [PY2 Veh FEUs], tbl_CRM_Lift_Report1_2.[Vehicle Count] AS [PY2 Veh Count], tbl_CRM_Lift_Report1_2.[Trailer FEUs] AS [PY2 Trailer FEUs], tbl_CRM_Lift_Report1_2.[Total_FEU/Count] AS [PY2 Total_FEU/Count], tbl_CRM_Lift_Report1_2.[Booking Count] AS [PY2 Booking Count], tbl_CRM_Lift_Report1_2.[Gross Less Fuel] AS [PY2 Gross Less Fuel]

FROM dbo.tbl_CRM_Lift_Report1 AS tbl_CRM_Lift_Report1_2 FULL OUTER JOIN dbo.tbl_Lift_Report_PayorList LEFT OUTER JOIN dbo.tbl_CRM_Lift_Report1 AS tbl_CRM_Lift_Report1_3 ON dbo.tbl_Lift_Report_PayorList.Booking = tbl_CRM_Lift_Report1_3.Booking LEFT OUTER JOIN dbo.tbl_CRM_Lift_Report1 ON dbo.tbl_Lift_Report_PayorList.Booking = dbo.tbl_CRM_Lift_Report1.Booking ON tbl_CRM_Lift_Report1_2.Booking = dbo.tbl_Lift_Report_PayorList.Booking FULL OUTER JOIN dbo.tbl_CRM_Lift_Report1 AS tbl_CRM_Lift_Report1_1 ON dbo.tbl_Lift_Report_PayorList.Booking = tbl_CRM_Lift_Report1_1.Booking

WHERE (dbo.tbl_CRM_Lift_Report1.[Profit Year] = DATEPART(yyyy, GETDATE()))
AND (tbl_CRM_Lift_Report1_3.[Profit Year] = DATEPART(yyyy, GETDATE()) - 1)
AND (dbo.tbl_Lift_Report_PayorList.[Rep Name] LIKE '%' + @Sales_Rep + '%')
AND (dbo.tbl_Lift_Report_PayorList.[Voyage No] BETWEEN @Voyage_Start AND @Voyage_End)
AND (dbo.tbl_Lift_Report_PayorList.[Service] Like '%'+@Service+'%')

OR (tbl_CRM_Lift_Report1_1.[Profit Year] = DATEPART(yyyy, GETDATE()) - 1)
OR (tbl_CRM_Lift_Report1_2.[Profit Year] = DATEPART(yyyy, GETDATE()) - 2)