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

 

本例实现了按日期汇总数据的要求（按周汇总本年的定单销售金额）。主要思想是：
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月下旬