指定时间范围内广告牌每日统计示例

if OBJECT_ID('a') is not null
  drop table a
go

create table a
(
pid int,
pname varchar(20)
)
go

insert into a
select 1,'广告1'
union
select 2,'广告2'
union
select 3,'广告3'
union
select 4,'广告4'
union
select 5,'广告5'
go

if OBJECT_ID('b') is not null
  drop table b
go

create table b
(
pid int,
starttime datetime,
endtime datetime
)
go

insert into b
select 1,'2010-01-01','2010-01-20'
union all
select 1,'2010-01-25','2010-02-25'
union all
select 2,'2010-01-01','2010-01-20'
union all
select 2,'2010-02-01','2010-02-25'
union all
select 3,'2010-01-01','2010-03-30'
go

select *
from a
go

select *
from b
go

if OBJECT_ID('fun_stat') is not null
  drop function fun_stat
go

create function fun_stat(@date datetime,@starttime datetime,@endtime datetime)
  returns @t table(dates datetime,sums int,counts int)
as
begin
  declare @sum int
  set @sum=(select count(distinct pname) from a)
  while @date>=@starttime and @date<=@endtime
  begin
    insert into @t
    select dates=@date,sums=@sum,counts=(select count(*) from b join a on b.pid=a.pid where starttime<=@date and endtime>=@date)
    set @date=dateadd(day,1,@date)
  end
 
  return
end
go

select * from dbo.fun_stat('2010-01-01','2010-01-01','2010-04-01')
go