某一类日期范围内每7天一组的销量之和，以及行转列

set @sqlstr =
	'
	select MIDDLECLASS, groupid, sum(Quantity) period_sum ,min(C_date) period_min_date,max(C_date) period_max_date  from(
	
    SELECT A.SkuCode,a.Quantity, a.C_date, datediff(day,''' +@B_time+  ''',CONVERT(varchar(100), a.C_date, 23) )/7 as groupid,
    p.MIDDLECLASS
    
    FROM dbo.OmsSale A LEFT JOIN   MF_BarCode B  ON A.SkuCode = B.SKU
     
    join DSBase_DB.dbo.v_productCode_class p on B.productcode=p.GOODSCODE

  WHERE CONVERT(varchar(100), C_date, 23) >= ''' +@B_time+  ''' AND CONVERT(varchar(100), C_date, 23)<= ''' +@E_time+ ''' 
    ) t 
    group by t.MIDDLECLASS,t.groupid 
    order by t.MIDDLECLASS,t.groupid 
   '  ;   

	/*
	IF(ISNULL(@Lswhere,'')<>'')  
		begin
			 SET @sqlstr=@sqlstr+' AND storename ='''+@Lswhere+''''
		end
	*/	
		 
	EXEC(@sqlstr);

 得到的结果：

 实现 某一类日期范围内每7天一组的销量之和，参考：http://www.cnblogs.com/wang3680/p/6056882.html

 

 

 

行转列，要调用上面的存储过程的结果集：

ALTER proc [dbo].[S_trend_row2column]
	   @B_time   varchar(30), 
       @E_time   varchar(30),
       @FunID    smallint
as      
begin  
	
	DECLARE @sql_str VARCHAR(8000)
		
    DECLARE @sql_col VARCHAR(8000);
	declare @demp table(MIDDLECLASS varchar(50),groupid int,period_sum int,period_min_date datetime,period_max_date datetime);
	
	insert into @demp exec S_trend @B_time,@E_time,0;
	--select * from @demp
	SELECT @sql_col = ISNULL(@sql_col + ',' , '') +  QUOTENAME(groupid)  FROM @demp GROUP BY groupid order by groupid  ;
	--print (@sql_col)
   
	SET @sql_str = '
 
	declare @demp table(MIDDLECLASS varchar(50),groupid varchar(50),period_sum int,period_min_date datetime,period_max_date datetime);
	
	insert into @demp exec S_trend '''+@B_time+''','''+@E_time+''',0;
 
 
	SELECT * FROM (
		SELECT [MIDDLECLASS],groupid,[period_sum] FROM @demp) p PIVOT 
		( SUM([period_sum]) FOR [groupid] IN ('+@sql_col+')
		) AS pvt ORDER BY pvt.[MIDDLECLASS]';
	
	--print (@sql_str)
	
    EXEC(@sql_str);

 

 

评论

1 楼 masuweng 2017-06-16