逻辑难题，求大神帮助，最后的分数献下，做完好回家过年

逻辑难题，求大神帮助，最后的分数献上，做完好回家过年啊
存储过程是这样的

USE DP6_Siemens
GO

ALTER PROCEDURE [dbo].[Siemens_ReRemind_XS]

    @BeginTime VARCHAR(10) ,
    @EndTime VARCHAR(10) ,
    @Corpid INT
AS 
    BEGIN
CREATE TABLE #aa(memberId INT ,stordid VARCHAR(500))
INSERT INTO #aa
SELECT B.MemberId,LEFT(UserList,LEN(UserList)-1) AS NIMEI FROM (
SELECT AA.MemberId,
    (SELECT CAST(StoreId AS NVARCHAR(max))+N'店,' FROM dbo.DP_MemberStore  WHERE MemberId=AA.MemberId  FOR XML PATH('')) AS UserList
FROM dbo.DP_MemberStore AA 
GROUP BY AA.MemberId
) B
--要发出的信息
     INSERT INTO DP_SmsOutQueue(CorpId , Mobile , [Content] , SendChannel , AttemptTimes , Operator)
        SELECT  @Corpid AS Corpid ,
                dbo.GetMobileByMemberId(a.MemberId) AS Mobile ,
                '西门子dp平台提醒：你好，你还有'+CAST(ISNULL(a.AA,0)AS VARCHAR(10))+'家门店未上报上月的进货额，请在今天内上报，多谢合作！未上报门店为：'+CAST(ISNULL(a.CC,0)AS VARCHAR(max))+ '。' AS CONTENT ,
                dbo.GetSmsChannelByMobile(dbo.GetMobileByMemberId(a.MemberId),@Corpid) AS SendChannel ,
                0 AS AttemptTimes ,
                'Siemens_ReRemind_XS' AS Operator
                
 --发出信息的参数 
        --发出信息对应的会员ID              
      FROM (SELECT tt.MemberId,                     
        --未上报XS会员对应为上报的门店数
      COUNT (DISTINCT CASE WHEN tt.MemberId like '%1%' THEN tt.storeId end )  AS AA,   
        --列出未上报的门店ID
    #aa.stordid AS CC       
      
              FROM dbo.DP_MemberStore tt
               LEFT JOIN #aa ON tt.MemberId = #aa.memberId  
                LEFT JOIN dbo.DP_Members pp ON tt.CorpId = pp.CorpId AND tt.MemberId = pp.MemberId 
              WHERE tt.CorpId=@Corpid AND pp.Status=2   AND pp.RoleId=7 
--排除上报了XS任务的会员              
              AND NOT EXISTS ( SELECT TOP 1 
 
                                        1
                                 FROM   dbo.DP_CommonSmsEntries DCSE
                                 WHERE  DCSE.RecvTime &gt;= @BeginTime
                                        AND DCSE.RecvTime &lt; @EndTime
                                        AND DCSE.CorpId = @Corpid
                                        AND DCSE.Instruction = 'XS'
                                        AND DCSE.MemberId = tt.MemberId ) 
                                        
--排除休假会员              
                                        AND NOT EXISTS ( SELECT TOP 1
                                        1
                                 FROM   DP_SysAttendanceEntries dsae
                                 WHERE  dsae.AttendDate &gt;= @BeginTime
                                        AND dsae.AttendDate &lt; @EndTime
                                        AND dsae.CorpId = @Corpid
                                        AND tt.MemberId = dsae.MemberId 
 
                                        AND dsae.TypeId = 2 )
                                        GROUP BY  tt.MemberId ,#aa.stordid
                                                            
                     )a          
     
  DROP TABLE #aa
    END



GO

运行后结果是：

但逻辑有误，如果MemberId对应有多个StoreId的话，那MemberId就算有些店没上报，也不会发出短信提示，就像这个逻辑难题，求大神帮助，最后的分数献下，做完好回家过年

1400002有多间门店，但是不会发送短信，求帮助，做完就回家过好年了，求大神帮助
[解决办法]
求耐心帝，说实话，我是没耐心看完这么多subselect。

如果步骤很长，那就一步一步来，先select基础信息进临时表，然后一步一步update.
中间可以随时select临时表信息，进行核对，信息是否则正确。

现在这么写的代码，让别人看，真是累死人不偿命。自己debug也麻烦。
[解决办法]
有多间门店有1间上报了，导致系统误以为已经上报？
判断基准是什么：or还是and

逻辑难题求大神帮助最后的分数献下

热点推荐