--示例--测试数据
create table tb(taskid int,日期 datetime,工时 int)
insert tb select 1001,'2004-11-25',8
union all select 1001,'2004-11-26',8
union all select 1001,'2004-11-27',8
union all select 1001,'2004-12-01',8
union all select 1001,'2004-12-02',8
union all select 1002,'2004-11-28',8
go--统计
select taskid,开始日期=convert(char(10),日期,120)
,天数=1+datediff(day,日期,(
select min(日期) from tb aa
where taskid=a.taskid and 日期>=a.日期
and not exists(select * from tb where taskid=aa.taskid and 日期=aa.日期+1)))
from tb a
where not exists(
select * from tb where taskid=a.taskid and 日期=a.日期-1)
go--删除测试
drop table tb/*--测试结果taskid 开始日期 天数
----------- ---------- -----------
1001 2004-11-25 3
1001 2004-12-01 2
1002 2004-11-28 1(所影响的行数为 3 行)
--*/
create table tb(taskid int,日期 datetime,工时 int)
insert tb select 1001,'2004-11-25',8
union all select 1001,'2004-11-26',8
union all select 1001,'2004-11-27',8
union all select 1001,'2004-12-01',8
union all select 1001,'2004-12-02',8
union all select 1002,'2004-11-28',8
go--统计
select taskid,开始日期=convert(char(10),日期,120)
,天数=1+datediff(day,日期,(
select min(日期) from tb aa
where taskid=a.taskid and 日期>=a.日期
and not exists(select * from tb where taskid=aa.taskid and 日期=aa.日期+1)))
from tb a
where not exists(
select * from tb where taskid=a.taskid and 日期=a.日期-1)
go--删除测试
drop table tb/*--测试结果taskid 开始日期 天数
----------- ---------- -----------
1001 2004-11-25 3
1001 2004-12-01 2
1002 2004-11-28 1(所影响的行数为 3 行)
--*/
from table group by taskid order by taskid asc
min(日期) as 开始日期,
count(distinct(日期)) as 天数
from table
group by taskid
order by taskid
select taskid,left(日期,7),
min(日期) as 开始日期,
count(distinct(日期)) as 天数
from table
group by taskid ,left(日期,7)) a
select a.taskid,a.开始日期,a.天数 from (
select taskid,convert(varchar(7),日期,120) as l,
min(日期) as 开始日期,
count(*) as 天数
from tb
group by taskid ,convert(varchar(7),日期,120)
) a
order by taskid