按时间进行汇总的一个解决方案

 

本例实现了按日期汇总数据的要求（按周汇总本年的定单销售金额）。主要思想是：
1、先将数据列中日期日期转换成我们想要的格式。如:2007-01月上旬
2、再按上述格式对数据进行汇总
3、按用户对数据格式的要求生成时间段表
4、将时间段和汇总表进行左连接

以下函数实现将一段日期格式化到数据表中。

GO
/**/ /****** 对象:  UserDefinedFunction [dbo].[LoopTime]    脚本日期: 09/19/2007 15:37:20 ******/
SET  ANSI_NULLS  ON
GO
SET  QUOTED_IDENTIFIER  ON
GO
--  =============================================
--  Author:        WGF2006
--  Create date: 2007-9-19
--  Description:    将时间转换为按年 季 月 旬 周 日的行集 
--  Parameters:  @STime开始时间 @ETime结束时间 @Type格式化的类型。Y年 Q季 M月 T旬 W周 D日

--  =============================================
CREATE   FUNCTION   [ dbo ] . [ LoopTime ]
(    
     --  Add the parameters for the function here
     @STime   datetime , 
     @ETime   datetime ,
     @Type   VARCHAR ( 2 )
)
RETURNS   @RetTable   TABLE (ID  INT   IDENTITY ( 1 , 1 ),TimeName  VARCHAR ( 50 )) 
AS
BEGIN
DECLARE    @Time   datetime
SET   @Time = @STime
WHILE ( @Time < @ETime )
BEGIN
     INSERT   INTO   @RetTable (TimeName)  VALUES (dbo.FormatTime( @Time , @Type ))
     SELECT   @Time = CASE   @Type   WHEN   ' Y '   THEN   DATEADD (YY, 1 , @Time )
     WHEN   ' Y '   THEN   DATEADD (YY, 1 , @Time )
     WHEN   ' Q '   THEN   DATEADD (Q, 1 , @Time )
     WHEN   ' M '   THEN   DATEADD (M, 1 , @Time )
     WHEN   ' T '   THEN   [ dbo ] .DATEADDXUN( @Time )
     WHEN   ' W '   THEN   DATEADD (WK, 1 , @Time )
     ELSE   DATEADD (D, 1 , @Time )
     end
END
RETURN
END

  以下函数完成添加一旬的功能

USE   [ MainData ]
GO
/**/ /****** 对象:  UserDefinedFunction [dbo].[DATEADDXUN]    脚本日期: 09/19/2007 15:37:52 ******/
SET  ANSI_NULLS  ON
GO
SET  QUOTED_IDENTIFIER  ON
GO
CREATE   FUNCTION   [ dbo ] . [ DateAddXUN ]
(    
     @STime    datetime  
)
RETURNS     datetime   
AS
BEGIN
     DECLARE   @DT   datetime
         IF ( DATEPART (D, @STime ) > 20 )
         BEGIN
             -- 处理下旬
             IF ( DATEPART (M, @STime )  IN  ( 1 , 3 , 5 , 7 , 8 , 10 , 12 ))
                 SET   @DT =   DATEADD (D, 11 , @STime )
             ELSE   IF ( DATEPART (M, @STime ) = 2 )
                 SET   @DT =    DATEADD (D, 8 , @STime )
             ELSE
                 SET   @DT =    DATEADD (D, 10 , @STime )
         END
         ELSE
             SET   @DT =    DATEADD (D, 10 , @STime )
        
     RETURN   @DT
END

以下函数完成日期的

GO
/**/ /****** 对象:  UserDefinedFunction [dbo].[FormatTime]    脚本日期: 09/19/2007 15:38:41 ******/
SET  ANSI_NULLS  ON
GO
SET  QUOTED_IDENTIFIER  ON
GO


--  =============================================
--  Author:        WGF2006
--  Create date: 2007-9-19
--  Description:    ''将时间格式化成 年 季 月 旬 周 日方便用来汇总
--  Parameters:  @DateTime进行格式的时间变量 @Type格式化的类型。Y年 Q季 M月 T旬 W周 D日
--  =============================================
CREATE   FUNCTION   [ dbo ] . [ FormatTime ]
(    
     @DateTime    datetime ,
     @Type   varchar ( 2 )
)
RETURNS   varchar ( 50 )  
AS
begin  
Declare   @str   varchar ( 50 ) 
Declare   @day   int
IF ( @Type = ' y ' )
     -- 格式化成年
     select   @str = Convert ( Varchar ( 4 ), @DateTime , 120 )
else   IF ( @Type = ' q ' )
     -- 格式化成季度
     select   @str = Convert ( Varchar ( 4 ), datepart (YY,  @DateTime )) + ' - ' + Convert ( Varchar ( 4 ), datepart (quarter,  @DateTime )) + ' 季度 '
else   IF ( @Type = ' m ' )
     -- 格式化成月
     select   @str = Convert ( Varchar ( 7 ), @DateTime , 120 )
else   IF ( @Type = ' t ' )
begin
     -- 格式化成旬
     set   @day = datepart (d,  @DateTime )
     select   @str = Convert ( Varchar ( 7 ), @DateTime , 120 ) + ' 月 ' + case  
     when    @day   between   1   and   10   then  
     ' 上旬 '
     when    @day   between   11   and   20   then  
     ' 中旬 '
     else
      ' 下旬 '
     end
end
else   IF ( @Type = ' w ' )
     -- 格式化成周
     select   @str = Convert ( Varchar ( 4 ), @DateTime , 120 ) + ' -第 ' + Convert ( varchar ( 2 ), datepart (wk, @DateTime )) + ' 周  '  
else   
     -- 格式化成年月日
     select   @str =   Convert ( Varchar ( 10 ), @DateTime , 120 ) 
 
return   @str
end  

实现汇总的测试

SELECT B.Bmoney, B.Total ,dbo.FormatTime(TellerTime,'t') AS tn INTO #t FROM CW_ORDER A Join Cw_OrderDetail B ON A.ID=B.OrderID  WHERE TellerTime between '2007-1-1' and'2007-8-31' and  A.state>3 and  A.ISDel=0 
select Sum(BMoney) BMoney ,Sum(Total)Total ,tn into #B From #T  Group by tn
SELECT * FROM dbo.LoopTime ('2007-1-1','2007-8-31','t') A
left JOIN  #B   B
ON A.TimeName= B.Tn
order by B.Bmoney desc
drop table #B
drop table #t

结果如下：

1 2007-01月上旬 71475.00 2007-01月上旬
2 2007-01月中旬 74225.25 2007-01月中旬
3 2007-01月下旬 22275.00 2007-01月下旬
4 2007-02月上旬 NULL NULL
5 2007-02月中旬 NULL NULL
6 2007-02月下旬 NULL NULL
7 2007-03月上旬 NULL NULL
8 2007-03月中旬 NULL NULL
9 2007-03月下旬 550.00 2007-03月下旬