搞了半天没搞出来，求教一个sql,日期统计解决方案

搞了半天没搞出来，求教一个sql,日期统计
就是统计某一连续日期等访问次数和登录人数，应该是需要先生成一组连续日期吧，然后join，可是弄半天弄乱套了也没搞出来，求教，思路已经乱了...

SQL code

create table visit(    int Id,    datetime VisitTime)insert into visitselect 1, '2012-1-1 18:00'union allselect 2, '2012-1-1 19:00'union allselect 3, '2012-1-3 00:00'union allselect 4, '2012-1-4 00:00'union allselect 5, '2012-1-5 00:00'create table logon(    int Id,    varchar(50) User,    datetime LogonTime)insert into logonselect 1, '张三', '2012-1-1 18:00'union allselect 2, '张三', '2012-1-1 19:00'union allselect 3, '张三', '2012-1-3 00:00'union allselect 4, '张三', '2012-1-4 00:00'union allselect 5, '李四' '2012-1-4 00:00'/*期望结果日期    访问次数    登录人数(过滤同一人的重复登录)2012-1-1    2    12012-1-2    0    02012-1-3    1    12012-1-4    1    22012-1-5    1    0*/

SQL code

create table visit(    Id int,    VisitTime datetime )insert into visitselect 1, '2012-1-1 18:00'union allselect 2, '2012-1-1 19:00'union allselect 3, '2012-1-3 00:00'union allselect 4, '2012-1-4 00:00'union allselect 5, '2012-1-5 00:00'create table logon(    Id int,    Users varchar(50),    LogonTime datetime )insert into logonselect 1, '张三', '2012-1-1 18:00'union allselect 2, '张三', '2012-1-1 19:00'union allselect 3, '张三', '2012-1-3 00:00'union allselect 4, '张三', '2012-1-4 00:00'union allselect 5, '李四', '2012-1-4 00:00'select dateadd(d,number,'2012-01-01') '日期',(select count(*) from visit where convert(varchar,VisitTime,111)=convert(varchar,dateadd(d,number,'2012-01-01'),111)) '访问次数',(select count(distinct Users) from logon where convert(varchar,LogonTime,111)=convert(varchar,dateadd(d,number,'2012-01-01'),111)) '登录人数'from master.dbo.spt_valueswhere [type]='P' and number between 0 and 4日期                      访问次数        登录人数----------------------- ----------- -----------2012-01-01 00:00:00.000   2           12012-01-02 00:00:00.000   0           02012-01-03 00:00:00.000   1           12012-01-04 00:00:00.000   1           22012-01-05 00:00:00.000   1           0(5 row(s) affected)
[解决办法]
可以不可以用BETWEEN...AND啊？
[解决办法]
select IA.VisitTime '日期',IA.VisitNumer '访问次数',
ISNULL(IB.LoginTimes,0)  '登录人数'  
from (
select CAST(visitTime as date) 'VisitTime'
,COUNT(0) 'VisitNumer'  
from visit
group by CAST(visitTime as date)
)  IA   left join  
(
select LogonTime,COUNT([USER]) 'LoginTimes' from  (
select  distinct cast(LogonTime as date) 'LogonTime',[USER]  
from logon) IC
group by LogonTime
)  IB
on IA.visitTime = IB.LogonTime