T-SQL:产品销量统计

/*--=============================
功能：产品销量统计
输入：
返回：
作者：dzh
时间：2006/06/02
说明：创建
*/--=============================
CREATE Procedure StatProductOut_F
 @UserId int,
 @datepart char(1),  -- y(null)-年；m-月；q-季。
 @field varchar(8), -- money(null)-金额；qty-数量。
 @StartDate datetime,
 @EndDate datetime,
 @CustomerId int,
 @InventoryTypeId int,
 @ProductGroupId varchar(50),
 @ProductName varchar(200)
AS


declare @strwhr varchar(8000) --查询条件
select  @strwhr = ' WHERE (O.User_ID = '+convert(varchar(50),@UserId)+')'
select  @strwhr = @strwhr + ' AND O.ProductOUT_Date Between '''+convert(varchar(10),@StartDate,120)+''' And '''+convert(varchar(10),@EndDate,120) +''''

if(@CustomerId>0)
   select  @strwhr = @strwhr + ' AND O.Customer_ID='+Convert(varchar(50),@CustomerId)
if(@InventoryTypeId>0)
   select  @strwhr = @strwhr + ' AND O.InventoryType_Id='+Convert(varchar(50),@InventoryTypeId)
If(rtrim(@ProductGroupId)!='')
   select  @strwhr = @strwhr + ' AND P.ProductGroup_ID='+@ProductGroupId
if(rtrim(@ProductName)!='')
   select  @strwhr = @strwhr + ' AND P.UserProduct_Name='''+@ProductName+''''


declare @str varchar(8000) --联接字符串
select  @str = ''
declare @i int
select  @i = 0
select  @datepart = lower(rtrim(@datepart))
select  @field = lower(rtrim(@field))
declare @tmpDate datetime

WHILE (@StartDate>'1900-1-1')
BEGIN
   select @tmpDate = DateAdd(month,@i,@StartDate)  
   IF(@field='qty') --///统计数量
   begin
       If(@datepart='m') --/按月统计
       Begin
           select @i = @i + 1
    
           select @str = @str + ',[' + Convert(varchar(4),year(@tmpDate)) + '/' 
            + Convert(varchar(2),Month(@tmpDate)) + ']
            = SUM(CASE Year(O.ProductOUT_Date)*100+Month(O.ProductOUT_Date) 
            WHEN '+ Convert(varchar(8),year(@tmpDate)*100+Month(@tmpDate)) +'
            THEN ISNULL(O.ProductOUT_Qty,0) ELSE 0 END)'
            
           IF year(@tmpDate)*100+month(@tmpDate) >= year(@EndDate)*100+month(@EndDate)
              BREAK
           ELSE      
              CONTINUE
       End
       Else If(@datepart='q') --/按季统计
       Begin
           select @i = @i + 3
    
           select @str = @str + ',[' + Convert(varchar(4),year(@tmpDate)) + '年' 
            + Convert(varchar(1),dbo.GetQuarter(@tmpDate)) + '季度]
            = SUM(CASE Year(O.ProductOUT_Date)*100+dbo.GetQuarter(O.ProductOUT_Date) 
            WHEN '+ Convert(varchar(8),year(@tmpDate)*100+dbo.GetQuarter(@tmpDate)) +'
            THEN ISNULL(O.ProductOUT_Qty,0) ELSE 0 END)'
            
           IF year(@tmpDate)*100+dbo.GetQuarter(@tmpDate) >= year(@EndDate)*100+dbo.GetQuarter(@EndDate)
              BREAK
           ELSE      
              CONTINUE
       End
       Else   --/按年统计
       Begin
           select @i = @i + 12
    
           select @str = @str + ',[' + Convert(varchar(4),year(@tmpDate)) + '年]
            = SUM(CASE Year(O.ProductOUT_Date) 
            WHEN '+ Convert(varchar(4),year(@tmpDate)) +'
            THEN ISNULL(O.ProductOUT_Qty,0) ELSE 0 END)'
            
           IF year(@tmpDate) >= year(@EndDate)
              BREAK
           ELSE      
              CONTINUE
       End
   end
   ELSE --///统计金额
   begin
       If(@datepart='m') --/按月统计
       Begin
           select @i = @i + 1
    
           select @str = @str + ',[' + Convert(varchar(4),year(@tmpDate)) + '/' 
            + Convert(varchar(2),Month(@tmpDate)) + ']
            = SUM(CASE Year(O.ProductOUT_Date)*100+Month(O.ProductOUT_Date) 
            WHEN '+ Convert(varchar(8),year(@tmpDate)*100+Month(@tmpDate)) +'
            THEN ISNULL(O.ProductOUT_Qty,0)*ISNULL(O.ProductOUT_Price,0.0) ELSE 0 END)'
            
           IF year(@tmpDate)*100+month(@tmpDate) >= year(@EndDate)*100+month(@EndDate)
              BREAK
           ELSE      
              CONTINUE
       End
       Else If(@datepart='q') --/按季统计
       Begin
           select @i = @i + 3
    
           select @str = @str + ',[' + Convert(varchar(4),year(@tmpDate)) + '年' 
            + Convert(varchar(1),dbo.GetQuarter(@tmpDate)) + '季度]
            = SUM(CASE Year(O.ProductOUT_Date)*100+dbo.GetQuarter(O.ProductOUT_Date) 
            WHEN '+ Convert(varchar(8),year(@tmpDate)*100+dbo.GetQuarter(@tmpDate)) +'
            THEN ISNULL(O.ProductOUT_Qty,0)*ISNULL(O.ProductOUT_Price,0.0) ELSE 0 END)'
            
           IF year(@tmpDate)*100+dbo.GetQuarter(@tmpDate) >= year(@EndDate)*100+dbo.GetQuarter(@EndDate)
              BREAK
           ELSE      
              CONTINUE
       End
       Else   --/按年统计
       Begin
           select @i = @i + 12
    
           select @str = @str + ',[' + Convert(varchar(4),year(@tmpDate)) + '年]
            = SUM(CASE Year(O.ProductOUT_Date) 
            WHEN '+ Convert(varchar(4),year(@tmpDate)) +'
            THEN ISNULL(O.ProductOUT_Qty,0)*ISNULL(O.ProductOUT_Price,0.0) ELSE 0 END)'
            
           IF year(@tmpDate) >= year(@EndDate)
              BREAK
           ELSE      
              CONTINUE
       End
   end
END
select @str = substring(@str,2,len(@str))
--Print @str

EXEC('
SELECT  --SUM(O.ProductOUT_Qty) AS SumQty, 
    --SUM(O.ProductOUT_Price*O.ProductOUT_Qty) AS SumMoney
 '+@str+'
FROM dbo.CU_Customer C RIGHT OUTER JOIN
      dbo.PR_ProductOUT O INNER JOIN
      dbo.PR_UserProduct P ON O.Product_ID = P.UserProduct_ID AND 
      O.User_ID = P.User_Id ON C.Customer_ID = O.Customer_ID AND 
      C.User_ID = O.User_ID' + @strwhr
)
GO

/*--=============================
功能：获得当前日期所在的季度
输入：@date
返回：
作者：dzh
时间：2006/06/02
说明：创建
*/--=============================
CREATE function dbo.GetQuarter
(
 @date datetime
)

Returns int
AS

Begin
    Declare @quarter int
    Select  @quarter=1

    SELECT @quarter=CASE month(@date) 
        WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 1
        WHEN 4 THEN 2 WHEN 5 THEN 2 WHEN 6 THEN 2
        WHEN 7 THEN 3 WHEN 8 THEN 3 WHEN 9 THEN 3
        ELSE 4 END        
    
    Return @quarter
End