我现在要写一个SQL的存储过程,主要是需要判别出当前两周每天是否是工作时间,然后会有一张自己的数据库表做对应更新操作,来输出符合自己公司的工作日历。SQL语句执行效果如下:
-- 先去创建一张真实表
-- create table GongShi_TempTime
-- (
-- DDay datetime,
-- WWeeks varchar(50),
-- Whether varchar(50)
-- )
--首先是创建一个叫OAWorkTime的存储过程
create procedure OAWorkTime
--定义输入日期
@startday DATETIME, --第一天日期
@endday DATETIME, --最后一天日期
--定义的当天是否是工作日
@whether INT ='1'
AS
BEGIN
DECLARE @tempday DATETIME
SET @startday = CONVERT(DATE,@startday);
SET @endday = CONVERT(DATE,@endday);
SET @tempday = @endday;
--传入的值不能为空
IF(
@startday IS NOT NULL AND @startday <> ''
AND
@endday IS NOT NULL AND @endday <> ''
)
BEGIN
--定义的当天日期对应的是星期几
declare @weeks int
--开始while循环推出
WHILE @endday >= @startday
BEGIN
set @weeks = datepart(weekday,@endday+@@DATEFIRST-1)
--用作if判断,输出是否是工作日
--0代表工作日,1代表休息日,2代表放假日
if @weeks = 1
begin
set @whether = 0
end
if @weeks = 2
begin
set @whether = 0
end
if @weeks = 3
begin
set @whether = 0
end
if @weeks = 4
begin
set @whether = 0
end
if @weeks = 5
begin
set @whether = 0
end
IF @weeks = 6
BEGIN
SET @whether = 1
END
IF @weeks = 7
BEGIN
SET @whether = 1
END
--条件判断当前日期是否在正式表中,如果不存在则插入
IF NOT EXISTS ( SELECT *
FROM GongShi_TempTime
WHERE @endday = GongShi_TempTime.DDay )
INSERT INTO GongShi_TempTime(DDay,WWeeks,Whether)
SELECT
CONVERT(VARCHAR,@endday,111) AS 日期,
@weeks AS 星期几,
@whether AS 是否是工作日
--条件判断OA调整过程表日期是否在正式表中,如果存在则更新
IF EXISTS ( SELECT *
FROM WORKTIME_SPECIALDAY
WHERE convert(date,@endday) = CONVERT(date,WORKTIME_SPECIALDAY.DATE_NUM) )
UPDATE t1 SET t1.Whether = t2.IS_REST FROM GongShi_TempTime t1,WORKTIME_SPECIALDAY t2 WHERE t1.DDay=t2.DATE_NUM
SET @endday = @endday -1
END
END
BEGIN
DELETE FROM
GongShi_TempTime
WHERE GongShi_TempTime.DDay NOT BETWEEN @startday AND @tempday
END
BEGIN
SELECT DDay AS TheDay,Whether AS Flag FROM GongShi_TempTime ORDER BY DDay
END
END
---select * from GongShiTempTime
---execute OAWorkTime '2018-09-29 01:33:33','2018-10-10 01:11:11'
---DROP PROCEDURE OAWorkTime
execute OAWORKTIME