对日期 ，和 master.dbo.spt_values 表操作

if object_id('tempdb..#xs') is not null
drop table #xs;
 
create table #xs(vip varchar(20), date datetime, sale float)
insert into #xs(vip, date, sale)
select 'A' as vip,'2013-1-1' as date,    230 as sale union all
select 'A','2013-1-5',    400 union all
select 'A','2013-3-6',    500 union all
select 'A','2013-4-30',600 union all
select 'B','2013-2-3',200 union all
select 'B','2013-3-5',300 union all
select 'B','2013-4-24',400 
 
;with t as 
(
select dateadd(month, number,'20130101') MonthDay
from master.dbo.spt_values
where type='p'
)
,tRange as 
(
select vip,convert(varchar(6),min(date),112)+'01' MinMonth,convert(varchar(6),max(date),112)+'01' MaxMonth
from #xs
group by vip
),tDimDate as
(
select tr.vip,t.MonthDay StartDay,dateadd(d,-1,DATEADD(month,1,t.MonthDay)) EndDay
from tRange tr
join t 
on tr.MinMonth<=t.MonthDay
and tr.MaxMonth>=t.MonthDay
),tdata as (
select t.vip,EndDay
,DATEDIFF(d,(select top 1 date from #xs xs where xs.date <=t.EndDay and xs.vip=t.vip order by xs.date desc  ),EndDay) NoVisitDays
,count(x.date) VisitDays
,sum(isnull(sale,0)) Sales
from tDimDate t
left join #xs x
on x.[date] between t.StartDay and t.EndDay
and t.vip=x.vip
group by t.vip,EndDay
)
select vip,EndDay,NoVisitDays,VisitDays
,(select sum(VisitDays) from tdata b where a.vip=b.vip and b.EndDay<=a.EndDay  ) sumVisitDays
,(select sum(Sales) from tdata c where a.vip=c.vip and c.EndDay<=a.EndDay  )  sumSales
from tdata a
order by 1,2


----
declare @begin datetime,@end datetime
set @begin='2012-1-2'
set @end='2012-5-5'

declare @days int
set @days=DATEDIFF(MM,@begin,@end)
select DATEADD(MM,number,@begin) 
from master.dbo.spt_values 
where type='p' AND number<=@days

--主要用作取数字
select number from master..spt_values where type='p'



 from  http://bbs.csdn.net/topics/390673453?page=1