去除周末算间隔天数的问题。

Question

With dt1 as(
Select 0 as id, '2013-01-01' as c1,'2013-01-04' as c2 union all 
Select 1,'2013-01-02','2013-01-03' union all 
Select 2,'2013-01-03','2013-01-07' union all 
Select 3,'2013-01-04','2013-01-06' union all 
Select 4,'2013-01-05','2013-01-08' union all 
Select 5,'2013-01-06','2013-01-11' union all 
Select 6,'2013-01-07','2013-01-14' union all 
Select 7,'2013-01-08','2013-01-12' union all 
Select 8,'2013-01-09','2013-01-10' union all 
Select 9,'2013-01-10','2013-01-11' union all 
Select 10,'2013-01-11','2013-01-13' union all 
Select 11,'2013-01-12','2013-01-15' union all 
Select 12,'2013-01-13','2013-01-16' union all 
Select 13,'2013-01-14','2013-01-15' union all 
Select 14,'2013-01-15','2013-01-18' union all 
Select 15,'2013-01-16','2013-01-16' union all 
Select 16,'2013-01-17','2013-01-20'  
),
dt2 as ( Select '2013-01-10' as c1 )
Select id, DATEDIFF(DD,c1,c2) '间隔',c1,c2,
case DATEPART(weekday, c1 ) when 1 then '日' 
when 2 then '一' when 3 then '二' 
when 4 then '三' when 5 then '四' 
when 6 then '五' when 7 then '六' end as 'C1_week',
case DATEPART(weekday, c2 ) when 1 then '日' 
when 2 then '一' when 3 then '二' 
when 4 then '三' when 5 then '四' 
when 6 then '五' when 7 then '六' end as 'C2_week' from dt数据表为dt1；
算出c1与c2之间的间隔，要求如下：1：当 c1 与 c2 在同一周的星期一到星期五之间，间隔几天就是几天 
如：2013-01-01 周二  2013-01-04 周五 间隔3天2：当 c1 、c2 在星期一到星期五之间但不在同一周，间隔天数要减去 周六 周日
如：2013-01-03 周四  2013-01-07 周一 间隔2天3：当 c1在星期一到五之间，c2 在周六 周日。去除周末，但不去除本身
算法如下：
2013-01-04 周五 2013-01-05 周六 间隔1天 
2013-01-04 周五 2013-01-06 周日 间隔1天 去除了 周六
2013-01-04 周五 2013-01-13 周日 间隔6天 去除了 5，6号周六、周日 和 12号的周六4：当 c2在星期一到五之间，c1 在周六 周日。去除周末，但不去除本身
算法和3一样，只是c1和c2对换位置。5：c1 c2同一天 间隔为0；6: c1 c2都在周末，去除非本身的周末日期。
2013-01-05 周六 2013-01-06 周日 间隔1天 
2013-01-05 周六 2013-01-13 周日 间隔6天 我现在是一个日期一个日期的比对，然后拼到一起，写的太丑，速度太慢了
1000多条测试数据就用了4，5s的时间。都不敢用在正式库上（每天都有数千条记录产生）
求一个高效的算法。
谢谢！

Answer 1

参考一下这篇http://blog.csdn.net/maco_wang/article/details/6262156

Answer 2

--1. 创建函数
create function dbo.fn_week(@startdate datetime,@enddate datetime)
returns int
as
begin
declare @totalday int
set @totalday=0
if DATEPART(weekday, @startdate)=1
begin
set @totalday=1
set @startdate=dateadd(day,1,@startdate)
end
if DATEPART(weekday, @startdate)=7
begin
set @totalday=1
set @startdate=dateadd(day,2,@startdate)
endwhile @startdate<=@enddate
begin
if DATEPART(weekday, @startdate) between 2 and 6
begin
set @totalday=@totalday+1
end
set @startdate=dateadd(day,1,@startdate)
end
if DATEPART(weekday, @enddate)=1 or DATEPART(weekday, @enddate)=7
begin
set @totalday=@totalday+1
end
return @totalday
end;With dt1 as(
Select 0 as id, '2013-01-01' as c1,'2013-01-04' as c2 union all 
Select 1,'2013-01-02','2013-01-03' union all 
Select 2,'2013-01-03','2013-01-07' union all 
Select 3,'2013-01-04','2013-01-06' union all 
Select 4,'2013-01-05','2013-01-08' union all 
Select 5,'2013-01-06','2013-01-11' union all 
Select 6,'2013-01-07','2013-01-14' union all 
Select 7,'2013-01-08','2013-01-12' union all 
Select 8,'2013-01-09','2013-01-10' union all 
Select 9,'2013-01-10','2013-01-11' union all 
Select 10,'2013-01-11','2013-01-13' union all 
Select 11,'2013-01-12','2013-01-15' union all 
Select 12,'2013-01-13','2013-01-16' union all 
Select 13,'2013-01-14','2013-01-15' union all 
Select 14,'2013-01-15','2013-01-18' union all 
Select 15,'2013-01-16','2013-01-16' union all 
Select 16,'2013-01-17','2013-01-20' union all 
Select 16,'2013-09-01','2013-09-15'   -->加了这一行，测试9月13,14,15日没问题
)
Select id, c1,c2,
case DATEPART(weekday, c1 ) when 1 then '日' 
when 2 then '一' when 3 then '二' 
when 4 then '三' when 5 then '四' 
when 6 then '五' when 7 then '六' end as 'C1_week',
case DATEPART(weekday, c2 ) when 1 then '日' 
when 2 then '一' when 3 then '二' 
when 4 then '三' when 5 then '四' 
when 6 then '五' when 7 then '六' end as 'C2_week',
DATEDIFF(DD,c1,c2) '间隔(包括周六、日)',
dbo.fn_week(c1,c2) as '间隔(不包括周六、日)'
from dt1/*
id c1 c2 C1_week C2_week 间隔(包括周六、日) 间隔(不包括周六、日)
----------------------------------------------------------------------------
0 2013-01-01 2013-01-04 二 五 3 4
1 2013-01-02 2013-01-03 三 四 1 2
2 2013-01-03 2013-01-07 四 一 4 3
3 2013-01-04 2013-01-06 五 日 2 2
4 2013-01-05 2013-01-08 六 二 3 3
5 2013-01-06 2013-01-11 日 五 5 6
6 2013-01-07 2013-01-14 一 一 7 6
7 2013-01-08 2013-01-12 二 六 4 5
8 2013-01-09 2013-01-10 三 四 1 2
9 2013-01-10 2013-01-11 四 五 1 2
10 2013-01-11 2013-01-13 五 日 2 2
11 2013-01-12 2013-01-15 六 二 3 3
12 2013-01-13 2013-01-16 日 三 3 4
13 2013-01-14 2013-01-15 一 二 1 2
14 2013-01-15 2013-01-18 二 五 3 4
15 2013-01-16 2013-01-16 三 三 0 1
16 2013-01-17 2013-01-20 四 日 3 3
16 2013-09-01 2013-09-13 日 五 12 12
*/

Answer 3

上面有误，应该如下：--1. 创建函数
create function dbo.fn_week(@startdate datetime,@enddate datetime)
returns int
as
begin
declare @totalday int
set @totalday=0
if DATEPART(weekday, @startdate)=1
begin
set @totalday=1
set @startdate=dateadd(day,1,@startdate)
end
if DATEPART(weekday, @startdate)=7
begin
set @totalday=1
set @startdate=dateadd(day,2,@startdate)
endwhile @startdate<=@enddate
begin
if DATEPART(weekday, @startdate) between 2 and 6
begin
set @totalday=@totalday+1
end
set @startdate=dateadd(day,1,@startdate)
end
if DATEPART(weekday, @enddate)=1 or DATEPART(weekday, @enddate)=7
set @totalday=@totalday+1
else 
set @totalday=@totalday-1  -->修改这里return @totalday
end
;With dt1 as(
Select 0 as id, '2013-01-01' as c1,'2013-01-04' as c2 union all 
Select 1,'2013-01-02','2013-01-03' union all 
Select 2,'2013-01-03','2013-01-07' union all 
Select 3,'2013-01-04','2013-01-06' union all 
Select 4,'2013-01-05','2013-01-08' union all 
Select 5,'2013-01-06','2013-01-11' union all 
Select 6,'2013-01-07','2013-01-14' union all 
Select 7,'2013-01-08','2013-01-12' union all 
Select 8,'2013-01-09','2013-01-10' union all 
Select 9,'2013-01-10','2013-01-11' union all 
Select 10,'2013-01-11','2013-01-13' union all 
Select 11,'2013-01-12','2013-01-15' union all 
Select 12,'2013-01-13','2013-01-16' union all 
Select 13,'2013-01-14','2013-01-15' union all 
Select 14,'2013-01-15','2013-01-18' union all 
Select 15,'2013-01-16','2013-01-16' union all 
Select 16,'2013-01-17','2013-01-20' union all 
Select 16,'2013-09-01','2013-09-15'   -->加了这一行，测试9月13,14,15日没问题
)-- 2.查询
Select id, c1,c2,
case DATEPART(weekday, c1 ) when 1 then '日' 
when 2 then '一' when 3 then '二' 
when 4 then '三' when 5 then '四' 
when 6 then '五' when 7 then '六' end as 'C1_week',
case DATEPART(weekday, c2 ) when 1 then '日' 
when 2 then '一' when 3 then '二' 
when 4 then '三' when 5 then '四' 
when 6 then '五' when 7 then '六' end as 'C2_week',
DATEDIFF(DD,c1,c2) '间隔(原)',
dbo.fn_week(c1,c2) as '间隔(不包括周六、日)'
from dt1-- 3.结果
/*
id c1 c2 C1_week C2_week 间隔(原) 间隔(不包括周六、日)
0 2013-01-01 2013-01-04 二 五 3 3
1 2013-01-02 2013-01-03 三 四 1 1
2 2013-01-03 2013-01-07 四 一 4 2
3 2013-01-04 2013-01-06 五 日 2 2
4 2013-01-05 2013-01-08 六 二 3 2
5 2013-01-06 2013-01-11 日 五 5 5
6 2013-01-07 2013-01-14 一 一 7 5
7 2013-01-08 2013-01-12 二 六 4 5
8 2013-01-09 2013-01-10 三 四 1 1
9 2013-01-10 2013-01-11 四 五 1 1
10 2013-01-11 2013-01-13 五 日 2 2
11 2013-01-12 2013-01-15 六 二 3 2
12 2013-01-13 2013-01-16 日 三 3 3
13 2013-01-14 2013-01-15 一 二 1 1
14 2013-01-15 2013-01-18 二 五 3 3
15 2013-01-16 2013-01-16 三 三 0 0
16 2013-01-17 2013-01-20 四 日 3 3
16 2013-09-01 2013-09-15 日 日 14 12
*/

Answer 4

函数还是有点问题,上面周四\周五到周日不对,应该分别是两天\一天--1. 创建函数
create function dbo.fn_week(@startdate datetime,@enddate datetime)
returns int
as
begin
declare @totalday int
set @totalday=0
if DATEPART(weekday, @startdate)=1
begin
set @totalday=1
set @startdate=dateadd(day,1,@startdate)
end
if DATEPART(weekday, @startdate)=7
begin
set @totalday=1
set @startdate=dateadd(day,2,@startdate)
endwhile @startdate<=@enddate
begin
if DATEPART(weekday, @startdate) between 2 and 6
begin
set @totalday=@totalday+1
end
set @startdate=dateadd(day,1,@startdate)
endset @totalday=@totalday-1
if DATEPART(weekday, @enddate)=1 or DATEPART(weekday, @enddate)=7
set @totalday=@totalday+1
return @totalday
endcreate table dt1(id int,c1 datetime,c2 datetime )
insert into dt1
Select 0 as id, '2013-01-01' as c1,'2013-01-04' as c2 union all 
Select 1,'2013-01-02','2013-01-03' union all 
Select 2,'2013-01-03','2013-01-07' union all 
Select 3,'2013-01-04','2013-01-06' union all 
Select 4,'2013-01-05','2013-01-08' union all 
Select 5,'2013-01-06','2013-01-11' union all 
Select 6,'2013-01-07','2013-01-14' union all 
Select 7,'2013-01-08','2013-01-12' union all 
Select 8,'2013-01-09','2013-01-10' union all 
Select 9,'2013-01-10','2013-01-11' union all 
Select 10,'2013-01-11','2013-01-13' union all 
Select 11,'2013-01-12','2013-01-15' union all 
Select 12,'2013-01-13','2013-01-16' union all 
Select 13,'2013-01-14','2013-01-15' union all 
Select 14,'2013-01-15','2013-01-18' union all 
Select 15,'2013-01-16','2013-01-16' union all 
Select 16,'2013-01-17','2013-01-20' 
union all Select 17,'2013-08-31','2013-09-15'  --->加以下几行测试
union all Select 18,'2013-09-01','2013-09-13'  
union all Select 19,'2013-09-01','2013-09-14'  
union all Select 20,'2013-09-01','2013-09-15'  -- 2.查询
Select id, c1,c2,
case DATEPART(weekday, c1 ) when 1 then '日' 
when 2 then '一' when 3 then '二' 
when 4 then '三' when 5 then '四' 
when 6 then '五' when 7 then '六' end as 'C1_week',
case DATEPART(weekday, c2 ) when 1 then '日' 
when 2 then '一' when 3 then '二' 
when 4 then '三' when 5 then '四' 
when 6 then '五' when 7 then '六' end as 'C2_week',
DATEDIFF(DD,c1,c2) '间隔(原)',
dbo.fn_week(c1,c2) as '间隔(不包括周六、日)'
from dt1drop function dbo.fn_week
drop table dt1-- 3.结果
/*
id c1 c2 C1_week C2_week 间隔(原) 间隔(不包括周六、日)
0 2013-01-01 00:00:00.000 2013-01-04 00:00:00.000 二 五 3 3
1 2013-01-02 00:00:00.000 2013-01-03 00:00:00.000 三 四 1 1
2 2013-01-03 00:00:00.000 2013-01-07 00:00:00.000 四 一 4 2
3 2013-01-04 00:00:00.000 2013-01-06 00:00:00.000 五 日 2 1
4 2013-01-05 00:00:00.000 2013-01-08 00:00:00.000 六 二 3 2
5 2013-01-06 00:00:00.000 2013-01-11 00:00:00.000 日 五 5 5
6 2013-01-07 00:00:00.000 2013-01-14 00:00:00.000 一 一 7 5
7 2013-01-08 00:00:00.000 2013-01-12 00:00:00.000 二 六 4 4
8 2013-01-09 00:00:00.000 2013-01-10 00:00:00.000 三 四 1 1
9 2013-01-10 00:00:00.000 2013-01-11 00:00:00.000 四 五 1 1
10 2013-01-11 00:00:00.000 2013-01-13 00:00:00.000 五 日 2 1
11 2013-01-12 00:00:00.000 2013-01-15 00:00:00.000 六 二 3 2
12 2013-01-13 00:00:00.000 2013-01-16 00:00:00.000 日 三 3 3
13 2013-01-14 00:00:00.000 2013-01-15 00:00:00.000 一 二 1 1
14 2013-01-15 00:00:00.000 2013-01-18 00:00:00.000 二 五 3 3
15 2013-01-16 00:00:00.000 2013-01-16 00:00:00.000 三 三 0 0
16 2013-01-17 00:00:00.000 2013-01-20 00:00:00.000 四 日 3 2
17 2013-08-31 00:00:00.000 2013-09-15 00:00:00.000 六 日 15 11
18 2013-09-01 00:00:00.000 2013-09-13 00:00:00.000 日 五 12 10
19 2013-09-01 00:00:00.000 2013-09-14 00:00:00.000 日 六 13 11
20 2013-09-01 00:00:00.000 2013-09-15 00:00:00.000 日 日 14 11
*/

Answer 5

楼主：你的规则实在太绕了，简直无法实现，重新整理了一下看是否正确：
1 c1和c2是两个日期，且 c1<=c2
2 若 c1=c2 返回 0
3 若 c1<c2 ，则从 c1+1 至 c2-1 天数中，减掉周六、周日得到 n 天
4 返回 n+1

Answer 6

真的蛮费事的，下面应该可以了
With dt1 as(
Select 0 as id, '2013-01-01' as c1,'2013-01-04' as c2 union all 
Select 1,'2013-01-02','2013-01-03' union all 
Select 2,'2013-01-03','2013-01-07' union all 
Select 3,'2013-01-04','2013-01-05' union all 
Select 3,'2013-01-04','2013-01-06' union all 
Select 3,'2013-01-04','2013-01-13' union all 
Select 4,'2013-01-05','2013-01-06' union all 
Select 4,'2013-01-05','2013-01-08' union all 
Select 4,'2013-01-05','2013-01-13'
)
,dt2 as (
select *
,datepart(weekday,c2) C2周
,DATEDIFF(D,dateadd(d,1,c1),c2) 天数
,(DATEDIFF(D,dateadd(d,1,c1),c2)+6)/7 周数
from dt1
),dt3 as (
select *, 周数 * 7 - 天数 调整 from dt2
)
select *,周数 * 5 - 调整 + 1 +
case when C2周+调整=8 or C2周=1 and 调整>0 then 1 when C2周+调整>8 then 2 else 0 end 结果
from dt3
id c1 c2 C2周 天数 周数 调整 结果
0 2013-01-01 2013-01-04 6 2 1 5 3
1 2013-01-02 2013-01-03 5 0 0 0 1
2 2013-01-03 2013-01-07 2 3 1 4 2
3 2013-01-04 2013-01-05 7 0 0 0 1
3 2013-01-04 2013-01-06 1 1 1 6 1
3 2013-01-04 2013-01-13 1 8 2 6 6
4 2013-01-05 2013-01-06 1 0 0 0 1
4 2013-01-05 2013-01-08 3 2 1 5 2
4 2013-01-05 2013-01-13 1 7 1 0 6

Answer 7

简化一下你的逻辑：周日当周一，周六当周五with tb(a,b,c) as
(
select '2013-01-01','2013-01-01',0 union all
select '2013-01-01','2013-01-04',3 union all
select '2013-01-03','2013-01-07',2 union all
select '2013-01-04','2013-01-05',1 union all
select '2013-01-04','2013-01-06',1 union all
select '2013-01-04','2013-01-07',1 union all
select '2013-01-04','2013-01-13',6 union all
select '2013-01-05','2013-01-05',0 union all
select '2013-01-05','2013-01-06',1 union all
select '2013-01-05','2013-01-13',6 union all
select '2013-01-12','2013-01-14',1 union all
select '2013-01-13','2013-01-14',1
)
select *,
d = datediff(day,a,b) - datediff(week,a,b)*2 + (datepart(weekday,a)/7 + (datepart(weekday,b)^6)/7) * sign(datediff(day,a,b))
from tb
/*
a b c d
2013-01-01 2013-01-01 0 0
2013-01-01 2013-01-04 3 3
2013-01-03 2013-01-07 2 2
2013-01-04 2013-01-05 1 1
2013-01-04 2013-01-06 1 1
2013-01-04 2013-01-07 1 1
2013-01-04 2013-01-13 6 6
2013-01-05 2013-01-05 0 0
2013-01-05 2013-01-06 1 1
2013-01-05 2013-01-13 6 6
2013-01-12 2013-01-14 1 1
2013-01-13 2013-01-14 1 1
*/

Answer 8

@Vidor 你好，能说明一下
d = datediff(day,a,b) - datediff(week,a,b)*2 + (datepart(weekday,a)/7 + (datepart(weekday,b)^6)/7) * sign(datediff(day,a,b))
的意思吗？
 (datepart(weekday,a)/7 + (datepart(weekday,b)^6)/7) 这两个 没看明白