InboundTest.sql

SELECT a.serviceproviderid, 
       Sum(CASE 
             WHEN b.notecode = 'RE' THEN 1 
             ELSE 0 
           END)  AS RECount, 
       Sum (CASE 
              WHEN a.lastupdate >= Dateadd(day, -2 + CASE Datediff(day, 0, 
                                                          Getdate()) % 
                                                          7 
                                                       WHEN 0 THEN 1 
                                                       WHEN 5 THEN 2 
                                                       WHEN 6 THEN 1 
                                                       ELSE 0 
                                                     END, Getdate()) THEN 1 
              ELSE 0 
            END) AS InboundTest 
FROM   tagnet_appointmentspn_orders a 
       LEFT JOIN [dbo].[tagnet_appointmentspn_partnerordernotes] b 
              ON a.[partnerprimaryorderid] = b.[partnerprimaryorderid] 
WHERE  orderstate = 36 
GROUP  BY a.serviceproviderid 
ORDER  BY a.serviceproviderid