汇总账单时的难点

问题
几个表内日期字段值都为日期+时间，例：2013年4月22号 13:18 ，要把每一天的这几个表的部分内容汇总成一条记录插入到新建的一个表中。

解决方法
1、为每个所需表创建临时表，汇总单个表一天内的内容；

2、将汇总插入到新表中。

这个方法是通过存储过程实现，代码如下：

if(OBJECT_ID('Proc_Sum','P') is not null)
drop proc Proc_Sum
go

create proc Proc_Sum
(
    --操作者的登录记录编号
    @Operator_Login_ID int
)
as
    --今日日期
    declare @DateNow as date
    --操作者姓名
    declare @Operator_Name as varchar(20)
    --今日上机人数
    declare @LoginNO as integer
    --今日注册人数
    declare @RegisterNO as integer
    --今日退卡人数
    declare @ReturnNO as integer
    --今日充值人数
    declare @RechargeNO as integer
    --今日退卡金额
    declare @ReturnMoney as real
    --今日消费金额
    declare @PayMoney as real
    --今日充值金额
    declare @RechargeMoney as real
    --今日应得金额
    declare @LookMoney as real
    --今日实利润
    declare @MakeMoney as real

begin tran t1
--充值金额充值人数
if object_id('tempdb.dbo.#RechargeMoney1') is not null
DROP TABLE #RechargeMoney1 --删除临时表
select Card_Recharge_Money, CONVERT(varchar(10),Card_Recharge_Time ,120) as date into #RechargeMoney1 from Card_Recharge_Record
if object_id('tempdb.dbo.#RechargeMoney2') is not null
DROP TABLE #RechargeMoney2 --删除临时表
select SUM(card_Recharge_Money) as RechargeMoney, COUNT(*) as RechargeNO, date into #RechargeMoney2 from #RechargeMoney1 group by date

--退卡金额退卡人数
if object_id('tempdb.dbo.#ReturnMoney1') is not null
DROP TABLE #ReturnMoney1 --删除临时表
select Card_Return_Money, CONVERT(varchar(10),Card_Return_Time ,120)as date into #ReturnMoney1 from Card_Return_Record
if object_id('tempdb.dbo.#ReturnMoney2') is not null
DROP TABLE #ReturnMoney2 --删除临时表
select SUM(card_Return_Money) as ReturnMoney, COUNT(*) as ReturnNO,date into #ReturnMoney2 from #ReturnMoney1 group by date

--消费金额上机人数时间
if object_id('tempdb.dbo.#PayMoney1') is not null
DROP TABLE #PayMoney1 --删除临时表
select User_Cost,convert(varchar(10),User_Login_Record.User_Login_Time,120) as date into #PayMoney1 from Bill, User_Login_Record where bill.Bill_ID = User_Login_Record .Bill_ID
if object_id('tempdb.dbo.#PayMoney2') is not null
DROP TABLE #PayMoney2 --删除临时表
select SUM(User_Cost) as UserPay, COUNT(*) as UserLoginNO,date into #PayMoney2 from #PayMoney1 group by date

--注册总人数
if object_id('tempdb.dbo.#UserRegister1') is not null
DROP TABLE #UserRegister1 --删除临时表
select user_Register_ID,CONVERT(varchar(10),user_Register_Time,120) as date into #UserRegister1 from User_Register_Record
if object_id('tempdb.dbo.#UserReigster2') is not null
DROP TABLE #UserReigster2 --删除临时表
select COUNT(*) as RegisterNO,date into #UserReigster2 from #UserRegister1 group by date

set @DateNow = convert(varchar(10),getdate(),120)

--判断DaySum表中有无今天的结账记录
if Exists(select * From DaySum where Daysum.DaySum_Date = @DateNow )
begin
delete from DaySum where daysum.DaySum_Date = @DateNow
end

--插入日期和操作者姓名
select @Operator_Name = Operator_Info.Operator_Name from Operator_Info, Operator_Login_Record where Operator_Info.Operator_ID = Operator_Login_Record.Operator_ID and Operator_Login_Record .Operator_Login_ID = @Operator_Login_ID
insert into DaySum(DaySum_Date, Operator_Name) values(@DateNow, @Operator_Name)

--插入充值金额和充值人数
select @RechargeMoney = RechargeMoney, @RechargeNO = RechargeNO from #RechargeMoney2 where date = @DateNow
update DaySum set DaySum_ReChargeMoney = @RechargeMoney ,DaySum_ReChargeNO = @RechargeNO where DaySum_Date = @DateNow

--插入退卡金额和退卡人数
select @ReturnMoney = ReturnMoney ,@ReturnNO =returnNO from #ReturnMoney2 where date = @DateNow
update DaySum set DaySum_ReturnMoney = @ReturnMoney ,DaySum_ReturnNO = @ReturnNO where DaySum_Date = @DateNow

--插入消费金额和上机人数
select @PayMoney = UserPay, @LoginNO = UserLoginNO from #PayMoney2 where date = @DateNow
update DaySum set DaySum_PayMoney = @PayMoney ,DaySum_LoginNO = @LoginNO where DaySum_Date = @DateNow

        --插入今日注册总人数
        select @RegisterNO = RegisterNO from #UserReigster2 where date = @DateNow
        update DaySum set DaySum_RegisterNO = @RegisterNO where DaySum_Date = @DateNow

        --插入今日总钱
        set @LookMoney = @RechargeMoney -@ReturnMoney
        update DaySum set DaySum_LookMoney = @LookMoney where DaySum_Date = @DateNow

        --插入利润字段
        set @MakeMoney = @PayMoney
        update DaySum set DaySum_MakeMoney = @MakeMoney where DaySum_Date = @DateNow

        if @@Error<>0
           rollback tran t1 --执行出错
        else
           Commit tran t1   --执行未出错
go

补充：综合编程 , 其他综合 ,