use kffprod
DECLARE @CUSTOMER VARCHAR(20) = '<<lccustno>>'
DECLARE @LOCATION VARCHAR(20) = '<<lclocation>>%'
DECLARE @ROUTE    VARCHAR(20) = '<<lcroute>>%'
DECLARE @SHIPTO   VARCHAR(20) = '<<lcshipto>>'
DECLARE @WKS      DATE        = '<<ldweekstartdate>>'

/*ON 11/25/2013 I added 
cf2.[Menu Days] as 'customer menu days', and

   LEFT JOIN [Kitchen Fresh Foods$Custom Fields - Customer]  cf2
		   on cf2.[Customer No_]    = @customer 

Because the proper menu days were not displaying in order summary for customers delivered from C-store routes

*/

SELECT loc2.[customer No_],
       loc2.[location no_] AS 'LOCATION',
	   loc2.[Route No_] AS 'ROUTE',
	   loc2.[name] AS 'LOCDESC',
      loc2.[status],      
	   loc2.[ship-to customer no_],
	   loc2.[ship-to code],
	   loc2.[Location Menu Days],
       cf2.[Menu Days] as 'customer menu days',
	   'Make Calc' = CASE loc2.[Make Calc]  WHEN '' THEN ISNULL(cf.[Make Calc Code],'') ELSE ISNULL(loc2.[Make Calc],'') END,
	   'Menu Days' = CASE loc2.[Menu Days]  WHEN '' THEN CASE ISNULL(cf.[Menu Days],'') WHEN '' THEN 'MTWRF' ELSE ISNULL(cf.[Menu Days],'') END ELSE ISNULL(loc2.[Menu Days],'') END,	   
	   Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,
	   'Total' = Sunday+Monday+Tuesday+Wednesday+Thursday+Friday+Saturday,
	   Sun,Mon,Tue,Wed,Thu,Fri,Sat
  FROM(
	SELECT loc1.[customer No_],
		   loc1.[location no_],
		   loc1.[Route No_],
		   loc1.[name],
         loc1.[status],
		   loc1.[ship-to customer no_],
		   loc1.[ship-to code],
		   loc1.[Location Menu Days],
		   loc1.[mon],loc1.[tue],loc1.[wed],loc1.[thu],loc1.[fri],'Sat'='','Sun'='',		   
		   'Make Calc' = CASE ISNULL(md.[Make Calc],'') WHEN '' THEN ISNULL(ST.[Make Calc Code],'') ELSE ISNULL(md.[Make Calc],'') END,
		   'Menu Days' = CASE ISNULL(md.[Menu Days],'') WHEN '' THEN ISNULL(ST.[Menu Days],'')      ELSE ISNULL(md.[Menu Days],'') END,
		   'Sunday' = 0,
		   'Monday' = monqty,
		   'Tuesday' = tueqty,
		   'Wednesday' = wedqty,
		   'Thursday' = thuqty,
		   'Friday' = friqty,
		   'Saturday' = 0		   
	  FROM ( SELECT loc.[customer No_],
					loc.[location no_],
					loc.[Route No_],
					loc.[name],
               loc.[status],
					'ship-to customer No_' = CASE rt.[ship-to customer No_] WHEN ''THEN LOC.[Customer No_] ELSE rt.[ship-to customer No_] END,
					rt.[ship-to code],
                    CASE loc.[Monday Menu]    WHEN 1 THEN 'M' ELSE '' END +
                    CASE loc.[Tuesday Menu]   WHEN 1 THEN 'T' ELSE '' END +
                    CASE loc.[Wednesday Menu] WHEN 1 THEN 'W' ELSE '' END +
                    CASE loc.[Thursday Menu]  WHEN 1 THEN 'R' ELSE '' END +
                    CASE loc.[Friday Menu]    WHEN 1 THEN 'F' ELSE '' END AS 'Location Menu Days',
					'Monqty' = isnull((select SUM(qty) from (SELECT H.[Document No_],SUM(l.[qty_ ordered]) qty FROM [Kitchen Fresh Foods$Sales Header Import] H JOIN [Kitchen Fresh Foods$Sales Line Import] L ON H.[Document No_] = L.[Document No_] AND H.[Document Type]=L.[Document Type] WHERE [Document Date] = DATEADD(D,1,@WKS) AND H.[Sell-To Customer No_] =  loc.[Customer No_] and [Customer Location] = loc.[Location No_] and H.[Document Type]= 1 group by h.[Document No_])D where qty>0),0),
					'Tueqty' = isnull((select sum(qty) from (SELECT H.[Document No_],SUM(l.[qty_ ordered]) qty FROM [Kitchen Fresh Foods$Sales Header Import] H JOIN [Kitchen Fresh Foods$Sales Line Import] L ON H.[Document No_] = L.[Document No_] AND H.[Document Type]=L.[Document Type] WHERE [Document Date] = DATEADD(D,2,@WKS) AND H.[Sell-To Customer No_] =  loc.[Customer No_] and [Customer Location] = loc.[Location No_] and H.[Document Type]= 1 group by h.[Document No_])D where qty>0),0),
					'Wedqty' = isnull((select sum(qty) from (SELECT H.[Document No_],SUM(l.[qty_ ordered]) qty FROM [Kitchen Fresh Foods$Sales Header Import] H JOIN [Kitchen Fresh Foods$Sales Line Import] L ON H.[Document No_] = L.[Document No_] AND H.[Document Type]=L.[Document Type] WHERE [Document Date] = DATEADD(D,3,@WKS) AND H.[Sell-To Customer No_] =  loc.[Customer No_] and [Customer Location] = loc.[Location No_] and H.[Document Type]= 1 group by h.[Document No_])D where qty>0),0),
					'Thuqty' = isnull((select sum(qty) from (SELECT H.[Document No_],SUM(l.[qty_ ordered]) qty FROM [Kitchen Fresh Foods$Sales Header Import] H JOIN [Kitchen Fresh Foods$Sales Line Import] L ON H.[Document No_] = L.[Document No_] AND H.[Document Type]=L.[Document Type] WHERE [Document Date] = DATEADD(D,4,@WKS) AND H.[Sell-To Customer No_] =  loc.[Customer No_] and [Customer Location] = loc.[Location No_] and H.[Document Type]= 1 group by h.[Document No_])D where qty>0),0),
					'Friqty' = isnull((select sum(qty) from (SELECT H.[Document No_],SUM(l.[qty_ ordered]) qty FROM [Kitchen Fresh Foods$Sales Header Import] H JOIN [Kitchen Fresh Foods$Sales Line Import] L ON H.[Document No_] = L.[Document No_] AND H.[Document Type]=L.[Document Type] WHERE [Document Date] = DATEADD(D,5,@WKS) AND H.[Sell-To Customer No_] =  loc.[Customer No_] and [Customer Location] = loc.[Location No_] and H.[Document Type]= 1 group by h.[Document No_])D where qty>0),0),
					'Mon' = isnull((SELECT TOP 1 [Document No_] FROM (SELECT H.[Document No_],SUM(l.[qty_ ordered]) qty FROM [Kitchen Fresh Foods$Sales Header Import] H JOIN [Kitchen Fresh Foods$Sales Line Import] L ON H.[Document No_] = L.[Document No_] AND H.[Document Type]=L.[Document Type] WHERE [Document Date] = DATEADD(D,1,@WKS) AND H.[Sell-To Customer No_] =  loc.[Customer No_] and [Customer Location] = loc.[Location No_] and H.[Document Type]= 1 group by h.[Document No_])D where qty>0),''),
					'Tue' = isnull((SELECT TOP 1 [Document No_] FROM (SELECT H.[Document No_],SUM(l.[qty_ ordered]) qty FROM [Kitchen Fresh Foods$Sales Header Import] H JOIN [Kitchen Fresh Foods$Sales Line Import] L ON H.[Document No_] = L.[Document No_] AND H.[Document Type]=L.[Document Type] WHERE [Document Date] = DATEADD(D,2,@WKS) AND H.[Sell-To Customer No_] =  loc.[Customer No_] and [Customer Location] = loc.[Location No_] and H.[Document Type]= 1 group by h.[Document No_])D where qty>0),''),
					'Wed' = isnull((SELECT TOP 1 [Document No_] FROM (SELECT H.[Document No_],SUM(l.[qty_ ordered]) qty FROM [Kitchen Fresh Foods$Sales Header Import] H JOIN [Kitchen Fresh Foods$Sales Line Import] L ON H.[Document No_] = L.[Document No_] AND H.[Document Type]=L.[Document Type] WHERE [Document Date] = DATEADD(D,3,@WKS) AND H.[Sell-To Customer No_] =  loc.[Customer No_] and [Customer Location] = loc.[Location No_] and H.[Document Type]= 1 group by h.[Document No_])D where qty>0),''),
					'Thu' = isnull((SELECT TOP 1 [Document No_] FROM (SELECT H.[Document No_],SUM(l.[qty_ ordered]) qty FROM [Kitchen Fresh Foods$Sales Header Import] H JOIN [Kitchen Fresh Foods$Sales Line Import] L ON H.[Document No_] = L.[Document No_] AND H.[Document Type]=L.[Document Type] WHERE [Document Date] = DATEADD(D,4,@WKS) AND H.[Sell-To Customer No_] =  loc.[Customer No_] and [Customer Location] = loc.[Location No_] and H.[Document Type]= 1 group by h.[Document No_])D where qty>0),''),
					'Fri' = isnull((SELECT TOP 1 [Document No_] FROM (SELECT H.[Document No_],SUM(l.[qty_ ordered]) qty FROM [Kitchen Fresh Foods$Sales Header Import] H JOIN [Kitchen Fresh Foods$Sales Line Import] L ON H.[Document No_] = L.[Document No_] AND H.[Document Type]=L.[Document Type] WHERE [Document Date] = DATEADD(D,5,@WKS) AND H.[Sell-To Customer No_] =  loc.[Customer No_] and [Customer Location] = loc.[Location No_] and H.[Document Type]= 1 group by h.[Document No_])D where qty>0),'')
			   FROM [Kitchen Fresh Foods$customer detail order location] loc
			   JOIN [Kitchen Fresh Foods$customer route] rt 
				 ON RT.[Route No_] = LOC.[Route No_] AND 
					RT.[Customer No_] = LOC.[Customer No_]      
			  WHERE loc.[customer No_] LIKE @CUSTOMER AND
					loc.[Route No_]      LIKE @ROUTE AND
					loc.[Location No_]   LIKE @LOCATION 
					AND rt.[ship-to code]   LIKE @SHIPTO 

			) LOC1
	  LEFT JOIN [Kitchen Fresh Foods$Menu Days] md 
		   on md.[Customer No_]    = loc1.[ship-to customer No_] and 
			  md.[Ship-to Code]    = loc1.[Ship-to Code] and
			  md.[Week Start Date] = @WKS
      LEFT JOIN [Kitchen Fresh Foods$User-Defined Fields - Ship-To] st
		   on st.[Customer No_]    = loc1.[ship-to customer No_] and 
			  st.[Ship-to Code]    = loc1.[Ship-to Code]
   ) loc2
   LEFT JOIN [Kitchen Fresh Foods$Custom Fields - Customer]  cf
		   on cf.[Customer No_]    = loc2.[ship-to customer No_] 
   LEFT JOIN [Kitchen Fresh Foods$Custom Fields - Customer]  cf2
		   on cf2.[Customer No_]    = @customer 
   ORDER BY loc2.[customer No_],[route no_],[location no_]

