会员按天统计、日分时统计

会员表：tbl_member /regtime注册日期

 

按天统计：

存储过程：

create          proc sp_tjmember
@type int
as

DECLARE @BDATE datetime
DECLARE @EDATE datetime
SET @EDATE=convert(varchar,getdate(),23)
if @type=1
 begin
  SET @BDATE=convert(varchar(7),getdate(),120)+'-01'--当月
 end
else
 begin
  SET @BDATE=convert(varchar,dateadd(day,-30,getdate()),23)--近30天
 end
print @BDATE
print @EDATE
select dt,sum(num) as num from (
SELECT CONVERT(VARCHAR(10),DATEADD(day,NUMBER,@BDATE),120) dt
,isnull(b.num,0) as num
FROM MASTER..SPT_VALUES as a

left join (select regtime,count(*) as num from tbl_member group by regtime) b
on datediff(day,dateadd(d,a.number,@BDATE),b.regtime)=0

WHERE TYPE='P'
AND DATEADD(day,NUMBER,@BDATE)<=@EDATE) as tbl group by dt

 

 

日分时统计：

存储过程：

 

 

create proc sp_tjmemberTM
@BDATE datetime
as
DECLARE @EDATE datetime
SET @EDATE=convert(varchar,DATEADD(day,1,@BDATE),23)

SELECT right(CONVERT(VARCHAR(13),DATEADD(hour,NUMBER,@BDATE),21)+':00',5)+' 至 '+
case right(CONVERT(VARCHAR(13),DATEADD(hour,NUMBER+1,@BDATE),21)+':00',5)
when '00:00' then '24:00' else right(CONVERT(VARCHAR(13),DATEADD(hour,NUMBER+1,@BDATE),21)+':00',5) end as dt
,isnull(b.num,0) as num
FROM MASTER..SPT_VALUES as a

left join (select CONVERT(VARCHAR(13),regtime,21) as regtime,count(*) as num from tbl_member group by CONVERT(VARCHAR(13),regtime,21)) b

on CONVERT(VARCHAR(13),DATEADD(hour,NUMBER,@BDATE),21)=CONVERT(VARCHAR(13),regtime,21)
WHERE TYPE='P'
AND DATEADD(hour,NUMBER,@BDATE)<@EDATE