select area.compname,cust.province,cust.cust_name,tb1.OrderNum,tb1.ItemNoNum,tb1.NoPick,count(1) as Overweight,Convert(decimal(5,2),(count(1)-(isnull(sum(case when esd.ex_no is null and bcs.service_no is null then 0 else 1 end), 0)))*100.00/count(1)) as UpPer,
          /*15*/
                (select sum(case when (Promises IS not null and Promises <>'') and OrderDate between getdate()-15 and getdate() then 1 else 0 end) from FA_WMSDB.ServicePos.dbo.SnService where customer_id=tb1.customer_id)
               /*15*/
           
           
             
          from #tb tb1
          join BC_customer cust on tb1.customer_id=cust.customer_id
          join acercomp area on area.compcode=cust.compcode
          left join FA_WMSDB.ServicePos.dbo.SnService tb2 on tb1.customer_id=tb2.customer_id and (tb2.Promises<>'' and tb2.Promises is not null)
          left join [BC_serianos] bcs on bcs.serial_number=tb2.SerialNo
          left join [exclude_serialno_detail] esd on esd.serialno = tb2.SerialNo      
          group by area.compname,cust.province,cust.cust_name,tb1.OrderNum,tb1.ItemNoNum,tb1.NoPick,tb1.customer_id

SELECT  area.compname , cust.province , cust.cust_name , tb1.OrderNum , tb1.ItemNoNum , tb1.NoPick , COUNT(1) AS Overweight ,
        CONVERT(DECIMAL(5 , 2) , (COUNT(1)-(ISNULL(SUM(CASE WHEN esd.ex_no IS NULL
                                                                 AND bcs.service_no IS NULL THEN 0
                                                            ELSE 1
                                                       END) , 0)))*100.00/COUNT(1)) AS UpPer ,
          /*15*/ 
          xx.oo
                 
               /*15*/
FROM    #tb tb1
JOIN    BC_customer cust
ON      tb1.customer_id = cust.customer_id
JOIN    acercomp area
ON      area.compcode = cust.compcode
LEFT JOIN FA_WMSDB.ServicePos.dbo.SnService tb2
ON      tb1.customer_id = tb2.customer_id
        AND (
             tb2.Promises <> ''
             AND tb2.Promises IS NOT NULL
            )
LEFT JOIN [BC_serianos] bcs
ON      bcs.serial_number = tb2.SerialNo
LEFT JOIN [exclude_serialno_detail] esd
ON      esd.serialno = tb2.SerialNo
LEFT JOIN 
(
 SELECT  oo=SUM(CASE WHEN (
                                         Promises IS NOT NULL
                                         AND Promises <> ''
                                        )
                                        AND OrderDate BETWEEN GETDATE()-15 AND GETDATE() THEN 1
                                   ELSE 0
                              END)
                  FROM    FA_WMSDB.ServicePos.dbo.SnService
) xx 
ON xx.customer_id = tb1.customer_id
GROUP BY area.compname , cust.province , cust.cust_name , tb1.OrderNum , tb1.ItemNoNum , tb1.NoPick , tb1.customer_id