日期:2014-05-17 浏览次数:20575 次
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
select * from #xs
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&nbs