34,838
社区成员




if object_id('[T]') is not null drop table [T]
go
create table [T]([机器号] varchar(2),[startdate] datetime,[enddate] datetime)
insert [T]
select '1#','2009-10-20','2009-10-25' union all
select '1#','2009-09-26','2009-10-05' union all
select '2#','2009-09-12','2009-09-20' union all
select '2#','2009-09-25','2009-10-10' union all
select '2#','2009-10-25','2009-11-15' union all
select '3#','2009-08-10','2009-11-05' union all
select '2#','2009-11-25','2009-12-30'
select
机器号,
convert(varchar(7),dt,120) as [年/月],
count(1) as 天数
from(
select 机器号,dateadd(dd,b.number,a.startdate) as dt
from T as a,master..spt_values b
where b.type='P' and dateadd(dd,b.number,a.startdate)<=a.enddate
) t
group by 机器号,convert(varchar(7),dt,120)
order by 机器号,convert(varchar(7),dt,120)
/**
机器号 年/月 天数
---- ------- -----------
1# 2009-09 5
1# 2009-10 11
2# 2009-09 15
2# 2009-10 17
2# 2009-11 21
2# 2009-12 30
3# 2009-08 22
3# 2009-09 30
3# 2009-10 31
3# 2009-11 5
(10 行受影响)
**/
if object_id('[T]') is not null drop table [T]
go
create table [T]([机器号] varchar(2),[startdate] datetime,[enddate] datetime)
insert [T]
select '1#','2009-10-20','2009-10-25' union all
select '1#','2009-09-26','2009-10-05' union all
select '2#','2009-09-12','2009-09-20' union all
select '2#','2009-09-25','2009-10-10' union all
select '2#','2009-10-25','2009-11-15' union all
select '3#','2009-08-10','2009-11-05'
select
机器号,
convert(varchar(7),dt,120) as [年/月],
count(1) as 天数
from(
select 机器号,dateadd(dd,b.number,a.startdate) as dt
from T as a,master..spt_values b
where b.type='P' and dateadd(dd,b.number,a.startdate)<=a.enddate
) t
group by 机器号,convert(varchar(7),dt,120)
order by 机器号,convert(varchar(7),dt,120)
/**
机器号 年/月 天数
---- ------- -----------
1# 2009-09 5
1# 2009-10 11
2# 2009-09 15
2# 2009-10 17
2# 2009-11 15
3# 2009-08 22
3# 2009-09 30
3# 2009-10 31
3# 2009-11 5
(9 行受影响)
**/