SELECT sh.[ServiceDate],
       si.[ServiceUID],
       DATENAME(dw,sh.[ServiceDate]) AS WeekOfDay,
       sf.ServiceFrequencyDescription,
       si.[Quantity],
       shwa.[Weight],
       et.description,
  CASE
        WHEN sc.[SvcCode] like '%BULK%' THEN 'BULK'
        WHEN sc.[SvcCode] = 'GARAGESVC' THEN 'GARAGE'
        ELSE sc.[SvcCode]
        END AS serviceCode,

  CASE
        WHEN (sc.LineOfBusiness) = 'FRONTLOAD' AND (sc.Material) IN ('CARDBOARD','COMINGELED', 'PAPER', 'SINGLESTRM', 'CARDB') THEN 'FRONTLOAD RECYCLING'
        ELSE (sc.LineOfBusiness)
        END AS lineOfBusiness,

                     CASE
         WHEN  (sf.ServiceFrequencyDescription) = 'Weekly' THEN '1'
         WHEN  (sf.ServiceFrequencyDescription) = '2 Times Per Week' THEN '2'
         WHEN  (sf.ServiceFrequencyDescription) = '3 Times Per Week' THEN '3'
         WHEN  (sf.ServiceFrequencyDescription) = '4 Times Per Week' THEN '4'
         WHEN  (sf.ServiceFrequencyDescription) = '5 Times Per Week' THEN '5'
         WHEN  (sf.ServiceFrequencyDescription) = '6 Times Per Week'THEN '6'
         WHEN  (sf.ServiceFrequencyDescription) is null THEN  '0'
         WHEN  (sf.ServiceFrequencyDescription) = 'ON Call' THEN '0'
         WHEN  (sf.ServiceFrequencyDescription) = 'Monthly' then '.25'
         WHEN  (sf.ServiceFrequencyDescription) = 'Every 2 Weeks' THEN '.50'
         WHEN  (sf.ServiceFrequencyDescription) = 'Every 4 Weeks' THEN '.25'
         ELSE  (sf.ServiceFrequencyDescription)
         END AS ServiceFrequency,

         CASE      when (et.Description) like '%comp%' OR (et.Description) like '%VIP%' and (sc.Material) != 'CARDB' and (sc.Material) !=  'CARDBOARD' then (ec.Volume)*3
         WHEN (et.Description) like '%comp%' OR (et.Description) like '%VIP%' and (sc.Material) in ('CARDB', 'CARDBOARD') then (ec.Volume)*2
         ELSE (ec.Volume)
         END AS CanSize
  INTO #A
  FROM [dbo].[ServiceInfo] si
  JOIN [dbo].[ServiceHistory] sh ON sh.ServiceInfoUID = si.ServiceInfoUID
  JOIN [dbo].[EquipmentType] et ON si.EquipmentType = et.EquipmentType
  JOIN [dbo].[EquipmentCategory] ec ON et.EquipmentCategory = ec.EquipmentCategory
  JOIN [dbo].[ServiceCode] sc ON sc.SvcCode = si.SvcCode
  JOIN [dbo].[ServiceFrequency] sf ON sf.ServiceFrequency = si.SvcFrequency
  LEFT JOIN [dbo].[ServiceHistoryWeightAudit] shwa ON sh.ServiceHistoryUID = shwa.ServiceHistoryUID


WHERE sh.ServiceDate in ('2016-12-12 00:00:00.000','2017-1-2 00:00:00.000','2016-12-19 00:00:00.000', '2016-12-24 00:00:00.000')
and  si.[ServiceUID]  = '4D0F3E62-3938-4A1F-9B96-0C991BD52DB8'and (shwa.Weight is not null) and (shwa.Weight> 0.00)

 select  *
 from #A


SELECT ServiceUID, ServiceDate, weight, quantity,
(DATENAME(dw, convert(int, ServiceDate))) as day_week,
--convert(nvarchar(200),count((DATENAME(dw,Sh.ServiceDate)))),
convert(nvarchar(20),count (DISTINCT(ServiceDate))) as day_count,
SUM(CASE WHEN [ServiceFrequencyDescription] = 'Daily' then 0 ELSE Weight end)  TotaDateWeight,
CanSize * Quantity * ServiceFrequency as YardPerWeek

from #A
WHERE [ServiceUID] = '4D0F3E62-3938-4A1F-9B96-0C991BD52DB8'
GROUP BY
ServiceUID, ServiceDate,weight, quantity, cansize, ServiceFrequency,
(DATENAME(dw, convert(int,ServiceDate)))
order by 1

drop table #A