27,580
社区成员
发帖
与我相关
我的任务
分享
--测试环境
create table dbo.Test_Crew(
Work_ID int,
Name varchar(50),
Department varchar(50),
start_date date,
work_day varchar(20),
holiday_type varchar(30)
)
--测试数据
insert into dbo.Test_Crew values('025881','A','IT','2015-10-5','break','');
insert into dbo.Test_Crew values('025881','A','IT','2015-10-6','work','');
insert into dbo.Test_Crew values('025881','A','IT','2015-10-7','work','issue');
insert into dbo.Test_Crew values('025881','A','IT','2015-10-8','work','issue');
insert into dbo.Test_Crew values('025881','A','IT','2015-10-9','break','');
insert into dbo.Test_Crew values('025881','A','IT','2015-10-10','work','issue');
insert into dbo.Test_Crew values('025881','A','IT','2015-10-11','work','issue');
insert into dbo.Test_Crew values('025881','A','IT','2015-10-12','work','');
insert into dbo.Test_Crew values('025881','A','IT','2015-10-13','work','');
insert into dbo.Test_Crew values('025881','A','IT','2015-10-14','work','');
insert into dbo.Test_Crew values('025881','A','IT','2015-10-15','work','issue');
insert into dbo.Test_Crew values('025881','A','IT','2015-10-16','work','issue');
insert into dbo.Test_Crew values('025881','A','IT','2015-10-17','break','');
insert into dbo.Test_Crew values('025881','A','IT','2015-10-18','work','sick');
insert into dbo.Test_Crew values('025881','A','IT','2015-10-19','work','sick');
insert into dbo.Test_Crew values('025881','A','IT','2015-10-20','work','');
insert into dbo.Test_Crew values('025881','A','IT','2015-10-21','work','');
--查看一下如果你想
--select * from dbo.Test_Crew
--使用 over&partition by语句
with good_name as
(
select *,
ROW_NUMBER() over(partition by Name, holiday_type order by start_date) as rnd,
ROW_NUMBER() over(partition by Name, work_day order by start_date) as rn
from dbo.Test_Crew
)
select Work_ID, Name,Department, MIN(start_date) as 'start', MAX(start_date) as 'end' , holiday_type , DATEDIFF(day,MIN(start_date),MAX(start_date))+1 as 'Count' from good_name
where holiday_type <> ''
group by Work_ID,Name,Department,holiday_type,rnd - rn
order by 'start'
我用了over & partition by语句,你可以根据我的代码改一下~