How to write multiple sum case statement . Something wrong with my case statement

Question

Hi @MelissaMa-MSFT

  /**** Sample Data ****/  
            Create Table Memberships  
            (  
            MembershipID int,  
            ActiveStart date,  
            ActiveEnd date  
            )  
            Insert into Memberships(MembershipID,ActiveStart,ActiveEnd)  
            Values  
            (186737,2020-10-06,2018-11-06),  
            (186737,2020-09-29,2020-12-30),  
            (186737,2020-03-03,2018-11-06),  
            (186737,2020-10-25,2020-11-02),  
            (186737,2020-10-20,2020-12-06),  
            (186737,2020-10-22,2020-12-06),  
            (186737,2020-10-16,2020-12-06),  
            (186737,2020-10-11,2020-12-06),  
            (186737,2020-10-06,2020-12-06);`  
              
    /***** Query *****/          
                Declare @beginDate int = 0;   
                Declare @pendDate int = 1;  
                  
                  
                Create Table #TEMPCancel(WeekDates date,MembershipID int,ActiveStart date,ActiveEnd date,[Status] varchar(100))  
                While (@beginDate < 21 and @pendDate < 22)  
                Begin   
                 Insert Into #TEMPCancel  
                 Select DATEADD(WEEK, -1 * @pendDate ,GETDATE()) ,M.MembershipID,  
                  
                 M.ActiveStart,M.ActiveEnd,   
                 /**** Something wrong with this case statement****/  
                  sum(case  
                    
                  when (M.ActiveEnd BETWEEN DATEADD(WEEK, -1 * @pendDate  ,GETDATE()) and DATEADD(WEEK, -1 * @beginDate ,GETDATE())) then 'CM'  
                  when (M.ActiveStart < DATEADD(WEEK, -1 * @pendDate ,GETDATE()))  AND (M.ActiveEnd > DATEADD(WEEK, -1 * @beginDate ,GETDATE())  or M.ActiveEnd is null) then 'LM'  
                  when (M.ActiveStart BETWEEN DATEADD(WEEK, -1 * @pendDate  ,GETDATE()) and DATEADD(WEEK, -1 * @beginDate ,GETDATE()))  then 'JN'  
                  else 0 end)  
                  
                  
                 From AX.Memberships M  
                   
                 Group by   
                 M.MembershipID, M.ActiveEnd,M.ActiveStart  
                  
                 Set @beginDate +=1  
                 Set @pendDate += 1  
                End  
                Select * from #TEMPCancel  
              
              
          
      


  
  

Answer 1

Hi @RXR ，

Varchar type cannot use sum function, JM, LM
, JN are varchar types, so I removed sum. Please check if it is your expected output, if not, please share your expected output with us (image or data is fine).In addition, your status column is of varchar type, and 0 cannot be inserted, so I changed 0 to null.
Please check:

Create Table Memberships  
(  
MembershipID int,  
ActiveStart date,  
ActiveEnd date  
)  
Insert into Memberships(MembershipID,ActiveStart,ActiveEnd)  
Values  
(186737,'2020-10-06','2018-11-06'),  
(186737,'2020-09-29','2020-12-30'),  
(186737,'2020-03-03','2018-11-06'),  
(186737,'2020-10-25','2020-11-02'),  
(186737,'2020-10-20','2020-12-06'),  
(186737,'2020-10-22','2020-12-06'),  
(186737,'2020-10-16','2020-12-06'),  
(186737,'2020-10-11','2020-12-06'),  
(186737,'2020-10-06','2020-12-06');  
create Table #TEMPCancel(WeekDates date,MembershipID int,ActiveStart date,  
ActiveEnd date,[Status] varchar(100))     
    
       
Declare @beginDate int = 0;   
Declare @pendDate int = 1;  
While (@beginDate < 21 and @pendDate < 22)  
Begin   
Insert Into #TEMPCancel  
Select DATEADD(WEEK, -1 * @pendDate ,GETDATE()) ,M.MembershipID,                    
M.ActiveStart,M.ActiveEnd,   
case  
when (M.ActiveEnd BETWEEN DATEADD(WEEK, -1 * @pendDate  ,GETDATE()) and DATEADD(WEEK, -1 * @beginDate ,GETDATE())) then 'CM'  
when (M.ActiveStart < DATEADD(WEEK, -1 * @pendDate ,GETDATE()))  AND (M.ActiveEnd > DATEADD(WEEK, -1 * @beginDate ,GETDATE())  or M.ActiveEnd is null) then 'LM'  
when (M.ActiveStart BETWEEN DATEADD(WEEK, -1 * @pendDate  ,GETDATE()) and DATEADD(WEEK, -1 * @beginDate ,GETDATE()))  then 'JN'  
else null end  
From Memberships M  
Group by M.MembershipID, M.ActiveEnd,M.ActiveStart  
Set @beginDate +=1  
Set @pendDate += 1  
End  
Select * from #TEMPCancel  
  
drop Table #TEMPCancel  
drop Table Memberships  

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our [documentationto][2] enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Maybe remove sum, keep case statement but replace else 0 with else NULL or with else ''.