会员表：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
本文转自94cool博客园博客，原文链接http://www.cnblogs.com/94cool/archive/2011/08/01/2124102.html，如需转载请自行联系原作者
