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

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

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的时间。都不敢用在正式库上（每天都有数千条记录产生）
求一个高效的算法。
谢谢！
[解决办法]
实际应用当中，都会有一个节假日，或者周末的表，来存放日期。
在有需求的时候，直接调用。剔除即可
[解决办法]
参考一下这篇http://blog.csdn.net/maco_wang/article/details/6262156
[解决办法]
函数还是有点问题,上面周四\周五到周日不对,应该分别是两天\一天

--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)
end

while @startdate<=@enddate
begin
if DATEPART(weekday, @startdate) between 2 and 6
begin
set @totalday=@totalday+1
end
set @startdate=dateadd(day,1,@startdate)
end

set @totalday=@totalday-1
if DATEPART(weekday, @enddate)=1 or DATEPART(weekday, @enddate)=7
set @totalday=@totalday+1
return @totalday
end

create 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 dt1

drop function dbo.fn_week
drop table dt1

-- 3.结果
/*
idc1c2C1_weekC2_week间隔(原)间隔(不包括周六、日)
02013-01-01 00:00:00.0002013-01-04 00:00:00.000二五33
12013-01-02 00:00:00.0002013-01-03 00:00:00.000三四11
22013-01-03 00:00:00.0002013-01-07 00:00:00.000四一42
32013-01-04 00:00:00.0002013-01-06 00:00:00.000五日21
42013-01-05 00:00:00.0002013-01-08 00:00:00.000六二32
52013-01-06 00:00:00.0002013-01-11 00:00:00.000日五55
62013-01-07 00:00:00.0002013-01-14 00:00:00.000一一75
72013-01-08 00:00:00.0002013-01-12 00:00:00.000二六44
82013-01-09 00:00:00.0002013-01-10 00:00:00.000三四11
92013-01-10 00:00:00.0002013-01-11 00:00:00.000四五11
102013-01-11 00:00:00.0002013-01-13 00:00:00.000五日21
112013-01-12 00:00:00.0002013-01-15 00:00:00.000六二32
122013-01-13 00:00:00.0002013-01-16 00:00:00.000日三33
132013-01-14 00:00:00.0002013-01-15 00:00:00.000一二11
142013-01-15 00:00:00.0002013-01-18 00:00:00.000二五33
152013-01-16 00:00:00.0002013-01-16 00:00:00.000三三00 
 
162013-01-17 00:00:00.0002013-01-20 00:00:00.000四日32
172013-08-31 00:00:00.0002013-09-15 00:00:00.000六日1511
182013-09-01 00:00:00.0002013-09-13 00:00:00.000日五1210
192013-09-01 00:00:00.0002013-09-14 00:00:00.000日六1311
202013-09-01 00:00:00.0002013-09-15 00:00:00.000日日1411
*/

[解决办法]
真的蛮费事的，下面应该可以了

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

idc1c2C2周天数周数调整结果
02013-01-012013-01-0462153
12013-01-022013-01-0350001
22013-01-032013-01-0723142
32013-01-042013-01-0570001
32013-01-042013-01-0611161
32013-01-042013-01-1318266
42013-01-052013-01-0610001
42013-01-052013-01-0832152
42013-01-052013-01-1317106
[解决办法]
简化一下你的逻辑：周日当周一，周六当周五

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
/*
abcd
2013-01-012013-01-0100
2013-01-012013-01-0433
2013-01-032013-01-0722
2013-01-042013-01-0511
2013-01-042013-01-0611
2013-01-042013-01-0711
2013-01-042013-01-1366
2013-01-052013-01-0500
2013-01-052013-01-0611
2013-01-052013-01-1366
2013-01-122013-01-1411
2013-01-132013-01-1411
*/

查看更多 下一篇