作者:执子之手2502891083 | 来源:互联网 | 2023-06-08 13:57
在日常开发中,有时候需要扣除节假日,本人实际开发中使用了一套比较好的办法与大家讨论.表结构ER设计如下:其中:节假日表,是存计算好的节假日结果.并且把日期换成整形日期,建立索引提高判断速度,只要节
在日常开发中,有时候需要扣除节假日,本人实际开发中使用了一套比较好的办法与大家讨论.
表结构ER设计如下:
其中:节假日表,是存计算好的节假日结果.并且把日期换成"整形日期",建立索引提高判断速度,
只要节假日,变成,当晚就可以通过DTS最新计算节假日表,
存储过程如下:
CREATE
Procedure
sp_holiday
@YEAR
int
AS
--
产生节假日数据
--
exec sp_holiday 2005
SET
NOCOUNT
ON
--
判断是否需要计算
IF
(
SELECT
SET_IS_REDO
FROM
TJ_SETTINGS
WHERE
SET_YEAR
=
@YEAR
)
=
1
BEGIN
BEGIN
TRAN
DELETE
FROM
TJ_HOLIDAY
WHERE
HOL_YEAR
=
@YEAR
DECLARE
@SQL
VARCHAR
(
100
),
@THIS_DATE
SMALLDATETIME
,
@BEGIN_DATE
SMALLDATETIME
,
@END_DATE
SMALLDATETIME
,
@HOL_NAME
VARCHAR
(
50
),
@INDEX
TINYINT
,
@IS_WEEK
TINYINT
--
初始化双休日数据
CREATE
TABLE
#WEEK (WEEK_DAY
TINYINT
PRIMARY
KEY
NOT
NULL
, IS_WEEK
TINYINT
NULL
)
SET
@INDEX
=
0
WHILE
(
@INDEX
<
7
)
BEGIN
SET
@SQL
=
'
INSERT INTO #WEEK (WEEK_DAY, IS_WEEK) SELECT
'
+
CAST
(
@INDEX
AS
CHAR
(
1
))
+
'
, WK_
'
+
CAST
(
@INDEX
AS
CHAR
(
1
))
+
'
FROM TJ_WEEK WHERE WK_YEAR =
'
+
CAST
(
@YEAR
AS
CHAR
(
4
))
EXEC
(
@SQL
)
SET
@INDEX
=
@INDEX
+
1
END
--
每一天判断
SET
@BEGIN_DATE
=
CONVERT
(
SMALLDATETIME
,
CAST
(
@YEAR
AS
CHAR
(
4
))
+
'
-01-01
'
,
120
)
SET
@END_DATE
=
DATEADD
(
YEAR
,
1
,
@BEGIN_DATE
)
SET
@THIS_DATE
=
@BEGIN_DATE
WHILE
(
@THIS_DATE
<
@END_DATE
)
BEGIN
--
非节假日
IF
EXISTS
(
SELECT
*
FROM
TJ_NONFERIA
WHERE
NFR_YEAR
=
@YEAR
AND
NFR_DATE
=
@THIS_DATE
)
begin
SET
@THIS_DATE
=
DATEADD
(
DAY
,
1
,
@THIS_DATE
)
CONTINUE
end
--
节日
ELSE
IF
EXISTS
(
SELECT
*
FROM
TJ_FERIA
WHERE
FER_YEAR
=
@YEAR
AND
FER_DATE
=
@THIS_DATE
)
BEGIN
SELECT
@HOL_NAME
=
FER_NAME
FROM
TJ_FERIA
WHERE
FER_YEAR
=
@YEAR
AND
FER_DATE
=
@THIS_DATE
INSERT
INTO
TJ_HOLIDAY (HOL_YEAR, HOL_DATE_INT, HOL_DATE, HOL_NAME)
VALUES
(
@YEAR
,
FLOOR
(
CONVERT
(
FLOAT
,
@THIS_DATE
)),
@THIS_DATE
,
@HOL_NAME
)
END
--
休息日
ELSE
BEGIN
SELECT
@IS_WEEK
=
IS_WEEK
FROM
#WEEK
WHERE
WEEK_DAY
=
(
DATEPART
(WEEKDAY,
@THIS_DATE
)
-
1
)
IF
(
@IS_WEEK
>
0
)
INSERT
INTO
TJ_HOLIDAY (HOL_YEAR, HOL_DATE_INT, HOL_DATE, HOL_NAME)
VALUES
(
@YEAR
,
FLOOR
(
CONVERT
(
FLOAT
,
@THIS_DATE
)),
@THIS_DATE
,
DATENAME
(WEEKDAY,
@THIS_DATE
))
END
SET
@THIS_DATE
=
DATEADD
(
DAY
,
1
,
@THIS_DATE
)
END
--
重新设置计算标记
UPDATE
TJ_SETTINGS
SET
SET_IS_REDO
=
0
WHERE
SET_YEAR
=
@YEAR
IF
@@ERROR
=
0
COMMIT
TRAN
ELSE
ROLLBACK
TRAN
DROP
TABLE
#WEEK
END
SET
NOCOUNT
OFF
GO
外
/**/
/******************************************************************
* 名称: 工作日重新
* 作者: WANGYJ
* 时间: 2005-5-17
*
* -----------------------------------------------------------------
* 版本 时间 作者 备注
*
* V1.00 2005-5-3 WANGYJ 创建
* -----------------------------------------------------------------
******************************************************************/
create
Procedure
sp_holiday_ALL
AS
DECLARE
my_cursor
CURSOR
for
select
SET_YEAR
from
TJ_SETTINGS
where
SET_IS_REDO
=
1
DECLARE
@year
varchar
(
4
)
open
my_cursor
FETCH
NEXT
FROM
my_cursor
INTO
@year
WHILE
@@FETCH_STATUS
=
0
BEGIN
exec
risk.sp_holiday
@year
FETCH
NEXT
FROM
my_cursor
INTO
@year
END
CLOSE
my_cursor
DEALLOCATE
my_cursor
GO
节假日扣除函数如下:
CREATE
FUNCTION
risk.CalcDay
(
@Diff
smallint
,
--
差别值.正数为加,负数减
@D_Date
datetime
--
差别日期
)
RETURNS
datetime
AS
BEGIN
declare
@ordDate
datetime
set
@ordDate
=
@D_Date
set
@D_Date
=
convert
(
char
(
10
),
@D_Date
,
120
)
--
去掉时间部分(防止传入的参数中有时间部分,影响处理)
if
@Diff
>
0
begin
while
@Diff
>
0
begin
select
@D_Date
=
@D_Date
+
@Diff
,
@Diff
=
count
(
*
)
from
TJ_HOLIDAY
where
HOL_DATE_INT
>
FLOOR
(
CONVERT
(
FLOAT
,
@D_Date
))
AND
HOL_DATE_INT
<=
FLOOR
(
CONVERT
(
FLOAT
,
@D_Date
+
@Diff
))
end
end
else
begin
while
@Diff
<
0
begin
select
@D_Date
=
@D_Date
+
@Diff
,
@Diff
=-
count
(
*
)
from
TJ_HOLIDAY
where
HOL_DATE_INT
<
FLOOR
(
CONVERT
(
FLOAT
,
@D_Date
))
AND
HOL_DATE_INT
>=
FLOOR
(
CONVERT
(
FLOAT
,
@D_Date
+
@Diff
))
end
end
set
@D_Date
=
dateadd
(hour,
datepart
(hour,
@ordDate
),
@D_Date
)
set
@D_Date
=
dateadd
(minute,
datepart
(minute,
@ordDate
),
@D_Date
)
set
@D_Date
=
dateadd
(second,
datepart
(second,
@ordDate
),
@D_Date
)
return
(
@D_Date
)
END
这样就可以使用了,dbo.CaclDay(-5,getdate())就是扣除节假以后的5天前的数据
希望对大家有帮助