 SQL日期和字符串可以相互转化，收藏一个临界时间的SQL：
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
--返回某段时间所有杂志某天的订阅量
--exec pro_rpt_MagazineCreateSubscribe
ALTER procedure dbo.pro_rpt_AllMagazineSubscribe
as
begin
set nocount on
--用户操作临时表
--oid nvarchar(200), --操作对象ID
--etime datetime, --时间
--edate nvarchar null --时间的日期部分
create table #userb(
oid nvarchar(200),
etime datetime,
edate nvarchar(50) null
)
--插入数据
declare @bDate datetime
set @bDate=DateAdd(day,-1,getdate())
declare @eDate datetime
set @eDate=getdate()
declare @beginDate varchar(50)
set @beginDate=convert(varchar(4),datepart(year,@bDate))+'-'+convert(varchar(2),datepart(month,@bDate))+'-'+convert(varchar(2),datepart(day,@bDate))+' 00:00:00'
declare @endDate varchar(50)
set @endDate=convert(varchar(4),datepart(year,@eDate))+'-'+convert(varchar(2),datepart(month,@eDate))+'-'+convert(varchar(2),datepart(day,@eDate))+' 00:00:00'
--往用户操作临时表插入所要数据
insert into #userb
(oid,etime,edate)
SELECT ub.ObjectID,ub.EventTime
,convert(varchar(4),datepart(year,ub.EventTime))+'-'+convert(varchar(2),datepart(month,ub.EventTime))+'-'+convert(varchar(2),datepart(day,ub.EventTime))
FROM UserBehavior1 as ub
WHERE EventName='SubscribeMagazine' and ObjectID <> '' and (ub.EventTime between @beginDate and @endDate) -- between @beginDate and @endDate) --ObjectType<>'Auto' and
--每天下载量的统计表
create table #tsub(
oid nvarchar(100),
dtime datetime,--日期
tsub decimal(10,2) null default 0.00, --本日真实下载量
tcsub decimal(10,2) null default 0.00 --本日处理过下载量
)
--select * from #userb order by edate
insert into #tsub
(oid,dtime,tsub)
select oid,edate,count(edate) as cnt from #userb
group by oid,edate
declare @rannum int --随机数
set @rannum=(select RandomNum from MagzineRandom where CreateTime=@bDate)
update #tsub set tcsub=case
when tsub>=0 and tsub<=5 then tsub*11+@rannum
when tsub>=6 and tsub<=15 then tsub*9+@rannum
when tsub>=16 then tsub*7+@rannum
end
Insert into MagazineSubStat
([ID],MagCode,DTime,TSub,TCSub)
select NewID() ,oid,dtime,tsub,tcsub from #tsub
select * from #tsub
drop table #tsub
drop table #userb
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
|
|
