declare  @strString VARCHAR(max)   = ''    -- 循环赋值内容的字符串

  declare @index int = 1  --起始下标

 declare @tableCount int = 0  --主表行数

 set   @tableCount = (select  count(1) from #TmpAll)

  WHILE(@index <= @tableCount)

begin

select @MaterialCode =  a.MaterialID,@SizeName = sz.SizeName,@StockID = InStockID, @CheckDate = InCheckDate  from #TmpAll  a 

inner join SD_Mat_Size sz on a.SizeID=sz.SizeID   

where id =  @index

   -- 7天

  set @strString = ''

    select  @seveDay = sum(qty) from #tableA_2  where MaterialCode  = '' + @MaterialCode +'' and SizeName  = '' + @SizeName +''   and  StockID =  '' + @StockID +''

  and CheckDate BETWEEN  CONVERT(varchar(100), @CheckDate, 20) and     DATEADD(dd,7,CONVERT(varchar(100),  @CheckDate, 20))  

    -- 14天

     select  @fourThen = sum(qty) from #tableA_2  where MaterialCode  = '' + @MaterialCode +'' and SizeName  = '' + @SizeName +''   and  StockID =  '' + @StockID +''

  and CheckDate BETWEEN  CONVERT(varchar(100), @CheckDate, 20) and     DATEADD(dd,14,CONVERT(varchar(100),  @CheckDate, 20)) 

      --21天

  select  @twentyOne = sum(qty) from #tableA_2  where MaterialCode  = '' + @MaterialCode +'' and SizeName  = '' + @SizeName +''   and  StockID =  '' + @StockID +''

  and CheckDate BETWEEN  CONVERT(varchar(100), @CheckDate, 20) and     DATEADD(dd,21,CONVERT(varchar(100),  @CheckDate, 20)) 

      --28天

    select  @twentyEight = sum(qty) from #tableA_2  where MaterialCode  = '' + @MaterialCode +'' and SizeName  = '' + @SizeName +''   and  StockID =  '' + @StockID +''

  and CheckDate BETWEEN  CONVERT(varchar(100), @CheckDate, 20) and     DATEADD(dd,28,CONVERT(varchar(100),  @CheckDate, 20)) 

   set @strString = @strString + '  update #TmpAll set '

if @seveDay > 0 

begin

   set @strString = @strString + '  count1 =   ' + CONVERT(varchar(100), @seveDay, 20)  +','

end 

if @fourThen > 0 

begin

set @strString = @strString + '  count2 =   ' + CONVERT(varchar(100), @fourThen, 20)+','

end

if @twentyOne > 0 

begin

set @strString = @strString + '  count3 =   ' +  CONVERT(varchar(100), @twentyOne, 20)+','

end

set @strString = @strString + '  count4 =   ' + CONVERT(varchar(100), @twentyEight, 20)

set @strString = @strString + '   where id =   ' + CONVERT(varchar(100), @index, 20) + ' ;'

set @index =  @index + 1 

end 

    print @strString -- 打印为空