USE KFFPROD

DECLARE @CUSTOMER      VARCHAR(20) = '<<lccustno>>'
DECLARE @ROUTE         VARCHAR(20) = '<<lcroute>>%'
DECLARE @LOCATION      VARCHAR(20) = '<<lclocation>>%'
DECLARE @WS            DATE        = '<<ldweekstartdate>>'

SELECT 'item' = l.[item no_],
       'itmdesc' = i.[description],
	   'Monday' = SUM(CASE WHEN h.[Document Date] = DATEADD(D,1,@WS) THEN l.[qty_ ordered] ELSE 0 END), 
	   'Tuesday' = SUM(CASE WHEN h.[Document Date] = DATEADD(D,2,@WS) THEN l.[qty_ ordered] ELSE 0 END), 
	   'Wednesday' = SUM(CASE WHEN h.[Document Date] = DATEADD(D,3,@WS) THEN l.[qty_ ordered] ELSE 0 END), 
	   'Thursday' = SUM(CASE WHEN h.[Document Date] = DATEADD(D,4,@WS) THEN l.[qty_ ordered] ELSE 0 END), 
	   'Friday' = SUM(CASE WHEN h.[Document Date] = DATEADD(D,5,@WS) THEN l.[qty_ ordered] ELSE 0 END),
	   'TOTAL'  = SUM(CASE WHEN h.[Document Date] BETWEEN DATEADD(D,1,@WS) AND DATEADD(D,5,@WS) THEN l.[qty_ ordered] ELSE 0 END),
	   'STATUS' = 0
FROM [Kitchen Fresh Foods$Sales Header Import] h 
JOIN [Kitchen Fresh Foods$Sales line Import] l ON h.[Document Type] = l.[Document Type] AND h.[Document No_] = l.[Document No_] 
join [Kitchen Fresh Foods$Item] i on i.[No_] = l.[Item No_]
WHERE h.[Sell-To Customer No_] = @CUSTOMER  AND
      H.[Customer Route] LIKE @ROUTE AND
      H.[Customer Location] LIKE @LOCATION AND      
      h.[Document Date] BETWEEN DATEADD(D,1,@WS) AND DATEADD(D,5,@WS) and
      l.[Qty_ Ordered] > 0
      GROUP BY l.[Item No_],i.[Description]