表A中有今年的节假日表,如下:
ID 日期 假期名称
2 2008-1-1 元旦
3 2008-2-6 春节
4 2008-2-7 春节
5 2008-2-8 春节
6 2008-4-4 清明节
7 2008-5-1 五一节
8 2008-6-7 端午节
9 2008-9-13中秋节
10 2008-10-1国庆节
11 2008-10-2国庆节
12 2008-10-3国庆节表B结构如下:
id 签定日期 到期日期
现想实现签定日期加上9个工作日(即扣除节假日和星期六、日)后到期日期是哪一天,该如何写这sql语句?
比如签定日期为2008-04-29 那9个工作日后的到期时间应为2008-05-13(其中扣除了5月3日星期六、5月4日星期天、5月10日星期六、5月11日星期天和表A中的5月1日五一节这五天)
ID 日期 假期名称
2 2008-1-1 元旦
3 2008-2-6 春节
4 2008-2-7 春节
5 2008-2-8 春节
6 2008-4-4 清明节
7 2008-5-1 五一节
8 2008-6-7 端午节
9 2008-9-13中秋节
10 2008-10-1国庆节
11 2008-10-2国庆节
12 2008-10-3国庆节表B结构如下:
id 签定日期 到期日期
现想实现签定日期加上9个工作日(即扣除节假日和星期六、日)后到期日期是哪一天,该如何写这sql语句?
比如签定日期为2008-04-29 那9个工作日后的到期时间应为2008-05-13(其中扣除了5月3日星期六、5月4日星期天、5月10日星期六、5月11日星期天和表A中的5月1日五一节这五天)
-- Author: happyflystone
-- Version:V1.001
-- Date:2008-11-26 12:34:11
-------------------------------------- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(ID int,日期 smalldatetime,假期名称 nvarchar(3))
Go
Insert into ta
select 2,'2008-1-1','元旦' union all
select 3,'2008-2-6','春节' union all
select 4,'2008-2-7','春节' union all
select 5,'2008-2-8','春节' union all
select 6,'2008-4-4','清明节' union all
select 7,'2008-5-1','五一节' union all
select 8,'2008-6-7','端午节' union all
select 9,'2008-9-13','中秋节' union all
select 10,'2008-10-1','国庆节' union all
select 11,'2008-10-2','国庆节' union all
select 12,'2008-10-3','国庆节'
Go
--Start
--在指定日期上,增加指定工作天数后的日期
CREATE FUNCTION f_WorkDayADD(
@date datetime, --基础日期
@workday int --要增加的工作日数
)RETURNS datetime
AS
BEGIN
DECLARE @bz int
--增加整周的天数
SELECT @bz=CASE WHEN @workday <0 THEN -1 ELSE 1 END
,@date=DATEADD(Week,@workday/5,@date)
,@workday=@workday%5
--增加不是整周的工作天数
WHILE @workday <> 0
SELECT @date=DATEADD(Day,@bz,@date),
@workday=CASE WHEN (@@DATEFIRST+DATEPART(Weekday,@date)-1)%7 BETWEEN 1 AND 5
THEN @workday-@bz ELSE @workday END
--避免处理后的日期停留在非工作日上
WHILE (@@DATEFIRST+DATEPART(Weekday,@date)-1)%7 in(0,6)
SET @date=DATEADD(Day,@bz,@date)
WHILE exists(select 1 from ta where datediff(d,日期,@date) =0 )
SET @date=DATEADD(Day,1,@date)
RETURN(@date)
END
go
select dbo.f_WorkDayADD('2008-04-29', 9)
drop function f_WorkDayADD
--Result:
/*------------------------------------------------------
2008-05-12 00:00:00.000(所影响的行数为 1 行)
*/
--End
declare @d table(id int identity(1,1),happyday datetime)
insert into @d select '2008-10-1' union all
select '2008-10-2' union all
select '2008-10-3'select * from @ddeclare @startdate datetime,@enddate datetime,@days int,@leftdays int
set @startdate='2008-9-30'
set @days=1
set @leftdays=@days
set @enddate=@startdatewhile @leftdays>0
begin
set @enddate=dateadd(dd,1,@enddate)
if (select count(*) from @d where happyday=@enddate)=0
set @leftdays=@leftdays-1
end
select 'enddate'=@enddate--------------------
运行结果:
2008-10-04 00:00:00.000
INSERT TA
SELECT 2, '2008-1-1', N'元旦' UNION ALL
SELECT 3, '2008-2-6', N'春节' UNION ALL
SELECT 4, '2008-2-7', N'春节' UNION ALL
SELECT 5, '2008-2-8', N'春节' UNION ALL
SELECT 6, '2008-4-4', N'清明节' UNION ALL
SELECT 7, '2008-5-1', N'五一节' UNION ALL
SELECT 8, '2008-6-7', N'端午节' UNION ALL
SELECT 9, '2008-9-13', N'中秋节' UNION ALL
SELECT 10, '2008-10-1', N'国庆节' UNION ALL
SELECT 11, '2008-10-2', N'国庆节' UNION ALL
SELECT 12, '2008-10-3', N'国庆节'DECLARE @DAYS INT
DECLARE @START SMALLDATETIMESET @DAYS=1
SET @START='2008-04-29'SET DATEFIRST 1
WHILE @DAYS<=9
BEGIN
IF NOT EXISTS(SELECT 1 FROM TA WHERE 日期=@START)
BEGIN
IF NOT (DATEPART(DW,@START) IN (6,7))
SET @DAYS=@DAYS+1
END
SET @START=DATEADD(DAY,1,@START)
ENDSELECT @START AS 到期日期
DROP TABLE TA
/*
到期日期
------------------------------------------------------
2008-05-13 00:00:00
*/
SET @START='2008-04-29' 如何改成表A中的A字段的值
WHILE @DAYS<=9 如何改成表B中的D字段的值
declare @TA TABLE(ID INT,日期 SMALLDATETIME, 假期名称 NVARCHAR(10))
INSERT @TA
SELECT 2, '2008-1-1', N'元旦' UNION ALL
SELECT 3, '2008-2-6', N'春节' UNION ALL
SELECT 4, '2008-2-7', N'春节' UNION ALL
SELECT 5, '2008-2-8', N'春节' UNION ALL
SELECT 6, '2008-4-4', N'清明节' UNION ALL
SELECT 7, '2008-5-1', N'五一节' UNION ALL
SELECT 8, '2008-6-7', N'端午节' UNION ALL
SELECT 9, '2008-9-13', N'中秋节' UNION ALL
SELECT 10, '2008-10-1', N'国庆节' UNION ALL
SELECT 11, '2008-10-2', N'国庆节' UNION ALL
SELECT 12, '2008-10-3', N'国庆节'declare @TB TABLE(Start SMALLDATETIME, Days smallint)
insert @TB values('2008-04-29',9)
DECLARE @DAYS INT
DECLARE @START SMALLDATETIMEselect @DAYS=Days, @START=Start from @tb
SET DATEFIRST 1
WHILE @DAYS<=9
BEGIN
IF NOT EXISTS(SELECT 1 FROM @TA WHERE 日期=@START)
BEGIN
IF NOT (DATEPART(DW,@START) IN (6,7))
SET @DAYS=@DAYS+1
END
SET @START=DATEADD(DAY,1,@START)
ENDSELECT @START AS 到期日期/*
到期日期
------------------------------------------------------
2008-05-13 00:00:00
*/
declare @TA TABLE(ID INT,日期 SMALLDATETIME, 假期名称 NVARCHAR(10))
INSERT @TA
SELECT 2, '2008-1-1', N'元旦' UNION ALL
SELECT 3, '2008-2-6', N'春节' UNION ALL
SELECT 4, '2008-2-7', N'春节' UNION ALL
SELECT 5, '2008-2-8', N'春节' UNION ALL
SELECT 6, '2008-4-4', N'清明节' UNION ALL
SELECT 7, '2008-5-1', N'五一节' UNION ALL
SELECT 8, '2008-6-7', N'端午节' UNION ALL
SELECT 9, '2008-9-13', N'中秋节' UNION ALL
SELECT 10, '2008-10-1', N'国庆节' UNION ALL
SELECT 11, '2008-10-2', N'国庆节' UNION ALL
SELECT 12, '2008-10-3', N'国庆节'DECLARE @DAYS INT
DECLARE @START SMALLDATETIMEselect @DAYS=A字段 from 表A
select @START=D字段 from 表B
SET DATEFIRST 1
WHILE @DAYS<=9
BEGIN
IF NOT EXISTS(SELECT 1 FROM @TA WHERE 日期=@START)
BEGIN
IF NOT (DATEPART(DW,@START) IN (6,7))
SET @DAYS=@DAYS+1
END
SET @START=DATEADD(DAY,1,@START)
ENDSELECT @START AS 到期日期/*
到期日期
------------------------------------------------------
2008-05-13 00:00:00
*/
这样通过
9楼和10楼的代码运行后只能增加一天变成2008-04-30 ,不知错在哪里高手请指点
Create table ta(ID int,日期 smalldatetime,假期名称 nvarchar(3))
Go
Insert into ta
select 2,'2008-1-1','元旦' union all
select 3,'2008-2-6','春节' union all
select 4,'2008-2-7','春节' union all
select 5,'2008-2-8','春节' union all
select 6,'2008-4-4','清明节' union all
select 7,'2008-5-1','五一节' union all
select 8,'2008-6-7','端午节' union all
select 9,'2008-9-13','中秋节' union all
select 10,'2008-10-1','国庆节' union all
select 11,'2008-10-2','国庆节' union all
select 12,'2008-10-3','国庆节'
Go
create table tb(id int,签定日期 smalldatetime,到期日期 smalldatetime)
insert into tb select 1,'2008-04-29',null
union all select 2,'2008-04-30',null
select * from ta
select * from tb
go
create function tatb(@start smalldatetime)
returns smalldatetime
as
begin
DECLARE @DAYS INTSET @DAYS=1while @days <=9
begin
if not exists(select 1 from ta where 日期=@start)
begin
if datepart(dw,@start) not in(6,7)
set @days=@days+1
end
set @start=dateadd(day,1,@start)
end
return @start
end
go
update a set 到期日期=dbo.tatb(b.签定日期)
from tb a,tb b
where a.id=b.id
select * from tb
drop table ta
drop table tb
drop function tatb
/*
ID 日期 假期名称
----------- ----------------------- ----
2 2008-01-01 00:00:00 元旦
3 2008-02-06 00:00:00 春节
4 2008-02-07 00:00:00 春节
5 2008-02-08 00:00:00 春节
6 2008-04-04 00:00:00 清明节
7 2008-05-01 00:00:00 五一节
8 2008-06-07 00:00:00 端午节
9 2008-09-13 00:00:00 中秋节
10 2008-10-01 00:00:00 国庆节
11 2008-10-02 00:00:00 国庆节
12 2008-10-03 00:00:00 国庆节(11 行受影响)id 签定日期 到期日期
----------- ----------------------- -----------------------
1 2008-04-29 00:00:00 NULL
2 2008-04-30 00:00:00 NULL(2 行受影响)
(2 行受影响)
id 签定日期 到期日期
----------- ----------------------- -----------------------
1 2008-04-29 00:00:00 2008-05-13 00:00:00
2 2008-04-30 00:00:00 2008-05-14 00:00:00
*/
declare @start datetime --签约日
declare @end datetime --到期日
declare @I int
SET @day =9
SET @start ='2008-04-29'SET @I = 1
SET @end =@start
while @I <= @day
begin
SET @end = DATEADD (day, 1,@end)
if not exists(select * from A where 日期 between @end and @end )
set @I=@I+1
end
select @end --到期日