SQL判断上班时间,具体到分钟

 
 
-- 获取某公司(或项目)的一个时间段内的休息日天数(暂用在SP3_SystemJumpCheckTach及CTCheckContentView.GetCheckOption中)
ALTER FUNCTION [dbo].[F3_GetHolidays]
(
	@WCCID VARCHAR(50),		-- 公司或项目ID
	@SD DATETIME,			-- 开始日期
	@ED DATETIME			-- 结束日期
) RETURNS DECIMAL(18,4)
BEGIN
	DECLARE @WCID VARCHAR(50)
	DECLARE @Cnt DECIMAL(18,4)
	DECLARE @IsWorkDay BIT

	SELECT @WCID=WCID FROM TWorkCalendarConfig WHERE WCCID=@WCCID

	SET @Cnt=0

	IF (@SD<@ED)
	BEGIN
		SET @IsWorkDay=1
		IF (DATEPART(dw,@SD)=1 OR DATEPART(dw,@SD)=7)
			SET @IsWorkDay=0
		SELECT @IsWorkDay=IsWorkDay FROM TWorkCalendarDetail
		WHERE WCID=@WCID AND DATEDIFF(dd,[Date],@SD)=0

		-- 如果是同一天
		IF (DATEDIFF(dd,@SD,@ED)=0)
		BEGIN
			IF(@IsWorkDay=0)
				SET @Cnt=DATEDIFF(ss,@SD,@ED)/86400.00
		END
		ELSE
		BEGIN
			-- (1)计算开始日期当天的休息天数
			IF(@IsWorkDay=0)
				SET @Cnt=1-(DATEPART(hh,@SD)*3600+DATEPART(mi,@SD)*60+DATEPART(ss,@SD))/86400.00
		
			-- (2)计算结束日期当天的休息天数
			SET @IsWorkDay=1
			IF (DATEPART(dw,@ED)=1 OR DATEPART(dw,@ED)=7)
				SET @IsWorkDay=0
			SELECT @IsWorkDay=IsWorkDay FROM TWorkCalendarDetail
			WHERE WCID=@WCID AND DATEDIFF(dd,[Date],@ED)=0

			IF(@IsWorkDay=0)
				SET @Cnt=@Cnt+(DATEPART(hh,@ED)*3600+DATEPART(mi,@ED)*60+DATEPART(ss,@ED))/86400.00

			-- (3)计算开始日期和结束日期之间的日期
			SET @SD=DATEADD(dd,1,CAST(YEAR(@SD) AS VARCHAR(4))+'-'+CAST(MONTH(@SD) AS VARCHAR(2))+'-'+CAST(DAY(@SD) AS VARCHAR(2)))
			SET @ED=DATEADD(dd,-1,CAST(YEAR(@ED) AS VARCHAR(4))+'-'+CAST(MONTH(@ED) AS VARCHAR(2))+'-'+CAST(DAY(@ED) AS VARCHAR(2)))
			WHILE (@SD<=@ED)
			BEGIN
				SET @IsWorkDay=1
				IF(DATEPART(dw,@SD)=1 OR DATEPART(dw,@SD)=7)
					SET @IsWorkDay=0
				SELECT @IsWorkDay=IsWorkDay FROM TWorkCalendarDetail
				WHERE WCID=@WCID AND DATEDIFF(dd,[Date],@SD)=0

				IF(@IsWorkDay=0)
					SET @Cnt=@Cnt+1

				SET @SD=DATEADD(dd,1,@SD)
			END
		END
	END

	RETURN (@Cnt)
END   --休息日天数,另一种计算方式用到
-- 获取某公司(或项目)的一个时间段内的工作日分钟(暂用在CTCheckContentView.GetCheckOption中)
ALTER FUNCTION [dbo].[F3_GetWorkMinutes]
(
	@CorpID VARCHAR(50),	-- 公司ID
	@SD DATETIME,			-- 开始日期
	@ED DATETIME			-- 结束日期
) RETURNS INT
BEGIN
	DECLARE @WCID VARCHAR(50)
	DECLARE @Cnt DECIMAL(18,4)	
	DECLARE @IsWorkDay BIT
	DECLARE @OnTime DATETIME,@MorningOffTime DATETIME,@AfternoonOnTime DATETIME,@OffTime DATETIME

	SELECT @WCID=WCID FROM TWorkCalendarConfig WHERE WCCID=@CorpID
	SELECT @OnTime='09:00',@MorningOffTime='12:00',@AfternoonOnTime='13:30',@OffTime='17:30'

	SELECT @OnTime=UpTime,@MorningOffTime=NoonDownTime,@AfternoonOnTime=DATEADD(mi,RestMinutes,NoonDownTime),@OffTime=DownTime
	FROM IDOADB.dbo.TWorkTimeOption
	WHERE CorpID=@CorpID

	SET @Cnt=0

	IF (@SD<@ED)
	BEGIN
		SET @IsWorkDay=1
		IF (DATEPART(dw,@SD)=1 OR DATEPART(dw,@SD)=7)
			SET @IsWorkDay=0
		SELECT @IsWorkDay=IsWorkDay FROM TWorkCalendarDetail
		WHERE WCID=@WCID AND DATEDIFF(dd,[Date],@SD)=0

		-- 如果是同一天
		IF (DATEDIFF(dd,@SD,@ED)=0)
		BEGIN
			IF(@IsWorkDay=1)
				SET @Cnt=dbo.F3_GetWorkMinutesByRange(@OnTime,@MorningOffTime,@AfternoonOnTime,@OffTime,@SD,@ED)
		END
		ELSE
		BEGIN
			-- (1)计算开始日期当天的工作分钟
			IF(@IsWorkDay=1)
				SET @Cnt=dbo.F3_GetWorkMinutesByRange(@OnTime,@MorningOffTime,@AfternoonOnTime,@OffTime,@SD,NULL)
		
			-- (2)计算结束日期当天的工作分钟
			SET @IsWorkDay=1
			IF (DATEPART(dw,@ED)=1 OR DATEPART(dw,@ED)=7)
				SET @IsWorkDay=0
			SELECT @IsWorkDay=IsWorkDay FROM TWorkCalendarDetail
			WHERE WCID=@WCID AND DATEDIFF(dd,[Date],@ED)=0

			IF(@IsWorkDay=1)
				SET @Cnt=@Cnt+dbo.F3_GetWorkMinutesByRange(@OnTime,@MorningOffTime,@AfternoonOnTime,@OffTime,NULL,@ED)

			-- (3)计算开始日期和结束日期之间的工作分钟
			SELECT @SD=DATEADD(dd,1,CONVERT(VARCHAR(10),@SD,120)),@ED=DATEADD(dd,-1,CONVERT(VARCHAR(10),@ED,120))
			WHILE (@SD<=@ED)
			BEGIN
				SET @IsWorkDay=1
				IF(DATEPART(dw,@SD)=1 OR DATEPART(dw,@SD)=7)
					SET @IsWorkDay=0
				SELECT @IsWorkDay=IsWorkDay FROM TWorkCalendarDetail
				WHERE WCID=@WCID AND DATEDIFF(dd,[Date],@SD)=0

				IF(@IsWorkDay=1)
					SET @Cnt=@Cnt+DATEDIFF(mi,@OnTime,@MorningOffTime)+DATEDIFF(mi,@AfternoonOnTime,@OffTime)

				SET @SD=DATEADD(dd,1,@SD)
			END
		END
	END

	RETURN (ROUND(@Cnt,0))
END
-- 获取工作日内某个时间段内的工作分钟(暂用在OperAllowDB.dbo.F3_GetWorkdays中)
-- 作者:程爱民
-- 日期:2012-11-28
ALTER FUNCTION [dbo].[F3_GetWorkMinutesByRange]
(
	@OnTime DATETIME,			-- 上班时间
	@MorningOffTime DATETIME,	-- 上午下班时间(午休开始时间)
	@AfternoonOnTime DATETIME,	-- 下午上班时间(午休结束时间)
	@OffTime DATETIME,			-- 下班时间
	@SD DATETIME,				-- 开始时间(当@ED为NULL时,获取以@SD为)
	@ED DATETIME				-- 结束时间
) RETURNS DECIMAL(18,4)
BEGIN
	DECLARE @Cnt DECIMAL(18,4)
	SET @Cnt=0.00
	
	SELECT @SD=CONVERT(VARCHAR(8),@SD,108),@ED=CONVERT(VARCHAR(8),@ED,108)
	SELECT @SD=ISNULL(@SD,@OnTime),@ED=ISNULL(@ED,@OffTime)
	
	IF (@SD<@ED)
	BEGIN
		-- 上午工作分钟
		IF (@SD<@MorningOffTime)
			SET @Cnt=DATEDIFF(ss,
				CASE WHEN @SD<@OnTime THEN @OnTime ELSE @SD END,
				CASE WHEN @ED>@MorningOffTime THEN @MorningOffTime ELSE @ED END)/60.0

		-- 下午工作分钟
		IF (@ED>@AfternoonOnTime)
			SET @Cnt=@Cnt+DATEDIFF(ss,
				CASE WHEN @SD<@AfternoonOnTime THEN @AfternoonOnTime ELSE @SD END,
				CASE WHEN @ED>@OffTime THEN @OffTime ELSE @ED END)/60.0

		IF (@Cnt<0)
			SET @Cnt=0
	END

	RETURN (@Cnt)
END



  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值