sql 计算两个时间间隔了几个工作日

[sandynet]

我现有三张表分别是：[数据表]、[节假日]、[调休日]

表结构分别为：

[数据表]
RequestDate CompletionDate
2013-04-26 2013-05-06
2013-04-12 2013-04-15

[节假日]

Holiday Description
2013-04-04 清明节
2013-04-05 清明节
2013-04-06 清明节
2013-04-29 劳动节
2013-04-30 劳动节
2013-05-01 劳动节
2013-06-10 端午节
2013-06-11 端午节
2013-06-12 端午节

[调休日]

Working days Description
2013-01-05 元旦调休
2013-01-06 元旦调休
2013-02-16 春节调休
2013-02-17 春节调休
2013-04-07 清明节调休
2013-04-27 劳动节调休
2013-04-28 劳动节调休
2013-06-08 端午节调休
2013-06-09 端午节调休
2013-09-22 中秋节调休
2013-09-29 国庆节调休
2013-10-12 国庆节调休

现在我要计算[数据表]中CompletionDate和RequestDate的间隔天数
要求是跳过“节假日表中的日期”并且跳过“双休日的天数”，但是要加回“调休日表中的天数”
根据这个逻辑最终结果如下：

RequestDate CompletionDate 天数
2013-04-26 2013-05-06 5
2013-04-12 2013-04-15 1

请问这个sql如何写呢？因为数据库用的是access，所以最好是用纯sql，不要用function.谢谢啦。

[sandynet]

不好意思哟，这个可能不行，我说了不要用function实现的

[發糞塗牆]

函数的本质也是T-SQL啊，你把代码体拿出来就可以拉，但是T-SQL语法本身就和access的有一定程度的区别，所以你不应该在SQLServer板块问。不然写出来也不一定能执行。

[习惯性蹭分]

select requestdate,completiondate,天数=
DATEDIFF(day,requestdate,completiondate)-(select count(1) from holidays
where holiday between requestdate and completiondate)+(
select count(1) from tiaoxiu
where [working days] between requestdate and completiondate
)

[sandynet]

我上午自己研究了一下，写了这个语句，您看下是否符合要求 select [CompletionDate],[RequestDate], datediff('d',[RequestDate],[CompletionDate])-datediff('w',[RequestDate],[CompletionDate])*2 -(select count(*) from [节假日] where [Holiday] between [RequestDate] and [CompletionDate] +(select count(*) from [调休日] where [Working days] between [RequestDate] and [CompletionDate] ) as [相差天数] from [数据表]

[gogodiy]

CREATE TABLE t1	--数据表
(
	RequestDate DATE,
	CompletionDate DATE
)
INSERT INTO t1
SELECT '2013-04-26','2013-05-06' UNION ALL
SELECT '2013-04-12','2013-04-15'
CREATE TABLE t2	--节假日
(
	Holiday	DATE,
	Descrip VARCHAR(10)
)
INSERT INTO t2
SELECT '2013-04-04','清明节' UNION ALL
SELECT '2013-04-05','清明节' UNION ALL
SELECT '2013-04-06','清明节' UNION ALL
SELECT '2013-04-29','劳动节' UNION ALL
SELECT '2013-04-30','劳动节' UNION ALL
SELECT '2013-05-01','劳动节' UNION ALL
SELECT '2013-06-10','端午节' UNION ALL
SELECT '2013-06-11','端午节' UNION ALL
SELECT '2013-06-12','端午节'
CREATE TABLE t3	--调休日
(
	Workingdays DATE,
	Descrip VARCHAR(20)
)
INSERT INTO t3
SELECT '2013-01-05','元旦调休' UNION ALL
SELECT '2013-01-06','元旦调休' UNION ALL
SELECT '2013-02-16','春节调休' UNION ALL
SELECT '2013-02-17','春节调休' UNION ALL
SELECT '2013-04-07','清明节调休' UNION ALL
SELECT '2013-04-27','劳动节调休' UNION ALL
SELECT '2013-04-28','劳动节调休' UNION ALL
SELECT '2013-06-08','端午节调休' UNION ALL
SELECT '2013-06-09','端午节调休' UNION ALL
SELECT '2013-09-22','中秋节调休' UNION ALL
SELECT '2013-09-29','国庆节调休' UNION ALL
SELECT '2013-10-12','国庆节调休'
SELECT * FROM t1
SELECT * FROM t2
SELECT * FROM t3

SET DATEFIRST 1
;WITH AAA AS
(
	SELECT	RequestDate,
			CompletionDate,
			DATEADD(DAY,number,RequestDate) AS riqi
	FROM	T1 AS A WITH(NOLOCK) INNER JOIN
			master..spt_values AS B WITH(NOLOCK) ON DATEADD(DAY,number,RequestDate)<=CompletionDate
	AND		B.[type]='P'
	AND		NOT EXISTS (
							SELECT	1 
							FROM	T2 AS A1 WITH(NOLOCK)
							WHERE	DATEADD(DAY,number,RequestDate)=A1.Holiday
					   )
	AND		DATEPART(WEEKDAY,DATEADD(DAY,number,RequestDate)) IN (6,7)
)
,BBB AS
(
	SELECT	*
	FROM	AAA
	UNION ALL
	SELECT	A.RequestDate,
			A.CompletionDate,
			B.Workingdays AS riqi
	FROM	T1 AS A WITH(NOLOCK) INNER JOIN
			T3 AS B WITH(NOLOCK) ON B.Workingdays BETWEEN A.RequestDate AND A.CompletionDate
)
SELECT	RequestDate,
		CompletionDate,
		COUNT(riqi)-1 AS [天数]
FROM	BBB 
GROUP BY RequestDate,CompletionDate

RequestDate	CompletionDate	天数
2013-04-12	2013-04-15	1
2013-04-26	2013-05-06	5

[唐诗三百首]

create table 数据表(RequestDate date, CompletionDate varchar(12))

create table 节假日(Holiday date, description varchar(12))

create table 调休日(Workingdays date, description varchar(20))

insert into 数据表
select '2013-04-26', '2013-05-06' union all
select '2013-04-12', '2013-04-15'

insert into 节假日
select '2013-04-04', '清明节' union all
select '2013-04-05', '清明节' union all
select '2013-04-06', '清明节' union all
select '2013-04-29', '劳动节' union all
select '2013-04-30', '劳动节' union all
select '2013-05-01', '劳动节' union all
select '2013-06-10', '端午节' union all
select '2013-06-11', '端午节' union all
select '2013-06-12', '端午节'

insert into 调休日
select '2013-01-05', '元旦调休' union all
select '2013-01-06', '元旦调休' union all
select '2013-02-16', '春节调休' union all
select '2013-02-17', '春节调休' union all
select '2013-04-07', '清明节调休' union all
select '2013-04-27', '劳动节调休' union all
select '2013-04-28', '劳动节调休' union all
select '2013-06-08', '端午节调休' union all
select '2013-06-09', '端午节调休' union all
select '2013-09-22', '中秋节调休' union all
select '2013-09-29', '国庆节调休' union all
select '2013-10-12', '国庆节调休'


select RequestDate,
       CompletionDate,
       '天数'=datediff(d,RequestDate,CompletionDate)-
       (select count(1) from 节假日 where Holiday between RequestDate and CompletionDate)-
       (select count(1) from
        (select cast(dateadd(d,number,'2013-01-01') as date) 'ssd'
         from master.dbo.spt_values 
         where type='P' and datepart(dw,dateadd(d,number,'2013-01-01')) in(1,7)) t
         where t.ssd between RequestDate and CompletionDate
         and t.ssd not in(select Holiday from 节假日))+
        (select count(1) from 调休日 where Workingdays between RequestDate and CompletionDate)
from 数据表

/*
RequestDate CompletionDate 天数
----------- -------------- -----------
2013-04-26  2013-05-06     5
2013-04-12  2013-04-15     1

(2 row(s) affected)
*/

[發糞塗牆]

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

[sandynet]

好的，谢谢各位。