USE [NationalUnion]GO/****** Object:  StoredProcedure [dbo].[proc_DataSummary]    Script Date: 07/03/2014 15:33:11 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOalter proc [dbo].[proc_DataSummary20140703](@UserID bigint--,--@Level int)asbegin    begin try        begin tran        declare @Level int        set @Level=1 --默认Level为1为最后一级分享者        ----------------------------------------PV---------------------------------------------------------------------------------------------------------------------------------            --PV            merge into dbo.DataSummary as DS            using (select SharedUserID,PlatformID,CONVERT(varchar(100), CreateDate, 111) as CreatDate,ChannelID,SharedManagerID,COUNT(*) as Qty from dbo.PVInfo where SharedLevel = @Level and (@UserID<0 or SharedUserID = @UserID) group by SharedUserID,PlatformID,ChannelID,SharedManagerID,CONVERT(varchar(100), CreateDate, 111)) as P            on (DS.UserID=P.SharedUserID and DS.PlatformID=P.PlatformID and DS.ChannelID=P.ChannelID and DS.ManagerID=P.SharedManagerID and DS.SummaryDate=P.CreatDate)            when matched then                update set PV=(PV+P.Qty)--更新统计己存在的信息            when not matched then--统计新的PV信息                insert values(P.SharedUserID,P.PlatformID,P.CreatDate,P.ChannelID,P.SharedManagerID,P.Qty,0,0,0,0,0,0,GETDATE());        ----------------------------------------UV---------------------------------------------------------------------------------------------------------------------------------            --UV            merge into dbo.DataSummary as DS            using (select SharedUserID,PlatformID,CONVERT(varchar(100), CreateDate, 111) as CreatDate,ChannelID,SharedManagerID,COUNT(*) as Qty from dbo.UVInfo where SharedLevel = @Level and (@UserID<0 or SharedUserID = @UserID) group by SharedUserID,PlatformID,ChannelID,SharedManagerID,CONVERT(varchar(100), CreateDate, 111)) as U            on (DS.UserID=U.SharedUserID and DS.PlatformID=U.PlatformID and DS.ChannelID=U.ChannelID and DS.ManagerID=U.SharedManagerID and DS.SummaryDate=U.CreatDate)            when matched then                update set UV=(UV+U.Qty)--更新统计己存在的信息            when not matched then--统计新的PV信息                insert values(U.SharedUserID,U.PlatformID,U.CreatDate,U.ChannelID,U.SharedManagerID,0,U.Qty,0,0,0,0,0,GETDATE());        ----------------------------------------预计可算拥金---------------------------------------------------------------------------------------------------------------------------------            merge into dbo.DataSummary as DS            using (select SharedUserID,PlatformID,CONVERT(varchar(100), CreateTime, 111) as CreatDate,ChannelID,SharedManagerID,sum(Commission) as TotalEstimateCommission from dbo.CPSOriDataOccur group by SharedUserID,PlatformID,ChannelID,SharedManagerID,CONVERT(varchar(100), CreateTime, 111)) as O            on (DS.UserID=O.SharedUserID and DS.PlatformID=O.PlatformID and DS.SummaryDate=O.CreatDate and DS.ChannelID=O.ChannelID and DS.ManagerID=O.SharedManagerID)            when matched then--预计可算拥金                update set ds.CommissionEstimate=(ds.CommissionEstimate+O.TotalEstimateCommission)             when  not matched then                insert values( O.SharedUserID,O.PlatformID,O.CreatDate,O.ChannelID,O.SharedManagerID,0,0,0,0,O.TotalEstimateCommission,0,0,GETDATE());        ----------------------------------------可结算拥金---------------------------------------------------------------------------------------------------------------------------------            merge into dbo.DataSummary as DS            using (select SharedUserID,PlatformID,CONVERT(varchar(100), CreateTime, 111) as CreatDate,ChannelID,SharedManagerID,SUM(Price) as TotalPrice,sum(Commission) as TotalCommission from dbo.CPSOriDataEffect group by SharedUserID,PlatformID,ChannelID,SharedManagerID,CONVERT(varchar(100), CreateTime, 111)) as E            on (DS.UserID=E.SharedUserID and DS.PlatformID=E.PlatformID and DS.SummaryDate=E.CreatDate and DS.ChannelID=E.ChannelID and DS.ManagerID=E.SharedManagerID)            when matched then                update set ds.OrderAmount=(ds.OrderAmount+E.TotalPrice),ds.AvaliableCommission=(ds.CommissionEstimate+E.TotalCommission)             when  not matched then                insert values( E.SharedUserID,E.PlatformID,E.CreatDate,E.ChannelID,E.SharedManagerID,0,0,0,E.TotalPrice,0,E.TotalCommission,0,GETDATE());                     commit tran    end try    begin catch        rollback tran    end catchend