select * from t;
1
2
3
4
5
6
7
8
9
10
11
12
A 2004-01-01 00:00:00.000 2004-05-01 00:00:00.000
A 2004-02-01 00:00:00.000 2004-05-01 00:00:00.000
A 2004-01-01 00:00:00.000 2004-03-01 00:00:00.000
A 2004-05-01 00:00:00.000 2004-06-01 00:00:00.000
A 2004-02-01 00:00:00.000 2004-09-01 00:00:00.000
B 2004-01-01 00:00:00.000 2004-05-01 00:00:00.000
B 2004-02-01 00:00:00.000 2004-05-01 00:00:00.000
B 2004-01-01 00:00:00.000 2004-03-01 00:00:00.000
B 2004-05-01 00:00:00.000 2004-06-01 00:00:00.000
select '2004-'+ str(n,len(n)) +'-01',col1,(
select count(1) from tb where tb.col1=tt.col1
and '2004-'+ str(n,len(n)) +'-01' between col2 and col3
) from t
,(select distinct col1 from tb) tt
2004-1-01 A 2
2004-2-01 A 4
2004-3-01 A 4
2004-4-01 A 3
2004-5-01 A 4
2004-6-01 A 2
2004-7-01 A 1
2004-8-01 A 1
2004-9-01 A 1
2004-10-01 A 0
2004-11-01 A 0
2004-12-01 A 0
2004-1-01 B 2
2004-2-01 B 3
2004-3-01 B 3
2004-4-01 B 2
2004-5-01 B 3
2004-6-01 B 1
2004-7-01 B 0
2004-8-01 B 0
2004-9-01 B 0
2004-10-01 B 0
2004-11-01 B 0
2004-12-01 B 0
1
2
3
4
5
6
7
8
9
10
11
12
A 2004-01-01 00:00:00.000 2004-05-01 00:00:00.000
A 2004-02-01 00:00:00.000 2004-05-01 00:00:00.000
A 2004-01-01 00:00:00.000 2004-03-01 00:00:00.000
A 2004-05-01 00:00:00.000 2004-06-01 00:00:00.000
A 2004-02-01 00:00:00.000 2004-09-01 00:00:00.000
B 2004-01-01 00:00:00.000 2004-05-01 00:00:00.000
B 2004-02-01 00:00:00.000 2004-05-01 00:00:00.000
B 2004-01-01 00:00:00.000 2004-03-01 00:00:00.000
B 2004-05-01 00:00:00.000 2004-06-01 00:00:00.000
select '2004-'+ str(n,len(n)) +'-01',col1,(
select count(1) from tb where tb.col1=tt.col1
and '2004-'+ str(n,len(n)) +'-01' between col2 and col3
) from t
,(select distinct col1 from tb) tt
2004-1-01 A 2
2004-2-01 A 4
2004-3-01 A 4
2004-4-01 A 3
2004-5-01 A 4
2004-6-01 A 2
2004-7-01 A 1
2004-8-01 A 1
2004-9-01 A 1
2004-10-01 A 0
2004-11-01 A 0
2004-12-01 A 0
2004-1-01 B 2
2004-2-01 B 3
2004-3-01 B 3
2004-4-01 B 2
2004-5-01 B 3
2004-6-01 B 1
2004-7-01 B 0
2004-8-01 B 0
2004-9-01 B 0
2004-10-01 B 0
2004-11-01 B 0
2004-12-01 B 0
select * from t;
1
2
3
4
5
6
7
8
9
10
11
12select * from tb
A 2004-01-01 00:00:00.000 2004-05-01 00:00:00.000
A 2004-02-01 00:00:00.000 2004-05-01 00:00:00.000
A 2004-01-01 00:00:00.000 2004-03-01 00:00:00.000
A 2004-05-01 00:00:00.000 2004-06-01 00:00:00.000
A 2004-02-01 00:00:00.000 2004-09-01 00:00:00.000
B 2004-01-01 00:00:00.000 2004-05-01 00:00:00.000
B 2004-02-01 00:00:00.000 2004-05-01 00:00:00.000
B 2004-01-01 00:00:00.000 2004-03-01 00:00:00.000
B 2004-05-01 00:00:00.000 2004-06-01 00:00:00.000select '2004-'+ str(n,len(n)) +'-01',col1,(
select count(1) from tb where tb.col1=tt.col1
and '2004-'+ str(n,len(n)) +'-01' between col2 and col3
) from t
,(select distinct col1 from tb) tt2004-1-01 A 2
2004-2-01 A 4
2004-3-01 A 4
2004-4-01 A 3
2004-5-01 A 4
2004-6-01 A 2
2004-7-01 A 1
2004-8-01 A 1
2004-9-01 A 1
2004-10-01 A 0
2004-11-01 A 0
2004-12-01 A 0
2004-1-01 B 2
2004-2-01 B 3
2004-3-01 B 3
2004-4-01 B 2
2004-5-01 B 3
2004-6-01 B 1
2004-7-01 B 0
2004-8-01 B 0
2004-9-01 B 0
2004-10-01 B 0
2004-11-01 B 0
2004-12-01 B 0
as
set nocount on
declare @i int,@dt datetime
select @dt=min(Datestart),@i=datediff(month,@dt,getdate())+1
from A--生成月份处理的序数表
set rowcount @i
select id=identity(int,0,1),dt=@dt,ym=cast(null as char(6)) into #t from syscolumns,sysobjects
set @i=@i-@@rowcount
while @i>0
begin
set rowcount @i
insert #t(dt) select @dt from syscolumns,sysobjects
set @i=@i-@@rowcount
end
update #t set dt=dateadd(month,id,@dt),ym=convert(char(6),dateadd(month,id,@dt),112)--查询
select a.SoftID,S_Month=a.ym
,ActiveAtEnd=isnull((select count(*) from a where SoftID=b.SoftID and datediff(month,CancelDate,b.dt)<0),0)
,Cancel=isnull((select count(*) from a where SoftID=b.SoftID and datediff(month,CancelDate,b.dt)=0),0)
from(
select a.SoftID,b.dt,b.ym
from(select SoftID from A group by SoftID)a,#t b
)b
go
as
set nocount on
declare @i int,@dt datetime
select @dt=min(Datestart),@i=datediff(month,@dt,getdate())+1
from A--生成月份处理的序数表
set rowcount @i
select id=identity(int,0,1),dt=@dt,ym=cast(null as char(6)) into #t from syscolumns,sysobjects
set @i=@i-@@rowcount
while @i>0
begin
set rowcount @i
insert #t(dt) select @dt from syscolumns,sysobjects
set @i=@i-@@rowcount
end
update #t set dt=dateadd(month,id,@dt),ym=convert(char(6),dateadd(month,id,@dt),112)--查询
select a.SoftID,S_Month=a.ym
,ActiveAtEnd=sum(case when datediff(month,a.CancelDate,b.dt)<0 then 1 else 0 end)
,Cancel=sum(case when datediff(month,a.CancelDate,b.dt)=0 then 1 else 0 end)
from(
select a.SoftID,b.dt,b.ym
from(select SoftID from A group by SoftID)a,#t b
)b left join a on a.SoftID=b.SoftID
and datediff(month,a.CancelDate,b.dt)<=0
go
as
set nocount on
declare @i int,@dt datetime
select @dt=min(Datestart),@i=datediff(month,@dt,getdate())+1
from A--生成月份处理的序数表
set rowcount @i
select id=identity(int,0,1),dt=@dt,ym=cast(null as char(6)) into #t from syscolumns,sysobjects
set @i=@i-@@rowcount
while @i>0
begin
set rowcount @i
insert #t(dt) select @dt from syscolumns,sysobjects
set @i=@i-@@rowcount
end
update #t set dt=dateadd(month,id,@dt),ym=convert(char(6),dateadd(month,id,@dt),112)--查询
select a.SoftID,S_Month=a.ym
,ActiveAtEnd=sum(case when datediff(month,a.CancelDate,b.dt)<0 then 1 else 0 end)
,Cancel=sum(case when datediff(month,a.CancelDate,b.dt)=0 then 1 else 0 end)
from(
select a.SoftID,b.dt,b.ym
from(select SoftID from A group by SoftID)a,#t b
)b left join a on a.SoftID=b.SoftID
and datediff(month,a.CancelDate,b.dt)<=0
and datediff(month,a.Datestart,b.dt)>=0 --觉得应该还有这个条件
go
create table T ( m char(6)) --这个表以后也可用上,不必删除。
declare @m char(6)
select @m=convert(char(6),min(datestart),112) from A
while @m<convert(char(6),getdate(),112)
begin
insert T select @m
set @m=convert(char(6),dateadd(month,1,cast(left(@m,4)+'-'+right(@m,2)+'-01' as datetime)),112)
end
--以下为实现你的要 求的语句,未经测试:insert B
select a.softid,
m,
sum( case when convert(char(6),datestart,112)<=m
and convert(char(6),canceldate,112)>m
then 1
else 0
end
),
sum( case when convert(char(6),canceldate,112)=m
then 1
else 0
end
)
from A join ( select softid,convert(char(6),min(datestart),112) as mt
from A
group by softid
) b
on a.softid=b.softid
join T on b.mt<=T.m
group by a.softid,m