6,129
社区成员
发帖
与我相关
我的任务
分享
create table #T_User ([ID] int identity(1,1) not null primary key, [UserName] varchar(20) default '', [Password] varchar(20) default '')
create table #T_Mo (InterfaceID int not null primary key,UserID int default 0, RegDate datetime default getdate())
create table #T_Report ([ID] int identity(1,1) not null primary key, UserId int default 0, InterfaceID int default 0, Status int, RegDate datetime)
/*
--加入测试数据
insert into #T_User(UserName, Password)
select 'A','A' union All
select 'B','B' union All
select 'C','C' union All
select 'D','D' union All
select 'E','E' union All
select 'F','F' union All
select 'G','G' union All
select 'H','H'
Go
declare @i int
set @i = 1
while @i < 100
begin
Insert Into #T_Mo
select @i, (select top 1 [ID] from #T_User order by newid()), getdate()
set @i = @i + 1
end
declare @i int
set @i = 1
while @i < 100
begin
Insert Into #T_Report
select (select top 1 [ID] from #T_User order by newid()), (select top 1 [InterfaceID] from #T_Mo order by newid()), 1, getdate()
set @i = @i + 1
end
select * from #T_Mo
select * from #T_Report
Update #T_Mo set RegDate = Dateadd(hh, 1, RegDate) where InterfaceID between 0 and 10
Update #T_Mo set RegDate = Dateadd(hh, 2, RegDate) where InterfaceID between 11 and 31
Update #T_Mo set RegDate = Dateadd(hh, 3, RegDate) where InterfaceID between 32 and 60
Update #T_Mo set RegDate = Dateadd(hh, 4, RegDate) where InterfaceID between 61 and 100
Update #T_Report set RegDate = Dateadd(hh, 1, RegDate) where [ID] between 0 and 10
Update #T_Report set RegDate = Dateadd(hh, 2, RegDate) where [ID] between 11 and 30
Update #T_Report set RegDate = Dateadd(hh, 3, RegDate) where [ID] between 32 and 56
Update #T_Report set RegDate = Dateadd(hh, 4, RegDate) where [ID] between 61 and 100
*/
select Year(RegDate) iYear, Month(RegDate) iMonth, Day(RegDate) iDay, (substring(convert(varchar(20),RegDate, 121),12,2)) iHour, Count(1) iCount into #T_Mo1 from #T_Mo group by RegDate
select Year(RegDate) iYear, Month(RegDate) iMonth, Day(RegDate) iDay, (substring(convert(varchar(20),RegDate, 121),12,2)) iHour, Count(1) iCount into #T_Report1 from #T_Report group by RegDate
select A.iCount iMoCount, B.iCount iRptCount, Round(((B.iCount*0.1) / (A.iCount*0.1)), 2) fRate, A.iHour from #T_Mo1 A inner join #T_Report1 B on A.iYear=B.iYear and A.iMonth=B.iMonth and A.iDay=B.iDay and A.iHour=B.iHour
create table #T_User ([ID] int identity(1,1) not null primary key, [UserName] varchar(20) default '', [Password] varchar(20) default '')
create table #T_Mo (InterfaceID int not null primary key,UserID int default 0, RegDate datetime default getdate())
create table #T_Report ([ID] int identity(1,1) not null primary key, UserId int default 0, InterfaceID int default 0, Status int, RegDate datetime)
insert into #T_User(UserName, Password)
select 'A','A' union All
select 'B','B' union All
select 'C','C' union All
select 'D','D' union All
select 'E','E' union All
select 'F','F' union All
select 'G','G' union All
select 'H','H'
Go
declare @i int
set @i = 1
while @i < 100
begin
Insert Into #T_Mo
select @i, (select top 1 [ID] from #T_User order by newid()), getdate()
set @i = @i + 1
end
declare @i int
set @i = 1
while @i < 100
begin
Insert Into #T_Report
select (select top 1 [ID] from #T_User order by newid()), (select top 1 [InterfaceID] from #T_Mo order by newid()), 1, getdate()
set @i = @i + 1
end
select * from #T_Mo
select * from #T_Report
Update #T_Mo set RegDate = Dateadd(hh, 1, RegDate) where InterfaceID between 0 and 10
Update #T_Mo set RegDate = Dateadd(hh, 2, RegDate) where InterfaceID between 11 and 31
Update #T_Mo set RegDate = Dateadd(hh, 3, RegDate) where InterfaceID between 32 and 60
Update #T_Mo set RegDate = Dateadd(hh, 4, RegDate) where InterfaceID between 61 and 100
Update #T_Report set RegDate = Dateadd(hh, 1, RegDate) where [ID] between 0 and 10
Update #T_Report set RegDate = Dateadd(hh, 2, RegDate) where [ID] between 11 and 30
Update #T_Report set RegDate = Dateadd(hh, 3, RegDate) where [ID] between 32 and 56
Update #T_Report set RegDate = Dateadd(hh, 4, RegDate) where [ID] between 61 and 100
select Year(RegDate) iYear, Month(RegDate) iMonth, Day(RegDate) iDay, (substring(convert(varchar(20),RegDate, 121),12,2)) iHour, Count(1) iCount into #T_Mo1 from #T_Mo group by RegDate
select Year(RegDate) iYear, Month(RegDate) iMonth, Day(RegDate) iDay, (substring(convert(varchar(20),RegDate, 121),12,2)) iHour, Count(1) iCount into #T_Report1 from #T_Report group by RegDate
select A.iCount iMoCount, B.iCount iRptCount, Round(((B.iCount*0.1) / (A.iCount*0.1)), 2) fRate, A.iHour from #T_Mo1 A inner join #T_Report1 B on A.iYear=B.iYear and A.iMonth=B.iMonth and A.iDay=B.iDay and A.iHour=B.iHour
CREATE PROCEDURE [dbo].[Total_pag]
@UserID int,--用户ID
@InterfaceID int,--通道ID
@Start_Times datetime,
@End_Times datetime,
@style int
AS
declare @times1 int
declare @times2 int
declare @Up_sum int
declare @Stau_sum int
if @style =1
begin
select count(DATEPART(HH,RegDate)) as Up_sum,DATEPART(HH,RegDate) as TR into #T_Mo from dbo.T_Mo where RegDate between @Start_Times and @End_Times and UserID=@UserID and InterfaceID=@InterfaceID GROUP BY DATEPART(HH,RegDate) order by DATEPART(HH,RegDate) desc
select count(DATEPART(HH,RegDate)) as Stau_sum,DATEPART(HH,RegDate) as RR into #T_Report from dbo.T_Report where RegDate between @Start_Times and @End_Times and UserID=@UserID and InterfaceID=@InterfaceID and Status=1 GROUP BY DATEPART(HH,RegDate) order by DATEPART(HH,RegDate) desc
select *,LTRIM(CONVERT(DEC(18,2),Stau_sum*100.0/Up_sum))+'%' as sty into ##Total from #T_Mo inner join #T_Report on #T_Mo.TR=#T_Report.RR
end
if @style =2
begin
select count(DATEPART(dd,RegDate)) as Up_sum, DATEPART(dd,RegDate) as Mre_Date into #T_Mo from dbo.T_Mo where RegDate between @Start_Times and @End_Times and UserID=@UserID and InterfaceID=@InterfaceID GROUP BY DATEPART(dd,RegDate) order by DATEPART(dd,RegDate) desc
select count(DATEPART(dd,RegDate)) as Stau_sum,DATEPART(dd,RegDate) as Rre_Date into #T_Report from dbo.T_Report where RegDate between @Start_Times and @End_Times and UserID=@UserID and InterfaceID=@InterfaceID and Status=1 GROUP BY DATEPART(dd,RegDate) order by DATEPART(dd,RegDate) desc
select *,LTRIM(CONVERT(DEC(18,2),Stau_sum*100.0/Up_sum))+'%' as sty into ##Total from #T_Mo inner join #T_Report on #T_Mo.Mre_Date=#T_Report.Rre_Date
end
if @style =3
begin
select count(UserID) as Up_sum,UserID into #T_Mo from T_Mo where InterfaceID=@InterfaceID and RegDate between @Start_Times and @End_Times group by UserID order by UserID
select count(UserID) as Stau_sum,UserID as U_ID into #T_Report from T_Report where InterfaceID=@InterfaceID and RegDate between @Start_Times and @End_Times and Status=1 group by UserID order by UserID
select *,LTRIM(CONVERT(DEC(18,2),Stau_sum*100.0/Up_sum))+'%' as sty into ##Total from #T_Mo inner join #T_Report on #T_Mo.UserID=#T_Report.U_ID
end
drop table #T_Mo
drop table #T_Report
drop table ##Total